大型MySQL查询和插入

Question

hey everyone im new here as well as to php only been at it for about 2 months kinda learning as i go. what i need to get done right now is to take data from about 16 different tables in a data base and duplicate the tables. the table are for applicants and either have an application id in common or a family id in commmon the id's are just genereated hashes. what im doing right now is individually wquerying every table and then doing an insert using the data and just changing the application id and or family id. some tables are pretty big and im trying to figuere out an easier faster way to do this heres an example piece iv writen for one of my tables.

 <?
      //get family info based on application id
 $queryC="SELECT Fam_Type, Fam_Title, Fam_SSNI20, Fam_SSNI20_Select, Fam_Name_First, Fam_Name_Middle, Fam_Name_Last, Fam_Name_Suffix, Fam_Gender, Fam_DOB, Fam_Disabled, Fam_Addy1, Fam_Addy2, Fam_City, Fam_State_Prov, Fam_Zip, Fam_Country, Fam_Home_Phone, Fam_Work_Phone, Fam_Work_Phone_Ext, Fam_Cell_Phone, Fam_Occupation, Fam_Employer, Fam_Primary_Res, Fam_Custody, Fam_Guard_Excuse, Fam_ChiDep_Ans, Fam_ReaVeh_Ans, InstructDone, SetDone, AppDone, HouDone, DepDone, AssDone, ReaDone, IncDone, ExpDone, QueDone, ApplicationDone, StatusDone,  Fam_Init, Fam_Start_Date, Fam_End_Date, Fam_Agree, username, password, email, Fam_Letter, Exp_Educational, Fam_Tax, Scan_FileNames, Scan_Descriptions, Fam_Complete, referral, Fam_Holds, appealed, Appeal_Letter, Appeal_Viewed, taxDocuments        
     FROM familY
     WHERE familyID='$famid' AND applicationID='$appid'";
     $resultC = mysql_db_query($aidDB, $queryC, $connection); echo $queryC;
     while($rC=mysql_fetch_array($resultC)){
       $Fam_Type="$rC['0']";
       $Fam_Title="$rC['1']";
       $Fam_SSNI20="$rC['2']";
       $Fam_SSNI20_Select="$rC['3']"; 
       $Fam_Name_First="$rC['4']";
       $Fam_Name_Middle="$rC['5']";
       $Fam_Name_Last="$rC['6']";
       $Fam_Name_Suffix ="$rC['7']";
       $Fam_Gender="$rC['8']";
       $Fam_DOB="$rC['9']";
       $Fam_Disabled="$rC['10']";
       $Fam_Addy1 ="$rC['11']";
       $Fam_Addy2 ="$rC['12']";
       $Fam_City="$rC['13']";
       $Fam_State_Prov="$rC['14']";
       $Fam_Zip="$rC['15']";
       $Fam_Country="$rC['16']";
       $Fam_Home_Phone ="$rC['17']";
       $Fam_Work_Phone  ="$rC['18']";
       $Fam_Work_Phone_Ext="$rC['19']";
       $Fam_Cell_Phone ="$rC['20']";
       $Fam_Occupation ="$rC['21']";
       $Fam_Employer="$rC['22']";
       $Fam_Primary_Res ="$rC['23']";
       $Fam_Custody="$rC['24']";
       $Fam_Guard_Excuse ="$rC['25']";
       $Fam_ChiDep_Ans ="$rC['26']";
       $Fam_ReaVeh_Ans="$rC['27']";
       $InstructDone ="$rC['28']";
       $SetDone ="$rC['29']";
       $AppDone ="$rC['30']";
       $HouDone ="$rC['31']";
       $DepDone ="$rC['32']";
       $AssDone="$rC['33']";
       $ReaDone ="$rC['34']";
       $IncDone ="$rC['35']";
       $ExpDone ="$rC['36']";
       $QueDone="$rC['37']";
       $ApplicationDone ="$rC['38']";
       $StatusDone ="$rC['39']";
       $Fam_Init="$rC['40']";
       $Fam_Start_Date ="$rC['41']";
       $Fam_End_Date ="$rC['42']";
       $Fam_Agree ="$rC['43']";
       $username ="$rC['44']";
       $password ="$rC['45']";
       $email ="$rC['46']";
       $Fam_Letter="$rC['47']";
       $Exp_Educational ="$rC['48']";
       $Fam_Tax ="$rC['49']";
       $Scan_FileNames="$rC['50']";
       $Scan_Descriptions ="$rC['51']";
       $Fam_Complete ="$rC['52']";
       $referral="$rC[''53]";
       $Fam_Holds="$rC['54']";
       $appealed="$rC['55']";
       $Appeal_Letter="$rC['56']";
       $Appeal_Viewed="$rC['57']";
       $taxDocuments="$rC['58']";

//insert into family with new applicationid
$queryC2="INSERT INTO family (familyID, applicationID, Fam_Type, Fam_Title, Fam_SSNI20, Fam_SSNI20_Select, Fam_Name_First, Fam_Name_Middle, Fam_Name_Last, Fam_Name_Suffix, Fam_Gender, Fam_DOB, Fam_Disabled, Fam_Addy1, Fam_Addy2, Fam_City, Fam_State_Prov, Fam_Zip, Fam_Country, Fam_Home_Phone, Fam_Work_Phone, Fam_Work_Phone_Ext, Fam_Cell_Phone, Fam_Occupation, Fam_Employer, Fam_Primary_Res, Fam_Custody, Fam_Guard_Excuse, Fam_ChiDep_Ans, Fam_ReaVeh_Ans, InstructDone, SetDone, AppDone, HouDone, DepDone, AssDone, ReaDone, IncDone, ExpDone, QueDone, ApplicationDone, StatusDone,  Fam_Init, Fam_Start_Date, Fam_End_Date, Fam_Agree, username, password, email, Fam_Letter, Exp_Educational, Fam_Tax, Scan_FileNames, Scan_Descriptions, Fam_Complete, referral, Fam_Holds, appealed, Appeal_Letter, Appeal_Viewed, taxDocuments   )
    VALUES
    (
     '$newfam1id,'        
     '$newappid,'
     '$Fam_Type,' 
     '$Fam_Title,'
     '$Fam_SSNI20,'
     '$Fam_SSNI20_Select,'
     '$Fam_Name_First,'
     '$Fam_Name_Middle, '
     '$Fam_Name_Last,'
     '$Fam_Name_Suffix,'
     '$Fam_Gender,' 
     '$Fam_DOB,'
     '$Fam_Disabled,'
     '$am_Addy1,' 
     '$Fam_Addy2,' 
     '$Fam_City,'
     '$Fam_State_Prov,'
     '$Fam_Zip,'
     '$Fam_Country,'
     '$Fam_Home_Phone,'
     '$Fam_Work_Phone, '
     '$Fam_Work_Phone_Ext,'
     '$Fam_Cell_Phone,'
     '$Fam_Occupation,'
     '$Fam_Employer,'
     '$Primary_Res,'
     '$Fam_Custody,' 
     '$Fam_Guard_Excuse,'
     '$Fam_ChiDep_Ans,'
     '$Fam_ReaVeh_Ans,' 
     '$InstructDone,' 
     '$SetDone,'
     '$AppDone,'
     '$HouDone,'
     '$DepDone,'
     '$AssDone,'
     '$ReaDone,'
     '$IncDone,'
     '$ExpDone,'
     '$QueDone,'
     '$ApplicationDone,'
     '$StatusDone,' 
     '$Fam_Init,' 
     '$Fam_Start_Date,'
     '$Fam_End_Date,'
     '$Fam_Agree,' 
     '$username,'
     '$password,'
     '$email,'
     '$Fam_Letter,'
     '$Exp_Educational,'
     '$Fam_Tax,'
     '$Scan_FileNames,' 
     '$Scan_Descriptions,'
     '$Fam_Complete,' 
     '$referral,' 
     '$Fam_Holds,' 
     '$appealed,' 
     '$Appeal_Letter,' 
     '$Appeal_Viewed,' 
     '$taxDocuments'   
    )"; echo $queryC2;

     }



 ?>

Answer 1

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.