Pulling related data from relational dB re: thread: Problems with a many-to-many inse

[filch]

OK, now that I managed to insert data properly into a relational database using a linking table, I am now faced with pulling the data properly out of the database, a displaying it so it makes sense. As a recap, I have inserted user data into a data base that included the usual name address, phone, email etc, as well as the fact that they have access to various airports and at each airport, access to one to three services, which are the same for every airport. ( see my previous thread called Problems with a many-to-many insert for more details ).

I have written the following SQL statement, which does indeed get all of the airports and services that a particular user has access to but I need to get them out in a related manner. For example, the user has access to airport YVR and has access to services Jet and Ground at this airport. He also has access to airport YYZ and at this airport, he has access to services Jet Ground and Glycol. Here is the SQL:

SELECT airport.airport, service.service, userairportservices.usr_id_users
FROM airport, service, userairportservices
WHERE userairportservices.usr_id_users = usr_id AND service.service_id = userairportservices.service_id_service AND airport.airport_id = userairportservices.airport_id_airport

And here is the info it pulls out: [ attached screen grab ]

So, I need to figure out a way to put this back into the page for display [ see second image for example of page ]. Obviously, it is being displayed in two forms: one as a non-editable details display and two, as an editable form for updating the record. So th image merely gives you an example of how I want to display the relationship between airport and services.

As always, I appreciate any insight and help.

Dave

[dickersonka]

lol oh filch

this is where the dynamic piece would have helped on the saving

i would suggest to possibly alphabetize them, that is unless they are in order by id

to your query add this

order airport.airport asc, service.service asc

[filch]

Actually, the insert form is being built dynamically. I did manage to get that done. This is a separate page and is not a form. This is a page that simply displays the users details. I am just trying to now display it in a grid, similar to the grid I used to insert it.

I am using the ORDERBY command first by airport and then by service, as you suggest but I want to only output the airport name once but if you look at the output I am getting, you see that an airport can have up to three records per user, depending on how many of the three available services the user has access to.

I am looking for a method to have the data repeat like in a repeat region. But only one row per airport but inside that row, a nested repeat that outputs all the services for that airport. And then moves on to the next airport.

Dave

[dickersonka]

why not do the similar thing as before

loop through the results
when the airportcode changes that means you are in a new group

if in same group, loop through the services and check the appropriate box

i'm not really following the part you are having trouble with, unles you are meaning you want rows to columns? meaning one single row per airport with columns of services

[filch]

Yeah I want there to be one row per airport and then, inside of that row, I want to list the related services, either as columns or as rows.

I have attached what my current SQL query is giving me. So now I just need a way to put it on the page.

Dave

[dickersonka]

Yeah I want there to be one row per airport and then, inside of that row, I want to list the related services, either as columns or as rows.

you are meaning sql or php?

[filch]

Ahh yes .. I mean in PHP. I guess, as I have an SQL query that gives me what I want, this question would not be appropriate here? I think I was wondering if there was a way to write an SQL query that give me the one airport as well as the one to three associated services back as one record rather than two or three.

D

[dickersonka]

just to be sure this is what you are meaning, here's a similar post

http://www.daniweb.com/forums/post735371-9.html

is this what you want?

[filch]

To be truthful I am not sure. I understand some of that but not enough to know if it would work with my situation. It seems like it would but I am not sure.

Dave

[dickersonka]

will you post the structure and i get you a query?

[filch]

This may seem like an idiot response but can you clarify what you mean by structure so I don't send you the wrong thing?

D

[dickersonka]

the table schema, if you want you can send a backup if its small enough

[filch]

Here you go:

-- 
-- Database: `fsmgroup3`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `access`
-- 

