...

View Full Version : In desperate need for help on this insert query



Puppet Master
06-05-2011, 02:57 AM
I get an error message saying to check my syntax when I try to use an insert query., but I just can't find the error myself. Maybe I need a fresh set of eyes to find it for me. Anyone got any clues?



if($action == 'complete')
{
$date = date('D n/j/Y @ g:i A');
$total = sanitize($_GET['total']);
$change = sanitize($_GET['change']);
$method = sanitize($_GET['payment']);
$tendered = sanitize($_GET['tendered']);
$purchasedQuery = $db->query("SELECT * FROM `current_charge`");
$listIds = array();
$count = 0;
while($purchased = $purchasedQuery->fetch_assoc())
{
$listIds[] = $purchased['pid'];
$count++;
}
echo "<center><table cellpadding='10' width='45%' style=\"background-color:#FFFFFF; border:2px; border-style:solid;\">";
$psold = implode(",", $listIds);
//$count = $purchasedQuery->num_rows;
$shift = $db->query("SELECT * FROM `current_shift`");
$tax1Query = $db->query("SELECT SUM(tax1) AS tax1 FROM current_charge");
$tax1Array = $tax1Query->fetch_assoc();
$tax1 = $tax1Array['tax1'];
$tax2Query = $db->query("SELECT SUM(tax2) AS tax2 FROM current_charge");
$tax2Array = $tax2Query->fetch_assoc();
$tax2 = $tax2Array['tax2'];
$sale = $db->query("INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('$total', '$tax1', '$tax2', '$method', '$tendered', '$change', '$psold', '$count', '$shift')");
if($db->errno)
{
die($db->error);

}
echo "<center><table cellpadding='10' width='45%' style=\"background-color:#FFFFFF; border:2px; border-style:solid;\">";
echo "<tr><tr>charged";
echo "</table></center>";

}

Fumigator
06-05-2011, 04:03 AM
Number one, echo the query text. Like..


echo "INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('$total', '$tax1', '$tax2', '$method', '$tendered', '$change', '$psold', '$count', '$shift')";

Number two, show us the error message. It points right to the problem in your query.

Puppet Master
06-05-2011, 05:12 AM
Number one, echo the query text. Like..


echo "INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('$total', '$tax1', '$tax2', '$method', '$tendered', '$change', '$psold', '$count', '$shift')";

Number two, show us the error message. It points right to the problem in your query.

Here's the exact error message that I get back from MySQL not PHP:


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 'change, psold, icount, shift) VALUES ('3.77', '0', '0', 'cash', '5.00', '1.23', ' at line 1

bullant
06-05-2011, 05:20 AM
Why not do what Fumigator suggested as the first thing to do?

Have a look at the actual insert query being run. It's the fist thing I would do as well.

Puppet Master
06-05-2011, 05:37 AM
Sorry, I was going to do that but I got distracted.

I echoed the sql statement, and it turns out, there is a comma being printed after the last value $shift. Why is that? I checked the variables and sql and couldn't find anything?


INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('3.77', '0', '0', 'cash', '5.00', '1.23', '1,4,58', '3', '')

bullant
06-05-2011, 05:52 AM
Count the number of column names and number of values you have.

It appears you have an empty value for your last column.


, '')also, are you sure


'1,4,58', is a correct value for psold?

Puppet Master
06-05-2011, 05:56 AM
Count the number of column names and number of values you have.

It appears you have an empty value for your last column.


, '')


Yea I took a closer look at an earlier part in the script, and I realized that I was getting the $shift with a query to current_shift rather than the actual name of the table currentshift. And since mysql_errno will only bring the newest error message, it didn't show up in the error message.

Now I'm getting a new error message :P

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 'change, psold, icount, shift) VALUES ('3.77', '0', '0', 'cash', '5.00', '1.23', ' at line 1

bullant
06-05-2011, 06:09 AM
ok, then an easy way to fix php code that generates sql is to first write and debug the sql command in an sql window like phpMyAdmiin or SQLyog (http://www.webyog.com/en/). Then transfer the sql command to your php script. It's often easier to create/debug sql queries in an sql GUI.

Puppet Master
06-05-2011, 06:25 AM
also, are you sure


'1,4,58', is a correct value for psold?

Yea, I add some IDs to an array and use php implode() to convert them into one string.

I double checked the SQL statement and found out some of the variables were misaligned. Now the only error is on PHPs end, Mysql is expecting a string to input for one of the field. But PHP is supplying something else.

I just gotta go through the script line by line to see where its going wrong.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum