ansaurus

Question

Large MySQL query and insert

Answer 1

+2 A:

you do not need to loop all the rows.
use this:

Insert into new_table select * from old_table where {your criteria here}

darko petreski 2010-07-29 13:36:30

Answer 2

+1 A:

mysql has INSERT into table (fields,list) SELECT fields,list FROM another_table syntax

Col. Shrapnel 2010-07-29 13:37:17

Answer 3

A:

you can simply do:

INSERT INTO family ( appliction_id, all_the_other_fields )
SELECT 'your_new_app_id', all_the_other_fields  
FROM familY
WHERE familyID='$famid' AND applicationID='$appid'

let's hope your $famid and $appid variables are escaped, shall we?

knittl 2010-07-29 13:41:48

this is basically what im doing just ur making it into one statement correct? id have to still do this for every table and yes they are escaped

Karl Lenz 2010-07-29 13:45:38

yes, you have to do it for every table, but you are saving yourself a lot of php code (no loop and such). also this version is likely to be faster, because no (table) data is sent between mysql server and php server (only the query is sent)

knittl 2010-07-29 14:12:41

Answer 4

A:

You can reduce the amount of PHP code on the SELECT side of things by using mysql_fetch_assoc() instead of array, especially since you're then just reassigning the returned array chunks to individual variables:

$query = "SELECT Fam_Type, Fam_Title, Fam_SSNI20, Fam_SSNI20_Select etc... etc...";
$resultC = mysql_db_query($aidDB, $queryC, $connection); echo $queryC;
while($rC = mysql_fetch_assoc($resultC)) {
   ...
}

At this point you end up with associated array which you can get at the individual values as:

echo "Fam_Type is " . $rc['Fam_Type'];
echo "Fam_Title is " . $rc['Fam_Title'];

which saves you that long huge blob of variable assignments. Of course, if any of the fields in your query would map internally to the same name (say, you're doing a join and are retrieving two different 'id' fields from two different tables), then you'd need to alias them to different names so they come out distinctly in the array, but that's a simple mod to the query:

SELECT table1.id AS table1_id, table2.id AS table2_id ...

which would leave to:

$result['table1_id'] = ...

As a side node, you should always check to see if the query succeeded. You blindly assume it worked and try to retrieve data. But if the query fails, the mysql functions return FALSE, which would not be a valid result statement and break your fetch attempt. At bare minimum, add some basic error checking

$resultC = mysql_db_query(...) or die("MySQL error: " . mysql_error());

which will spit out the error message and explain why the query failed, instead of then failing with "... is not a valid statement handle" later on. As well, mysql_db_query() is deprecated as of PHP 5.3, and will vanish in some future PHP version. May want to switch to the regular mysql_query() instead.

Marc B 2010-07-29 13:51:34

that was an awesome post man thanks. the mysql_fetch will essentially just assign my variables for me to the name of the field. is there a way i can use this in my insert query?

Karl Lenz 2010-07-29 13:56:16

Unfortunately, not really. There's DB abstraction libraries that could do it for you, and certain tricks to build the query string dynamically, but nothing automatic like this.

Marc B 2010-07-31 02:35:22

ansaurus

tags:

views:

answers:

Large MySQL query and insert

related questions