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?

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

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

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

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

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

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 ;

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

thanks, how soon do you need it?

cool if i get back with you in the morning?

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

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

Dave

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

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

...ahhhh, a fresh mind

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

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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Looks to be the same results.

Dave

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

reverse the statement and use a left

select ....

from airports

left join user_airport_services

on ....

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

Still looks the same. Is this what you mean?

SELECT userairportservices.usr_id_users, userairportservices.airport_id_airport, airport.airport_code, airport.airport_name, 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 airport LEFT JOIN userairportservices ON userairportservices.airport_id_airport = airport.airport_id

WHERE userairportservices.usr_id_users = usr_id

GROUP BY airport_id_airport LIMIT 0, 30

Dave

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

yes, except the left join on user_id

the where clause is limiting the results to only ones that exist, if the change doesn't show the results properly, then let me know, and it can be modified

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

I'm sorry but you have lost me here ;-(

LEFT JOIN users ON userairportservices.usr_id_users = user.usr_id ???

Sorry to be dull here.

Dave

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

OK I have managed to get this to work. I use a:

SELECT * FROM airport

and a

SELECT * FROM service

to set up a parent

<?php do { } while ?>

loop and another as a nested loop inside of this parent loop.

In the parent loop I have the following:

SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, 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 LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport WHERE uas.usr_id_users = %s AND uas.airport_id_airport = %s GROUP BY airport_id_airport LIMIT 0, 30

This pulls out a single record if it matches the user id AND the airport id. So I can then check if any row was generated. If it was, then that airport was assigned to the user and I can set the checkbox to 'checked'. Further, if a row was generated, I can check for the values of JET, GROUND and GLYCOL and set the checkboxes to checked as necessary.

Still I am sure there is a way to generate the proper row perhaps without using as much code and in using a proper SQL statement. I was not having much luck with the SQL part so I used a combination of SQL and PHP. Not truly elegant but it works and seems to work well.

However, I have reached what I hope is my last major hurdle (yeah right!!). How the hell do I update the userairportservices table. The only way to do that I think, is by knowing the userairportservices_id. But how would we know that as this is an update not an insert?? That is the only unique key in that table??

Dave

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

OK .. seriously .. if you are ever in Toronto, the beer is on me. I am pulling my hair out (what is left of it) because I am so close but have run smack into what seems to be a cement wall.

I can insert a new user. I can delete a user and the delete cascades to the userairportservices table and deletes the user from that table as well ... which is cool.

I can search for a new user and that works as well.

What I cannot do is update a user. My insert is split into two statements .. one inserts the user data, the seomd, using the last_insert_id() inserts the data into the userairportservices table. That all works fine. However, because the userairportservices table can have a bunch of records for a particular user, I cannot figure out a way to update the user table AND the multiple records attached to that user in the userairportservices table. I thought about updating the user data, then deleting all the records from the userairportservices that match the user_id and then inserting the new data in to replace it. However, the foreign key constraints seem to be stopping me from deleting anything in the userairportservices table because a user with the matching id still exists in the users table.

The constraints are set to ON UPDATE CASCADE and ON DELETE CASCADE, but these only function if the action is taken on the parent table, in this case users. How am I supposed to do this?

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

lol whoaaa now

might have to come for toronto, can never turn down a free beer

you almost got it, what you can do is for each checkbox value, query the table, if exists, do an update, if not, do an insert

$query = "SELECT user_airport_services_id FROM user_airport_services WHERE user_id = '$u' AND SERVICE_ID='$s" AND AIRPORT_ID ='$a'";

$uasid= $db->getOne($query);

if ($count>0){

$query = "UPDATE user_airport_services SET ........  WHERE user_airport_services_id=$usaid;

}

else{

$query = "INSERT INTO user_airport_services .........

        }

$db->query($query);

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

also what you could do which would probably be a better solution, just maybe more time consuming, is to create a hidden field that goes with each checkbox that stores the user_airport_services_id, if exists set it, otherwise set it to -1, then you can check if hidden field is -1 insert, otherwise update

<input type="hidden" id="YAVGLYCOL" value="4">

<input type="hidden" id="YAVJET" value="-1">

basic example, but you will know glycol has been assigned and to do an update, and jet hasn't and do an insert

up to you, which way you want to go with my previous post as well

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

Thanks for this. I will give this a try. It seems right to me.

However, I am wondering if some of the problem I was having did not have to do with the key constraints. I tried deleting from the phpMyAdmin interface and got an error, But I guess that there would be a restriction on deleting directly from the uas table as the user cannot exist in the users table without the user_id in the uas table. Correct? But, in theory then, you are saying that the uas should allow and UPDATE (I already know it allows an INSERT) as long as the matching user exists in the user table. Am I going in the right direction?

Dave

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

the uas should allow deletes, inserts, and updates with no cascading, if you think of it, this is a bottom level table, no tables depend upon it, it depends upon other tables

if user_id gets updated in the user table, then the uas table needs updated as well, not the other way around

if the service_id gets updated in the services table, then the uas table needs updated, same way as before, not the other way around

i don't know exactly what error you were getting, but any changes to the uas table should affect key constraints on any other table, as long as user_id, service_id, and airport_id match, deleting or inserting should not affect anything

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

OK well then, I understand that if something on the user table, or the service or airport tables gets updated, the uas table must be updated as well. However, if I delete an airport from the airport table, then all of the entries in the uas table relating to that airport should be deleted too .. is this not correct? Same for service. So, if I delete a user from the user table, all entires in the uas table for that user are removed. I have not tried deleting an airport or a service but that also should work the same way shouldn't it?

So, if this is correct, the uas table should not allow deleting. Deletes should only happen from the primary tables. Maybe I am way off here but that makes sense to me.