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