MySQL - INSERT function

[aussieinnyc]

Hi All,

I am having some trouble with the insert function in my script...The dbinfo.inc.php file has the login info. I am actually trying to insert data from one table to another in the same database..Please Help!

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM Master WHERE id='$id'";

$query2="INSERT INTO Cancelled (id, AccountName, FirstName, LastName, OrganizationName, EmailAddress, Address, City, State, Zip, HomePhone, WorkPhone, SetUpDate, AccntPassword, AccntType, MonthlyFee, ServerName, FrntPg, RealAudio, MySql, SmartSeller, SmartEdit, RefLogs, ChatVolcano, Reseller, MasterAccnt, ResoldAccntCharge, OtherCharges, PaymentMethod, CreditCardNumber, ExpirationDate, NameonCard, NextBillDate, PaymentComments, Comments, PromoCode, ProductName) VALUES ('$id', '$AccountName', '$FirstName', '$LastName', '$OrganizationName', '$EmailAddress', '$Address', '$City', '$State', '$Zip', '$HomePhone', '$WorkPhone', '$SetUpDate', '$AccntPassword', '$AccntType', '$MonthlyFee', '$ServerName', '$FrntPg', $RealAudio', '$MySql', '$SmartSeller', '$SmartEdit', '$RefLogs', '$ChatVolcano', '$Reseller', '$MasterAccnt', '$ResoldAccntCharge', '$OtherCharges', '$PaymentMethod', '$CreditCardNumber', '$ExpirationDate', '$NameonCard', '$NextBillDate', '$PaymentComments', '$Comments', '$PromoCode', '$ProductName')";

mysql_query($query2);
echo "Record Cancelled";

mysql_close();
?>

[stolzyboy]

first of all you didn't run your first query with mysql_query, and second you need to use a while loop and grab the row vars and set them to the reg vars, like set

$AccountName = $row['AccountName'];

so you can use it in your insert statement

[aussieinnyc]

Thanks for the help, but it still isnt working. I get the message "Record Cancelled" but when I go to check in the Cancelled database there is nothing there. Here is the whole script I am using:

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query2="SELECT * FROM Master WHERE id='$id'";
$result2=mysql_query($query2);
$num=mysql_numrows($result2);
mysql_close();

$i=0;
while ($i < $num) {
$AccountName=mysql_result($result2,$i,"AccountName");
$FirstName=mysql_result($result2,$i,"FirstName");
$LastName=mysql_result($result2,$i,"LastName");
$OrganizationName=mysql_result($result2,$i,"OrganizationName");
$EmailAddress=mysql_result($result2,$i,"EmailAddress");
$Address=mysql_result($result2,$i,"Address");
$City=mysql_result($result2,$i,"City");
$State=mysql_result($result2,$i,"State");
$Zip=mysql_result($result2,$i,"Zip");
$HomePhone=mysql_result($result2,$i,"HomePhone");
$WorkPhone=mysql_result($result2,$i,"WorkPhone");
$SetUpDate=mysql_result($result2,$i,"SetUpDate");
$AccntPassword=mysql_result($result2,$i,"AccntPassword");
$AccntType=mysql_result($result2,$i,"AccntType");
$MonthlyFee=mysql_result($result2,$i,"MonthlyFee");
$ServerName=mysql_result($result2,$i,"ServerName");
$FrntPg=mysql_result($result2,$i,"FrntPg");
$RealAudio=mysql_result($result2,$i,"RealAudio");
$MySql=mysql_result($result2,$i,"MySql");
$SmartSeller=mysql_result($result2,$i,"SmartSeller");
$SmartEdit=mysql_result($result2,$i,"SmartEdit");
$RefLogs=mysql_result($result2,$i,"RefLogs");
$ChatVolcano=mysql_result($result2,$i,"ChatVolcano");
$Reseller=mysql_result($result2,$i,"Reseller");
$MasterAccnt=mysql_result($result2,$i,"MasterAccnt");
$ResoldAccntCharge=mysql_result($result2,$i,"ResoldAccntCharge");
$OtherCharges=mysql_result($result2,$i,"OtherCharges");
$PaymentMethod=mysql_result($result2,$i,"PaymentMethod");
$CreditCardNumber=mysql_result($result2,$i,"CreditCardNumber");
$ExpirationDate=mysql_result($result2,$i,"ExpirationDate");
$NameonCard=mysql_result($result2,$i,"NameonCard");
$NextBillDate=mysql_result($result2,$i,"NextBillDate");
$PaymentComments=mysql_result($result2,$i,"PaymentComments");
$Comments=mysql_result($result2,$i,"Comments");
$PromoCode=mysql_result($result2,$i,"PromoCode");
$ProductName=mysql_result($result2,$i,"ProductName");
?>
<?
++$i;
}
?>

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
$query="INSERT INTO Cancelled (id, AccountName, FirstName, LastName, OrganizationName, EmailAddress, Address, City, State, Zip, HomePhone, WorkPhone, SetUpDate, AccntPassword, AccntType, MonthlyFee, ServerName, FrntPg, RealAudio, MySql, SmartSeller, SmartEdit, RefLogs, ChatVolcano, Reseller, MasterAccnt, ResoldAccntCharge, OtherCharges, PaymentMethod, CreditCardNumber, ExpirationDate, NameonCard, NextBillDate, PaymentComments, Comments, PromoCode, ProductName) VALUES ('$id', '$AccountName', '$FirstName', '$LastName', '$OrganizationName', '$EmailAddress', '$Address', '$City', '$State', '$Zip', '$HomePhone', '$WorkPhone', '$SetUpDate', '$AccntPassword', '$AccntType', '$MonthlyFee', '$ServerName', '$FrntPg', $RealAudio', '$MySql', '$SmartSeller', '$SmartEdit', '$RefLogs', '$ChatVolcano', '$Reseller', '$MasterAccnt', '$ResoldAccntCharge', '$OtherCharges', '$PaymentMethod', '$CreditCardNumber', '$ExpirationDate', '$NameonCard', '$NextBillDate', '$PaymentComments', '$Comments', '$PromoCode', '$ProductName')";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Cancelled";

