I have 3 seperate tables with data, all of which have their own auto_increment ID column. I have to somehow relate all of the IDs into a 4th table given a set of search values. Hopefully my example helps explain.
Simple Example:
SELECT car_id AS carID FROM tbl_cars WHERE str_make = 'Honda' and str_model = 'Accord';
SELECT pet_id AS petID FROM tbl_pets WHERE str_petname = 'Scruffy';
SELECT person_id AS personID FROM tbl_people WHERE str_name = 'Doe, John';
INSERT INTO tbl_relationship (car_id,pet_id,person_id) VALUES ('carID','petID','personID');
So I have a list of some 40,000 entries, each with 3 columns of data. They have all been broken down and put into seperate tables with a bash shell script, now what I want to do is go back over the list again and relate the data in the 4th table, just like my example shows.
The only way i see this possible is if I use php to query my DB 120,000 times, that dosn't seem logical. This sort of thing would only have to be run once though every few months. So any ideas?
Thanks!$db = mysql_connect($host,$user,$pass);
mysql_select_db($dbase);
$query=mysql_query("SELECT car_id as carID, pet_id as petID, person_id as personID FROM tbl_cars, tbl_pets, tbl_people WHERE str_make = 'Honda' AND str_model = 'Accord' AND str_petname = 'Scruffy' AND str_name = 'Doe, John'");
while($result = mysql_fetch_array($query)){
$carID = $result['carID'];
$petID = $result['petID'];
$personID = $result['personID'];
mysql_unbuffered_query("INSERT INTO tbl_relationship (car_id, pet_id, person_id) VALUES ('$carID','$petID','$personID')");
}
mysql_close($db);
... I THINK that should work... but you may have to make some minor adjustments to the query if you have redundant field names...
Hope that helps.
Simple Example:
SELECT car_id AS carID FROM tbl_cars WHERE str_make = 'Honda' and str_model = 'Accord';
SELECT pet_id AS petID FROM tbl_pets WHERE str_petname = 'Scruffy';
SELECT person_id AS personID FROM tbl_people WHERE str_name = 'Doe, John';
INSERT INTO tbl_relationship (car_id,pet_id,person_id) VALUES ('carID','petID','personID');
So I have a list of some 40,000 entries, each with 3 columns of data. They have all been broken down and put into seperate tables with a bash shell script, now what I want to do is go back over the list again and relate the data in the 4th table, just like my example shows.
The only way i see this possible is if I use php to query my DB 120,000 times, that dosn't seem logical. This sort of thing would only have to be run once though every few months. So any ideas?
Thanks!$db = mysql_connect($host,$user,$pass);
mysql_select_db($dbase);
$query=mysql_query("SELECT car_id as carID, pet_id as petID, person_id as personID FROM tbl_cars, tbl_pets, tbl_people WHERE str_make = 'Honda' AND str_model = 'Accord' AND str_petname = 'Scruffy' AND str_name = 'Doe, John'");
while($result = mysql_fetch_array($query)){
$carID = $result['carID'];
$petID = $result['petID'];
$personID = $result['personID'];
mysql_unbuffered_query("INSERT INTO tbl_relationship (car_id, pet_id, person_id) VALUES ('$carID','$petID','$personID')");
}
mysql_close($db);
... I THINK that should work... but you may have to make some minor adjustments to the query if you have redundant field names...
Hope that helps.