943,948 Members | Top Members by Rank

Web Development > Databases > MySQL > Pulling related data from relational dB re: thread: Problems with a many-to-many inse
Ad:
  • MySQL Discussion Thread
  • Marked Solved
  • Views: 4857
  • MySQL RSS
You are currently viewing page 5 of this multi-page discussion thread; Jump to the first page
Permalink
Nov 24th, 2008
0

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

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)
Reputation Points: 133
Solved Threads: 141
Veteran Poster
dickersonka is offline Offline
1,162 posts
since Aug 2008
Permalink
Nov 24th, 2008
0

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

Right .. OK. Thanks. ;-)
Reputation Points: 10
Solved Threads: 1
Junior Poster
filch is offline Offline
132 posts
since Nov 2008
Permalink
Nov 24th, 2008
0

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

see this database stuff isn't so hard now is it :-)
Reputation Points: 133
Solved Threads: 141
Veteran Poster
dickersonka is offline Offline
1,162 posts
since Aug 2008
Permalink
Nov 24th, 2008
0

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

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:

MySQL Syntax (Toggle Plain Text)
  1. [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
Last edited by filch; Nov 24th, 2008 at 1:51 pm.
Reputation Points: 10
Solved Threads: 1
Junior Poster
filch is offline Offline
132 posts
since Nov 2008
Permalink
Nov 24th, 2008
0

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

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
Reputation Points: 133
Solved Threads: 141
Veteran Poster
dickersonka is offline Offline
1,162 posts
since Aug 2008
Permalink
Nov 24th, 2008
0

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

Click to Expand / Collapse  Quote originally posted by dickersonka ...
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');
		}
	}
Reputation Points: 10
Solved Threads: 1
Junior Poster
filch is offline Offline
132 posts
since Nov 2008
Permalink
Nov 24th, 2008
0

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

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
Reputation Points: 10
Solved Threads: 1
Junior Poster
filch is offline Offline
132 posts
since Nov 2008
Permalink
Nov 24th, 2008
0

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

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/packag...on.getone.html
Last edited by dickersonka; Nov 24th, 2008 at 3:36 pm.
Reputation Points: 133
Solved Threads: 141
Veteran Poster
dickersonka is offline Offline
1,162 posts
since Aug 2008
Permalink
Nov 24th, 2008
0

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

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
Reputation Points: 133
Solved Threads: 141
Veteran Poster
dickersonka is offline Offline
1,162 posts
since Aug 2008
Permalink
Nov 24th, 2008
0

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

you can also use mysql_num_rows to the same effect
http://us.php.net/mysql_num_rows
Reputation Points: 133
Solved Threads: 141
Veteran Poster
dickersonka is offline Offline
1,162 posts
since Aug 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: Which is better? Tons of separate tables with a little data or one big table?
Next Thread in MySQL Forum Timeline: update in steps? is it possible ?





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC