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

Question

dickersonka 104 Veteran Poster

16 Years Ago

Correct, if you delete an airport or service then all records that reference that airport or service needs to be deleted as well

deletes on those CAN only happen in the primary tables(airport, services)

what i was referring to was if a user has a service, then the service is removed for that user only, the delete needs to happen in the uas table
ex (userid 1 has ground and glycol for airport id 2, now we want to remove ground for user 1 for airport 2, we only delete the uas record for that user in the uas table)

dickersonka 104 Veteran Poster

16 Years Ago

see this database stuff isn't so hard now is it :-)

dickersonka 104 Veteran Poster

16 Years Ago

you sure you mean $aid instead of $a_id

also your update statement needs to be based on uas_id, otherwise , you will have multiple rows for each user in the update

dickersonka 104 Veteran Poster

16 Years Ago

ahhhh, well we have no deletes here

i don't really like this option, but would you want to delete all the rows for that user_id prior to doing updates and inserts?

(edit: stupid me, there will be no updates if the rows are deleted :-) )

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

filch 0 Junior Poster · Answer 1 · 2008-11-24T03:43:49+00:00

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

dickersonka 104 Veteran Poster · Answer 2 · 2008-11-24T03:53:53+00:00

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

filch 0 Junior Poster · Answer 3 · 2008-11-24T04:05:32+00:00

yes, except the left join on user_id

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

filch 0 Junior Poster · Answer 4 · 2008-11-24T08:52:14+00:00

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

filch 0 Junior Poster · Answer 5 · 2008-11-24T13:35:35+00:00

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?

dickersonka 104 Veteran Poster · Answer 6 · 2008-11-24T19:51:30+00:00

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);

dickersonka 104 Veteran Poster · Answer 7 · 2008-11-24T20:01:07+00:00

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

filch 0 Junior Poster · Answer 8 · 2008-11-24T22:25:12+00:00

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

dickersonka 104 Veteran Poster · Answer 9 · 2008-11-24T22:34:28+00:00

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

filch 0 Junior Poster · Answer 10 · 2008-11-24T22:50:27+00:00

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.

filch 0 Junior Poster · Answer 11 · 2008-11-24T23:10:27+00:00

filch 0 Junior Poster

16 Years Ago

Right .. OK. Thanks. ;-)

filch 0 Junior Poster · Answer 12 · 2008-11-24T23:49:31+00:00

I am using the following:

$userid = $_GET['usr_id'];
  echo "USER ID: " . $userid . "<br />"; 
  $a_id = $_POST['airport_id'];
  /*echo print_r($linkID);*/
  echo print_r($a_id);
 

	foreach ($a_id as $aid => $chosen) {
		foreach ($chosen as $sid => $v) {
		$query = "SELECT userairportservices_id FROM userairportservices WHERE usr_id_users = $userid AND service_id_service = $v AND airport_id_airport = $aid";
		$uasid= $db->getOne($query);
		if ($count>0){
			$query = "UPDATE userairportservices SET service_id_service = $v, airport_id_airport = $aid WHERE usr_id_users = $userid";
		} else {
			$query = "INSERT INTO userairportservices (usr_id_users, service_id_service, airport_id_airport) VALUES ($_SESSION[$WA_sessionName], $v, $aid)";
		}  
		$db->query($query) or die('Error, update of uas table failed');
		}
	}

I am getting nothing just a blank page ... although the user_id is correct. It seems that the code in red is causing the following php error:

[24-Nov-2008 12:31:43] PHP Fatal error:  Call to a member function on a non-object in /admin/members/mem_Update.php on line 257

Dave

filch 0 Junior Poster · Answer 13 · 2008-11-25T01:22:01+00:00

you sure you mean $aid instead of $a_id
also your update statement needs to be based on uas_id, otherwise , you will have multiple rows for each user in the update

RE: $aid vrs $a_id: yes I am pretty sure that is correct. It works perfectly in my INSERT page.

The error, however, seems to be because I have no $db object. This is OOP and there is no object class or constructor for this. Is there another way to get the same functionality as the $db->getOne()?

I have changed the code to reflect yourr comment. Is this what you are referring to?

$userid = $_GET['usr_id'];
  echo "USER ID: " . $userid . "<br />"; 
  $a_id = $_POST['airport_id'];
  /*echo print_r($linkID);*/
  echo print_r($a_id);
  $db = mysql_select_db($database_conFSM2, $conFSM2);

	foreach ($a_id as $aid => $chosen) {
		foreach ($chosen as $sid => $v) {
		$query = "SELECT userairportservices_id FROM userairportservices WHERE usr_id_users = $userid AND service_id_service = $v AND airport_id_airport = $aid";
		$uasid = $db->getOne($query);
		if ($count>0){
			$query = "UPDATE userairportservices SET service_id_service = $v, airport_id_airport = $aid WHERE userairportservices_id = $uasid";
		} else {
			$query = "INSERT INTO userairportservices (usr_id_users, service_id_service, airport_id_airport) VALUES ($_SESSION[$WA_sessionName], $v, $aid)";
		}  
		$db->query($query) or die('Error, update of uas table failed');
		}
	}

filch 0 Junior Poster · Answer 14 · 2008-11-25T01:32:12+00:00

Sorry ... let me make sure I understand what is going on here:

1. I have a multidimensional array here created from the combination of the airport_id checkboxes and the service_id checkboxes.
2. I am looping through the array with the foreach loops.
3. As each loop happens, we query the dB to get the current userairportservices_id for all records that match the user_id AND the service_id AND the airport_id (is this correct?) ( this is confusing because this says that all three conditions must be met to have a match ... doesn't it?)
4. then, in theory, using this $db->getOne($query), we are pulling the first column in the first row out. (this is where the problem is I believe)
5. then we check if we have some using the $count>0 ??
6. if we do we UPDATE
7. if we do not, we INSERT

Is that about it?

Dave

dickersonka 104 Veteran Poster · Answer 15 · 2008-11-25T01:35:44+00:00

sorry about that, all you need to do see if there is a result or not

here's a link if you are ever interested
http://pear.activeventure.com/package/package.database.db.db-common.getone.html

dickersonka 104 Veteran Poster · Answer 16 · 2008-11-25T01:38:15+00:00

thats it man

the reason why you have to query all three for the uas, is because user_id has multiple airports and multiple services for each, without a uas_id then you need to query against all those

dickersonka 104 Veteran Poster · Answer 17 · 2008-11-25T01:40:13+00:00

you can also use mysql_num_rows to the same effect
http://us.php.net/mysql_num_rows

filch 0 Junior Poster · Answer 18 · 2008-11-25T03:07:36+00:00

Well it is still not updating and is still pretty weird to me. Here is what I have so far:

foreach ($a_id as $aid => $chosen) {
		foreach ($chosen as $sid => $v) {		
				if (isset($_GET['usr_id'])) {
  				$userid_rsUasID = (get_magic_quotes_gpc()) ? $_GET['usr_id'] : addslashes($_GET['usr_id']);			
			}		
				if (isset($v)) {
  				$v_rsUasID = (get_magic_quotes_gpc()) ? $v : addslashes($v);
			}
			
				if (isset($aid)) {
  			$aid_rsUasID = (get_magic_quotes_gpc()) ? $aid : addslashes($aid);
			}
			mysql_select_db($database_conFSM2, $conFSM2);
			$query_rsUasID = sprintf("SELECT userairportservices.userairportservices_id FROM userairportservices WHERE userairportservices.usr_id_users = $userid_rsUasID AND userairportservices.service_id_service = $v_rsUasID AND userairportservices.airport_id_airport = $aid_rsUasID", GetSQLValueString($userid_rsUasID, "int"),GetSQLValueString($v_rsUasID, "int"),GetSQLValueString($aid_rsUasID, "int"));
			$rsUasID = mysql_query($query_rsUasID, $conFSM2) or die(mysql_error());
			$row_rsUasID = mysql_fetch_assoc($rsUasID);
			$totalRows_rsUasID = mysql_num_rows($rsUasID);
if ($totalRows_rsUasID>0){
			$query = "UPDATE userairportservices SET service_id_service = $v, airport_id_airport = $aid WHERE userairportservices_id = $uasid[userairportservices_id]";
		} else {
			$query = "INSERT INTO userairportservices (usr_id_users, service_id_service, airport_id_airport) VALUES ($userid, $v, $aid)";
		}  
		mysql_query($query) or die('Error, update of UAS table failed');
		}
	}

This does not give me an SQL error but it also does not update the table. Also the ouput of the uasid array gives you:

40
40
42
42
42
52
51
45
45
49
47
50
50
50

One number for every loop. Doesn't seem right does it?

Dave

dickersonka 104 Veteran Poster · Answer 19 · 2008-11-25T03:18:12+00:00

there will be an entry in the array for each service for that user, the part i'm not sure about is how are you having duplicate id's?
40
40
40?

42
42
42?

filch 0 Junior Poster · Answer 20 · 2008-11-25T06:14:05+00:00

There is something here that does not seem quite right (God I want to be done with this ;-))

