[RESOLVED] sql query quoting issue

dkrysak

Ok, I have a query:

//$query = "SELECT staff, COUNT(email) AS Total FROM `ccStaffTracker` WHERE `added` BETWEEN 20081001000000 AND 20081031235959 GROUP BY staff";

The above works fine. Now I want to sub in the date ranges with variables - so something like:

$query = "SELECT staff , COUNT( email ) AS Total FROM `ccStaffTracker` WHERE `added` BETWEEN ".$dateBg." and ".$dateEd." GROUP BY staff ";

Now for some reason the above code gives me the darn 1064 error.

What am i doing wrong?

laserlight

EDIT:
Whoops, I remembered wrongly. Apparently, you are using a reserved word in an inappropriate context. You need to get the error message, but that depends on your database extension.

dkrysak

laserlight;10894907 wrote:
EDIT:
Whoops, I remembered wrongly. Apparently, you are using a reserved word in an inappropriate context. You need to get the error message, but that depends on your database extension.

Sorry, I don't quite follow....

I am using mysqli with mysql 5.0.27 on php 5.1.6.

AS for the reserved word, I suspect it is the "and" between the date ranges?

laserlight

dkrysak wrote:
I am using mysqli with mysql 5.0.27 on php 5.1.6.

Okay, print $conn->error to find out what is the error message, where $conn is your connection object.

dkrysak

laserlight;10894912 wrote:
Okay, print $conn->error to find out what is the error message, where $conn is your connection object.

Ok, here is what we are looking at:

Error #: 1064
Error: 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 'and GROUP BY staff' at line 1'

laserlight

Ah, that implies that the $dateEd variable is empty.

dkrysak

laserlight;10894916 wrote:
Ah, that implies that the $dateEd variable is empty.

That is odd, because I have an EXACT copy of this method that works perfectly. The only different is the SQL query itself. In that one it is:

$query = "SELECT * FROM `ccStaffTracker` WHERE `added` BETWEEN " . $dateBg . " and " . $dateEd;

So I would assume that the $dateEd variable is actually being populated.

Ok, however - you are correct. It was a typo on my methods parameter variable! ugh. I should have copied the entire thing (which i thought I had!).

Once again.. thanks!

Weedpacket

dkrysak wrote:
Ok, however - you are correct. It was a typo on my methods parameter variable!

If you had PHP's error reporting set to E_ALL you would have got a notice about an uninitialised variable as soon as you tried to use that parameter.

Roger_Ramjet

And if you were using PDO Prepared Statements you would not have to worry about string quoting, or sql injection protection. You would also get a PDO Eception if an input parameter was missing.

laserlight

Roger Ramjet wrote:
And if you were using PDO Prepared Statements you would not have to worry about string quoting, or sql injection protection.

Actually, MySQLi has support for prepared statements as well, so it is a matter of using the feature.