 |  |  |  |  |  |  |  |
Pulling related data from relational dB re: thread: Problems with a many-to-many inse
Thread Solved |
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation: 
Solved Threads: 139
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#41 Nov 24th, 2008
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)
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)
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
•
•
Join Date: Nov 2008
Posts: 84
Reputation:
Solved Threads: 1
Junior Poster in Training
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#42 Nov 24th, 2008
Right .. OK. Thanks. ;-)
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation:
Solved Threads: 139
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#43 Nov 24th, 2008
see this database stuff isn't so hard now is it :-)
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
•
•
Join Date: Nov 2008
Posts: 84
Reputation:
Solved Threads: 1
Junior Poster in Training
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many
0
#44 Nov 24th, 2008
I am using the following:
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:
Dave
$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)
Dave
Last edited by filch; Nov 24th, 2008 at 1:51 pm.
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation:
Solved Threads: 139
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#45 Nov 24th, 2008
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
also your update statement needs to be based on uas_id, otherwise , you will have multiple rows for each user in the update
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
•
•
Join Date: Nov 2008
Posts: 84
Reputation:
Solved Threads: 1
Junior Poster in Training
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many
0
#46 Nov 24th, 2008
•
•
•
•
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
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');
}
} •
•
Join Date: Nov 2008
Posts: 84
Reputation:
Solved Threads: 1
Junior Poster in Training
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many
0
#47 Nov 24th, 2008
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
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
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation:
Solved Threads: 139
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#48 Nov 24th, 2008
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
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.
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation:
Solved Threads: 139
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#49 Nov 24th, 2008
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
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
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation:
Solved Threads: 139
Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
0
#50 Nov 24th, 2008
you can also use mysql_num_rows to the same effect
http://us.php.net/mysql_num_rows
http://us.php.net/mysql_num_rows
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
 |
Other Threads in the MySQL Forum
- Previous Thread: Which is better? Tons of separate tables with a little data or one big table?
- Next Thread: update in steps? is it possible ?
Views: 3861 | Replies: 71
| Thread Tools | Search this Thread |
Latest MySQL Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
Tag cloud for MySQL
"use" .db 1 ajax alphabet amazon aws camparingtocolumns cascade cast changingprices convert count count(col) data database delete design distinct ec2 eliminate enter error form generator html images innerjoins insert join keyword keywords matchingcolumns multiple multipletables mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch noob number open operand oracle order pdf php priceupdating print query recursive relationaldatabases relationship relationships remove reorderingcolumns resultset retrieve search select simpledb sort sql statement syntax table tree type-conversion update welsh
