THE BORE

General => The Superdeep Borehole => Topic started by: GilloD on May 27, 2009, 08:29:26 PM

Title: SQL dudes, question
Post by: GilloD on May 27, 2009, 08:29:26 PM

I'm copying a row between two tables. Here's mah code:

$curr_po_num is the Purchase Order # of the current row, of the row that triggered the script.
Basically, I'm saying that IF the current purchase order # EQUALS the PO # of a record in the first table (new_batch), COPY that record to the second table (archive_batch)
 

Code: [Select]

$queryall="SELECT * FROM new_batch WHERE po_num=$curr_po_num";
$result=mysql_query($queryall) or die(mysql_error());
while($row=mysql_fetch_array($result)){
$swap="INSERT INTO archive_batch(batch_num,customername,po_num,carrier,pro_num,isNew,isShipped)
   VALUES ('$row[batch_num],'$row[customername],'$row[po_num],'$row[carrier],'$row[pro_num],'$row[isNew],'$row[isShipped])";
echo "BATCH NUM: '$row[batch_num]'";
$swapresult = @mysql_query($swap);
echo "RECORD SWAPPED";
}

Now, the BATCH NUM echoes fine. I got the "Record Swapped" message just once, like I should be. I should NEVER get the message more than once, no two orders will EVER have the same PO#.

However, the row never gets created in archive_batch. The tables are identical, I figure I must either have a simple typo goof or a simple logic goof. Thoughts?

Title: Re: SQL dudes, question
Post by: Bocsius on May 28, 2009, 01:03:00 AM

Looks like you're missing some apostrophes. '$row[po_num] should nave a trailing apostrophe before the comma, for example. It should be noted that if any of the actual field values being inserted have apostrophes in them, it could blow the statement up. Also, if any of the fields actually contain numeric data, lose the apostrophes for those fields.

Another way to insert your record is to construct a statement like this:

Code: [Select]

Insert Into archive_batch (batch_num, customername, po_num, carrier, pro_num, isNew, isShipped)
Select batch_num, customername, po_num, carrier, pro_num, isNew, isShipped
From new_batch
Where po_num = $curr_po_num

Title: Re: SQL dudes, question
Post by: recursivelyenumerable on May 28, 2009, 02:35:27 AM

what Bocsius said (re the query; I'm not too familiar with Perl or PHP or whatever the language is).  don't use procedural code unless you have to.

Title: Re: SQL dudes, question
Post by: GilloD on May 28, 2009, 10:25:37 AM

Thanks, guys. I'm still pretty new to SQL, so some of the finer points and consequences have eluded me. And yes, it's a dopey ' problem. Stupid.

Title: Re: SQL dudes, question
Post by: GilloD on May 28, 2009, 12:23:08 PM

Quote from: Bocsius on May 28, 2009, 01:03:00 AM

Looks like you're missing some apostrophes. '$row[po_num] should nave a trailing apostrophe before the comma, for example. It should be noted that if any of the actual field values being inserted have apostrophes in them, it could blow the statement up. Also, if any of the fields actually contain numeric data, lose the apostrophes for those fields.

Another way to insert your record is to construct a statement like this:

Code: [Select]

Insert Into archive_batch (batch_num, customername, po_num, carrier, pro_num, isNew, isShipped)
Select batch_num, customername, po_num, carrier, pro_num, isNew, isShipped
From new_batch
Where po_num = $curr_po_num

Weird. This worked once and then wouldn't work for other records. Odd.

Title: Re: SQL dudes, question
Post by: GilloD on May 28, 2009, 12:33:57 PM

I wrote this up to try and see if I could force a predictable result:

Code: [Select]

$queryall="SELECT * FROM new_batch WHERE po_num=$curr_po_num";
$result=mysql_query($queryall) or die(mysql_error());
while($row=mysql_fetch_array($result)){
$swap="INSERT INTO archive_batch(batch_num,customername,po_num,carrier,pro_num,isNew,isShipped)
   VALUES (80,TestingTest,123456,UPS,123456,0,1)";
 
echo "PO NUM SWAPPED: '$row[po_num]'";
$swapresult = @mysql_query($swap);
echo "RECORD SWAPPED";
}
No dice.  It's like it won't create the row (Except ONCE!). Thoughts?

Title: Re: SQL dudes, question
Post by: Bocsius on May 28, 2009, 03:00:14 PM

Isn't that statement only supposed to execute once?

Alternate question: If you're trying to execute the same insert statement twice, is there a primary key or another unique constraint on the table that would prevent the insert from succeeding?

Beyond that, I'm not a PHP guy, either. Could be something within that code that is not obvious to me.