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

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

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

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

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

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

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

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

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

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

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

you are meaning sql or php?

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

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

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

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?

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

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

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

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