DROP TABLE IF EXISTS `access`;
CREATE TABLE `access` (
  `access_id` int(11) NOT NULL auto_increment,
  `access` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`access_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `airport`
-- 

DROP TABLE IF EXISTS `airport`;
CREATE TABLE `airport` (
  `airport_id` int(11) NOT NULL auto_increment,
  `airport_name` varchar(100) NOT NULL default '',
  `airport_code` char(3) NOT NULL default '',
  PRIMARY KEY  (`airport_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `service`
-- 

DROP TABLE IF EXISTS `service`;
CREATE TABLE `service` (
  `service_id` int(11) NOT NULL auto_increment,
  `service` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `title`
-- 

DROP TABLE IF EXISTS `title`;
CREATE TABLE `title` (
  `title_id` int(11) NOT NULL auto_increment,
  `title` varchar(4) NOT NULL default '',
  PRIMARY KEY  (`title_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `userairportservices`
-- 

DROP TABLE IF EXISTS `userairportservices`;
CREATE TABLE `userairportservices` (
  `userairportservices_id` int(11) NOT NULL auto_increment,
  `usr_id_users` int(11) NOT NULL default '0',
  `airport_id_airport` int(11) NOT NULL default '0',
  `service_id_service` int(11) NOT NULL default '0',
  PRIMARY KEY  (`userairportservices_id`),
  KEY `usr_id_users` (`usr_id_users`),
  KEY `airport_id_airport` (`airport_id_airport`),
  KEY `service_id_service` (`service_id_service`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `usr_id` int(11) NOT NULL auto_increment,
  `usr_access` int(11) NOT NULL default '0',
  `usr_title` int(11) default NULL,
  `usr_fname` varchar(75) NOT NULL default '',
  `usr_lname` varchar(75) NOT NULL default '',
  `usr_add1` varchar(75) NOT NULL default '',
  `usr_add2` varchar(75) default NULL,
  `usr_add3` varchar(75) default NULL,
  `usr_city` varchar(75) NOT NULL default '',
  `usr_prov_state` char(2) NOT NULL default '',
  `usr_pcode` varchar(10) NOT NULL default '',
  `usr_cntry` varchar(75) NOT NULL default '',
  `usr_acode` varchar(4) NOT NULL default '',
  `usr_phone` varchar(15) NOT NULL default '',
  `usr_email` varchar(75) NOT NULL default '',
  `usr_pass` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`usr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

[dickersonka]

thanks, how soon do you need it?

cool if i get back with you in the morning?

[filch]

Of course ... whenever you can. I appreciate it.

Dave

[dickersonka]

sure man, will work on it right after i get to work in the morning

...ahhhh, a fresh mind

[dickersonka]

this should do the trick, the only problem is if you ever add services, you will need to change the query to reflect that

select usr_id_users as USR_ID, airport_id_airport AS AIRPORT_ID,
a.airport_code AS AIRPORT_CODE,
max(if(service_id_service=1, 1, 0)) as JET,
max(if(service_id_service=2, 1, 0)) as GROUND,
max(if(service_id_service=3, 1, 0)) as GLYCOL
FROM userairportservices uas
INNER JOIN airport a
 on a.airport_id = uas.airport_id_airport
-- change this for different users
where usr_id_users = 1 
group by airport_id_airport;

[filch]

Thanks for this. I will not have a chance to get to it until Friday hopefully but will let you hnow how it goes. Can you explain this statement to me?

max(if(service_id_service=1, 1, 0)) as JET

I am not sure what the max statement means so a brief explanation to get me started would be appreciated.

Thanks a lot for all your time and help.

Dave

[dickersonka]

basically there will be rows, that will be null (its set to 0 in the the if statement), because the rows are columns, if the service_id != 1 then the row will be null

therefore, the max will pull the highest, which will be 1 if the entry is there

think of it like for columns JET GROUND and GLYCOL

1 0 0
0 1 0
0 0 1

the max, allows us to group these three rows and select "1" if the service is enabled, although its in a different row

let me know if i need to explain it a little better

[filch]

Hey ... just wanted to let you know that I got this working .. and now understand this quite a bit more, thanks to you. I really do appreciate your help.

Cheers

Dave

[filch]

OK .. so this is the last part of this ( and I bet you thought you were done with me didn't you? ).

If I wanted to modify the SQL statement you gave me for an update form in which I wanted to show the data just for that user (based in the usr_id passed in the URL string) and for that user, I need to show all of the airports and services available but with the users selections for airports and services checked and the others not?

I have been trying it with multiple recordsets but I am thinking there is a more efficient way?

Dave

[dickersonka]

not quite sure what you are meaning, show me what you want your result set to be and what your current query is

[filch]

Well, I have attached an image of what I need the layout to be and how it shows ALL of the airports and all three services for each. It also then shows the airports that are selected with their respective services by means of a checkbox.

At this point, while I know I need a more flexible query, I am basically using three separate querys to make this work so far. The problem with it is that all the airports and services get displayed OK ... but they are all checked.

First Query to get the user data:

SELECT *
FROM users
WHERE usr_id = Paramusr_id

Then I am using two queries to get all of the airports and all of the services. The reason I am going this is that I am building a repeat region for the airports, and a nested repeat region for the services is each row that displays the airport current record (see attached image).

SELECT *
FROM airport

SELECT *
FROM service

Then I am getting the records from the userairportservices table where the usr_id_usr matches the usr_id passed in the URL string

SELECT *
FROM userairportservices
WHERE userairportservices.usr_id_users = usr_id

I suspect I need to do a JOIN on these. As far as the layout goes, I am not necessarily stuck on having to do the nested repeat so if the query combines the data properly that I can do a single repeat region and then put the service select boxes in a row with the airport name, code and select box, that will work as well.

Thanks

Dave

[filch]

OK . so here is the beginning of a revised query:

SELECT u.usr_id AS ID, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, uas.airport_id_airport AS AIRPORTID, uas.service_id_service AS SERVICEID FROM users u INNER JOIN userairportservices uas ON uas.usr_id_users = u.usr_id

This pulls up the results shown in the attached image. What I need to do now is combine this query somehow with another that selects all of the airports, all of the services -> airports (services for each airport), which would show the user id, the airport id, a 1 or 0 for the airport checked or unchecked, the services for that airport and a 1 or 0 for selected or unselected checkbox.

I thought I was getting the hang of this, and I suppose to a certain degree I am but either I am over-complicating again or this IS complicated. Not sure which. ;-)

Dave

[filch]

Wondering why this does not work?

SELECT u.usr_id, a.airport_name, a.airport_id, s.service_id, uas.service_id_service as chosen FROM airport a INNER JOIN service s INNER JOIN users u USING (usr_id) LEFT JOIN userairportservices uas USING (airport_id_airport, service_id_service) ORDER BY u.usr_id, a.airport_name, s.service

Throws the error shown in the attached image. Grrrr!!!!

Dave

[dickersonka]

you need to specify inner join on (columnname)
maybe i just don't get it, lol but i cannot understand what you are trying to do here

[filch]

OK .. I am sure I am not explaining this clearly ??

I need to dynamically list all the airport names and their codes plus a checkbox (which needs to be checked if the user has access to the airport) as well as, in the same row, all the services with a checkbox for each, that are checked or unchecked. depending on whether the database shows the user as having access to the service at this particular airport. Sort of what you gave me before but I need to show all the airports > services, not just the ones that the user has access to. This is because this is part of an update form where the admin may be adding or removing access to an airport that was not previously selected.

I might be able to use PHP and the previous query you gave me to get what I need but I am thinking that there is a way to do this completely in a query. This would be quicker and more efficient I would think.

Dave

[dickersonka]

i don't have a mysql database in front of me right now, but trying changing that statement i gave you, to a right join instead of inner

[filch]

Looks to be the same results.

Dave

[dickersonka]

reverse the statement and use a left

select ....
from airports
left join user_airport_services
on ....