Syntax error on submitting order.

Hi,

I get syntax error after submitting order button on "confirm.php" page saying;

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stay sweep rigger frame, with European style 12mm block,2,2,'Rig21A',0,1)' at line 1"
("stay sweep rigger frame, with European style 12mm block"- part of a product name, "Rig21A" product ID (not unique one))

MySQL version:5.1.37
PHP version: 5.2.11

I assume this is rather a problem with coding on confirm.php, as I tried to fix function of confim button myself since it wasn't working properly.

I can't tell what is wrong with it.

Thank you very much.
Rika

I've noticed two things.

1. "//WA eCart Include" was mentioned twice, so I deleted one.
2. I added redirect page to --> $WA_redirectURL = "";

Now I don't get syntax error code, but contents of order are not sent to data base.

Just in case I didn't explain enough for above additional change;

1. The entire section of "//WA eCart Include" was doubled, so I deleted one of them.

As I mentioned, redirect URL is working, confirmation email is sent, but the email doesn't have any contents except it says "0".

Any help will be appreciated.
Rika

Removing the extra include is fine but you should not set the redirect like you have done. Rather than stop the error from occurring it seems that your redirect is just getting the user to the next page before the code that causes the error runs. You should undo any changes that you made to the redirect.

The error you are getting is a MySQL error and is being reported directly from your db. This tells me that there is a problem with the values you are using on this part of the checkout. What type of checkout did you select and have you made any changes to the page? What about the data types of the information that is being stored? I have seen errors like this when the information is not of the rite type, missing, or referencing non existing values.

To determine what the problem may be you should print out the query so that it can be examined further. I have added some debug code that should print out the query so it can be looked at in more detail. I added a die() statement just after line 292 in the page that you posted. Please run a test with this page and let us know what the result is.


Also, you should update your Universal Email code on the page, currently in the UE code that is on the top of the page you have these lines:

$BurstSize = undefined; 
$BurstTime = undefined; 
$WaitTime = undefined;

You should update these lines so that they are like this instead:

$BurstSize = 200;
$BurstTime = 1; 
$WaitTime = 1;

Thank you.
I realize that just setting the redirect is not solving a problem at all.
I deleted the URL for redirecting.

The result of the test after I swtched the PHP code that you replied is;
"insert into orderdetails (DetailID,DetailOrderID,DetailProductID,DetailName,DetailPrice,DetailQuantity) values (Standard rudder,102,102,'Fin1-S',0,1)"

But if I update the UE code on "confirm.php" as you adviced, the page appears blank.
So at the moment it is kept as it was before (as "undefined").

For the checkout, I chose "no-payment-gateway" (is this what you were asking about?).
I haven't made any change to these check-out pages generated by e-cart except this confirm.php page.
The changes I've made are layout, ecart set session value (changed ## part: $_SESSION["eCartCheckoutForm_##"] = "".((isset($_POST["##"]))?$_POST["##"]:"") ."";)
, "submit order" button ( it stopped working so I replaced it with checkout button).

I realize that I completely messed this up...
Thank you for your patience.

Thanks for getting back with the details, this was the info that I was looking for. Based on what you have in your query and the way that the Store Order Details server behavior is setup it seems like you may just have the associations to the fields in your order details table misconfigured.

For example in the printed out query that you have the first column mentioned in the DetailID, but the first value you are passing into this column is 'Standard rudder'. This is not an appropriate value for this column. You should not be specifying any value for the id column, it should be automatically create for you when the record is inserted.

You should edit your store order details server behavior and re-associate the columns with the correct value. You should not specify anything for the DetailID, for the other columns you should set the values like this:

DetailOrderID $_SESSION["Swift_OrderID"]
DetailProductID product id column in the cart
DetailName the name column from the cart
DetailPrice the price column from the cart
DetailQuantity the quantity column from the cart

As for the Universal Email code that you have on the page there was a problem with the code that I posted, it should be updated to be like this instead:

$BurstSize = 200;

$BurstTime = 1; 

$WaitTime = 1;

Eric,

Thank you, I understand.
But I came up with one more problem.

I tried to re-configure "store order details server behavior" under server behavior wiondow, but somehow I can not see it.
I don't remember if I did anything to it, but it is not there. There is a coding for it under "// WA eCart Store Cart Details in Db", but not in the server behaivor window. I'm not confident to edit the code manually.

Is there anyway to get the "store order details server behavior" under server behavior window, or should I re-insert this server behavior from server behavior panel's data manipulation -->store cart details in database??

Since the one on the page is not working for you now I think you would be fine to just replace it with a new one. Always make sure to backup any pages before you edit or alter them.

Hello Eric,