mysql_close();
?>

[stolzyboy]

check out the changes i have done, also it will always echo out record cancelled no matter if query returns true or false the way you have it written, notice the way i have grabbed the values with mysql_fetch_array, instead of the way you had, change all them to what i did and try that

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query2 = "SELECT * FROM Master WHERE id='$id';";
$result2 = mysql_query($query2);

while ($row = mysql_fetch_array($result2))
{
// ----------------------
$AccountName = $row['AccountName']; // use this for each variable, not like you have below
// ----------------------

$FirstName=mysql_result($result2,$i,"FirstName");
$LastName=mysql_result($result2,$i,"LastName");
$OrganizationName=mysql_result($result2,$i,"OrganizationName");
$EmailAddress=mysql_result($result2,$i,"EmailAddress");
$Address=mysql_result($result2,$i,"Address");
$City=mysql_result($result2,$i,"City");
$State=mysql_result($result2,$i,"State");
$Zip=mysql_result($result2,$i,"Zip");
$HomePhone=mysql_result($result2,$i,"HomePhone");
$WorkPhone=mysql_result($result2,$i,"WorkPhone");
$SetUpDate=mysql_result($result2,$i,"SetUpDate");
$AccntPassword=mysql_result($result2,$i,"AccntPassword");
$AccntType=mysql_result($result2,$i,"AccntType");
$MonthlyFee=mysql_result($result2,$i,"MonthlyFee");
$ServerName=mysql_result($result2,$i,"ServerName");
$FrntPg=mysql_result($result2,$i,"FrntPg");
$RealAudio=mysql_result($result2,$i,"RealAudio");
$MySql=mysql_result($result2,$i,"MySql");
$SmartSeller=mysql_result($result2,$i,"SmartSeller");
$SmartEdit=mysql_result($result2,$i,"SmartEdit");
$RefLogs=mysql_result($result2,$i,"RefLogs");
$ChatVolcano=mysql_result($result2,$i,"ChatVolcano");
$Reseller=mysql_result($result2,$i,"Reseller");
$MasterAccnt=mysql_result($result2,$i,"MasterAccnt");
$ResoldAccntCharge=mysql_result($result2,$i,"ResoldAccntCharge");
$OtherCharges=mysql_result($result2,$i,"OtherCharges");
$PaymentMethod=mysql_result($result2,$i,"PaymentMethod");
$CreditCardNumber=mysql_result($result2,$i,"CreditCardNumber");
$ExpirationDate=mysql_result($result2,$i,"ExpirationDate");
$NameonCard=mysql_result($result2,$i,"NameonCard");
$NextBillDate=mysql_result($result2,$i,"NextBillDate");
$PaymentComments=mysql_result($result2,$i,"PaymentComments");
$Comments=mysql_result($result2,$i,"Comments");
$PromoCode=mysql_result($result2,$i,"PromoCode");
$ProductName=mysql_result($result2,$i,"ProductName");
?>
<?
++$i;
}
?>

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
$query="INSERT INTO Cancelled (id, AccountName, FirstName, LastName, OrganizationName, EmailAddress, Address, City, State, Zip, HomePhone, WorkPhone, SetUpDate, AccntPassword, AccntType, MonthlyFee, ServerName, FrntPg, RealAudio, MySql, SmartSeller, SmartEdit, RefLogs, ChatVolcano, Reseller, MasterAccnt, ResoldAccntCharge, OtherCharges, PaymentMethod, CreditCardNumber, ExpirationDate, NameonCard, NextBillDate, PaymentComments, Comments, PromoCode, ProductName) VALUES ('$id', '$AccountName', '$FirstName', '$LastName', '$OrganizationName', '$EmailAddress', '$Address', '$City', '$State', '$Zip', '$HomePhone', '$WorkPhone', '$SetUpDate', '$AccntPassword', '$AccntType', '$MonthlyFee', '$ServerName', '$FrntPg', $RealAudio', '$MySql', '$SmartSeller', '$SmartEdit', '$RefLogs', '$ChatVolcano', '$Reseller', '$MasterAccnt', '$ResoldAccntCharge', '$OtherCharges', '$PaymentMethod', '$CreditCardNumber', '$ExpirationDate', '$NameonCard', '$NextBillDate', '$PaymentComments', '$Comments', '$PromoCode', '$ProductName')";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Cancelled";

mysql_close();
?>

[stolzyboy]

it always helps to use or die(mysql_error()); after your mysql_query's to tell you exactly why it isn't working

[Lars_Berg]

You can use a statement like

insert into t1(c1,c2)
  select c3,c4 from t2
 where c3 = 4711

for copying data from one table to another thus eliminating the need for a loop.

[stolzyboy]

not with current versions of mysql, it doesn't support subqueries, 4.2 does, but that is in alpha stages

[Lars_Berg]

stolzyboy

Check things up before posting please.

[stolzyboy]

what are you talking about lars

[xblue]

probably http://www.mysql.com/doc/en/INSERT_SELECT.html

[stolzyboy]

well thats just genius, they allow you to subquery within an insert, but not until 4.2 can you subquery in a select, lame, maybe i should switch to postgres