Here is the SQL to find all matches:

SELECT uas.userairportservices_id FROM userairportservices uas WHERE uas.usr_id_users = userid AND uas.service_id_service = v AND uas.airport_id_airport = aid

Look right but the only time this matches is when the user_id matches and the value of v and aid is 1. there should only be one match but it says there are 13, What the hell??

Dave

dickersonka 104 Veteran Poster · Answer 21 · 2008-11-25T06:17:56+00:00

print the query right before its executed once values are subbed in, need to see what is happening here

filch 0 Junior Poster · Answer 22 · 2008-11-26T07:04:36+00:00

Ok ... I am back. I had a minor freak out because I am under considerable pressure to get this done and then my machine started to crash and then my page code got out of control etc ...etc. On and on it goes!!!

OK ... I feel better now.

So, I have the update page updating the users data and the airport selections along with the service selections for each airport .... that is, as long as I am adding them. However, when I want to remove an airport or services from the selection list, it does not do it. It is hopefully something simple that I have missed. Here is my current SQL/PHP:

/* begin update/insert routine to update UAS table */ 
  //echo print_r($_POST['airport_id']);
  $userid = $_GET['usr_id'];
  /* put $_POST array into variable */
  $a_id = $_POST['airport_id'];
  
  foreach ($a_id as $aid => $chosen) {
		foreach ($chosen as $sid => $v) {
			$userid_rsUasID = "-1";
			if (isset($userid)) {
  				$userid_rsUasID = $userid;
			}
			$v_rsUasID = "-1";
			if (isset($v)) {
  				$v_rsUasID = $v;
			}
			$aid_rsUasID = "-1";
			if (isset($aid)) {
  				$aid_rsUasID = $aid;
			}
			/* get the current uas_id, depending on what the user_id, airport_id and service_id is, which is derived from the foreach loops*/
			mysql_select_db($database_conFSM2, $conFSM2);
			$query_rsUasID = sprintf("SELECT userairportservices.userairportservices_id FROM userairportservices WHERE userairportservices.usr_id_users = %s AND userairportservices.service_id_service = %s AND userairportservices.airport_id_airport = %s", GetSQLValueString($userid_rsUasID, "int"),GetSQLValueString($v_rsUasID, "int"),GetSQLValueString($aid_rsUasID, "int"));
			$rsUasID = mysql_query($query_rsUasID, $conFSM2) or die(mysql_error());
			$row_rsUasID = mysql_fetch_assoc($rsUasID);
			$totalRows_rsUasID = mysql_num_rows($rsUasID);
			$uasid = $row_rsUasID['userairportservices_id'];
			//echo "uas_id is $uasid";
			if ($totalRows_rsUasID>0){
			/* we have found at least one match currently in the UAS table */
			$query = "UPDATE userairportservices SET service_id_service = $v, airport_id_airport = $aid WHERE userairportservices_id = $uasid";
		} else {
			/* there is no matching record in the UAS table so INSERT a new record */
			$query = "INSERT INTO userairportservices (usr_id_users, service_id_service, airport_id_airport) VALUES ($userid, $v, $aid)";
		}  
		mysql_query($query) or die('Error, update of UAS table failed');
		}
	}

I am wondering if somehow the comparison or condition string is missing something when we take a selection away?

Dave

filch 0 Junior Poster · Answer 23 · 2008-11-26T07:36:28+00:00

Well I had been working along the line of deleting all the records belonging to the usr_id before INSERT or UPDATE. However, this is where I ran into trouble with the constraints. I would have to delete the user from the users table, which would then delete from the UAS table via the cascade, and then re-insert all the data.

What were you thinking?

Dave

dickersonka 104 Veteran Poster · Answer 24 · 2008-11-26T07:39:31+00:00

noooo, not the user table

this is what i was meaning, i think you are using $u_id

delete * from useraccessservices where user_id = $u_id

maintain the user table, how do you get to the screen where you update your services? have to log in or anything? you should know a user id if you are loading fields based upon it

filch 0 Junior Poster · Answer 25 · 2008-11-26T07:43:40+00:00

Yeah I have the usr_id no problem but the uas table will NOT allow deletes if a matching user exists in the users table because a user cannot exist in the users table without a related id in the uas table.

At least that is what I think was happening when I tried the delete route before.

dickersonka 104 Veteran Poster · Answer 26 · 2008-11-26T07:45:55+00:00

should be the opposite, user_id can't exist in the uas table without being in the user_table

delete from the uas table only, not user table

try to run the command without php and see if you get any errors