Sorry it's been while since I got your reply.
So I tried to re creat the Store Cart Detail in Database.

I wasn't so sure about one point though.
Where you choose value for each column title (under the value field by clicking lightening button), I kept DetailsID blank as you suggested. For "DetailOderID", I chose [session ID] under "swift" which is the cart, and this got me a code; <?php echo session_id(); ?>. Is this correct?

Under the "Session", all I see there is "paypal_ECO_~, or "eCartCheckoutForm_~" and these didn't sound right for me, so I didn't touch it.

BUT, there is one problem!
As I mentioned in #5, when I change the UE code into below (as you suggested), the confirm.php page doesn't function at all. It appears as a blank page.

//WA Universal Email object="mail"
set_time_limit(0);
$EmailRef = "waue_confirm_1";
$BurstSize*=*200;*
$BurstTime*=*1;*
$WaitTime*=*1;*
$GoToPage = "";
$RecipArray = array();
$StartBurst = time();
$LoopCount = 0;
$TotalEmails = 0;
$RecipIndex = 0;

What do you think is the reason??

Just in case I pasted the newly inserted Store Cart Details code as well.


<?php
// WA eCart Store Cart Details in Db
if ($_SERVER["REQUEST_METHOD"] == "POST") // Trigger
{
$WA_connection = $localhost;
$WA_table = "OrderDetail";
$WA_redirectURL = "";
$WA_indexField = "DetailsOrderID";
$WA_fieldNamesStr = "DetailsOrderID|DetailsItemID|DetailsItemName|DetailsItemDsc|DetailsQuantity|DetailPrice";
$WA_fieldValuesStr = WA_eCDB_ConvertNumber("".session_id() ."") . "|" . WA_eCDB_ConvertNumber("".$Swift->DisplayInfo("ID") ."") . "|" . "".$Swift->DisplayInfo("Name") ."" . "|" . "".$Swift->DisplayInfo("Description") ."" . "|" . WA_eCDB_ConvertNumber("".$Swift->DisplayInfo("Quantity") ."") . "|" . WA_eCDB_ConvertNumber("".$Swift->DisplayInfo("Price") ."");
$WA_columnTypesStr = "none,none,NULL|none,none,NULL|',none,''|',none,''|none,none,NULL|none,none,NULL";
$WA_sql = "";
mysql_select_db($database_localhost, $WA_connection);
$indexFieldIndex = -1;
$WA_fieldValues = explode("|", $WA_fieldValuesStr);
if (!session_id()) session_start();
if ($WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos('?', $WA_redirectURL) === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_columns = explode("|", $WA_columnTypesStr);
for ($i = 0; $i < sizeof($WA_fieldNames); $i++)
{
if ($WA_indexField == $WA_fieldNames[$i])
{
$indexFieldIndex = $i;
break;
}
}
$deleteFieldValue = $WA_fieldValues[$indexFieldIndex];
if (isset($deleteFieldValue) && $deleteFieldValue != "")
{
$deleteParamsObj = WA_generateInsertParams(array($WA_indexField), array($WA_columns[$indexFieldIndex]), array($deleteFieldValue), -1);
$WA_sql = "Delete from " . $WA_table . " where " . $WA_indexField . " = " . $deleteParamsObj->WA_dbValues;
$WA_editCmd = mysql_query($WA_sql, $WA_connection) or die(mysql_error());
$WA_okToDelete = false;
}
$Swift->MoveFirst();
while (!$Swift->EOF())
{
$WA_fieldValuesStr = WA_eCDB_ConvertNumber("".session_id() ."") . "|" . WA_eCDB_ConvertNumber("".$Swift->DisplayInfo("ID") ."") . "|" . "".$Swift->DisplayInfo("Name") ."" . "|" . "".$Swift->DisplayInfo("Description") ."" . "|" . WA_eCDB_ConvertNumber("".$Swift->DisplayInfo("Quantity") ."") . "|" . WA_eCDB_ConvertNumber("".$Swift->DisplayInfo("Price") ."");
$WA_fieldValues = explode("|", $WA_fieldValuesStr);
$insertParamsObj = WA_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WA_sql = "insert into ".$WA_table." (".$insertParamsObj->WA_tableValues.") values (".$insertParamsObj->WA_dbValues.")";
$WA_editCmd = mysql_query($WA_sql, $WA_connection) or die(mysql_error());
$Swift->MoveNext();
}
if ($WA_redirectURL != "") {
header("Location: ".$WA_redirectURL); exit;
}
$Swift->MoveFirst();
}
?>

The reason the page is blank is because of syntax errors. The code that I originally posted for you in #4 has been updated, the same updated code is listed in post #6. Make sure to update the UE code with this newer updated code to get the page working again.