syntax error when using POST values in query

[oddshoes]

I am trying to build a query to check stock levels via a web page. I post the values through, print them just to check , then try to use them to build the query. The $_POST array is ok but the query fails and I get a syntax error

<?php
print_r($_POST);
      // 3. perfprm database query
	$result = mysql_query("SELECT SUM({$_POST[1]}) FROM `orders` WHERE `takenAt`= '{$_POST[0]}' and `collectDate` BETWEEN '{$_POST[2]}' AND '{$_POST[3]}' ", $connection );
	if (!$result)
{
	die("sorry can't do that one: " . mysql_error());
}
	// 4. use returned data      
print_r($result)

?>

I am still rather new to this and would greatly appreciate a hint or two

cheers

[Fou-Lu]

What's the error? And what is the value of the print_r($_POST) showing?

[oddshoes]

This are the array print and errors

Array ( [takenAt] => Askew Road [editItem] => qty13 [date1] => 2012-11-08 [date2] => 2012-12-24 [submitEdit] => )
sorry can't do that one: 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 ') FROM `orders` WHERE `takenAt`= '' and `collectDate` BETWEEN '' AND ''' at line 1

thanks

[Fou-Lu]

All empty; the $_POST is indexed associatively. You need to use the names you see there as the access to the $_POST array, not the numerical offset of where it would be.
Also, you'll want to look into SQL injection to prevent overwriting the SQL string. Binding with a prepared statement would be better, but you cannot use it in a structural element such as a property or a table name.

[oddshoes]

thanks;
1. I understand the first bit and have substituted the actual key names. I was trying to be clever - always a mistake! I had not fully understood the difference between the two types of array. The query now runs, but I do not get the expected result. I get "Resource id #8" instead of the expected integer, (4) in this case.
2. I am vaguely aware of SQL injection, bit do not think (??) it matters in this case as the site is private with only two users.
3. This bit I do not understand, but will do some research to hopefully educate myself.

[Fou-Lu]

$result of a resource# is good. That means you have a resultset back; this doesn't tell you if you have any records or anything about it thourhg. You now need to fetch it, I'd suggest since you will only have one result that mysql_result be the best option

PHP Code:

$sum = mysql_result($result, 0); 


$sum will be the number you want.
You should always put the effort into preventing sql injection regardless of the use. Using the mysql library its a simple matter of:

1. Check if magic_quotes_gpc directive is enabled. If it is, execute stripslashes on any input data
2. Cast the datatypes appropriate; if string (including datetime datatypes), filter through mysql_real_escape_string

If you use prepared statements in PDO or MySQLi, then only the first step needs to occur. The statement is prepared outside of the data provided, so its not possible to inject SQL commands into data.