syntax error in converting varchar into int

Question

I am getting this error

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 '(int) , (int)' at line 2

I followed up this answer on SO

I am getting base and limit as string , i want to convert them into int. This is my code

$category = $_POST['category'];
$base= $_POST['base'];
$limit= $_POST['limit'];

$sql = "SELECT id, name, url
FROM OBJECTS where CATEGORY='$category' limit (int)$base , (int)$limit";

PHP wont interpolate that in a quoted string, you should do this `{(int)$base}` with brackets, or `".(int)$base."` — ArtisticPhoenix, Jun 28 '16 at 05:38
As others have said, you're trying to use the PHP method of casting in a mySQL statement. mySQL doesn't understand that. Either cast it first in PHP and then put it into the SQL statement or use mySQL's cast operators. — Sgt AJ, Jun 28 '16 at 05:41
@ArtisiticPhoenix `{(int)$base}` will not work, because `{` must be followed by `$` in order for PHP to place variable there. — Justinas, Jun 28 '16 at 05:42
@Justinas - you may be right, I'm on the right track though. I'll update my answer. By the way thanks, I do recall that now that you mention it. — ArtisticPhoenix, Jun 28 '16 at 05:45
I like doing my codes in the SQL statement as it allows me to be modular with the POSTs, all the other answers are good takes too. — Ctc, Jun 28 '16 at 06:10

score 3 · Answer 1 · answered Jun 28 '16 at 05:38

You will need to cast it. What you are doing now is PHP casting. you will have to cast it in the SQL statement.

$category = $_POST['category'];
$base= $_POST['base'];
$limit= $_POST['limit'];

$sql = "SELECT id, name, url
FROM OBJECTS where CATEGORY='$category' limit CAST($base AS UNSIGNED) , CAST($limit AS UNSIGNED)";

score 3 · Answer 2 · answered Jun 28 '16 at 05:39

Use casting like this,

$category = $_POST['category'];
$base= intval($_POST['base']);
$limit= intval($_POST['limit']);
//OR
/*
 $base= (int)($_POST['base'];
 $limit= (int)($_POST['limit'];
*/

$sql = "SELECT `id`, `name`, `url`
FROM `OBJECTS` where CATEGORY='$category' limit $base ,$limit";

ArtisticPhoenix · Accepted Answer · 2016-06-28T05:46:16.827

PHP variable interpolation will not fill that in when in a string.

You need to do it like this

 $sql = "SELECT id, name, url FROM OBJECTS where CATEGORY='$category' limit ".(int)$base." , ".(int)$limit;

Interpolation only works on variables ( or things that start with the $ ) such as accessing a class ( which is in a variable ) so things like this

   echo "self::$STATIC";

will look for $STATIC as a variable. Essentially you are putting this if $v = 1

  "(int)$v" becomes "(int)1"

And subsequently, MySql looks at (int)1 as a string and blows up.

score 2 · Answer 4 · answered Jun 28 '16 at 05:40

MySql does not have type cast in PHP manner. So (int)$base is wrong syntax.

First type cast, than place to query:

$base = intval($base);
$limit = intval($limit);

$sql = "SELECT id, name, url FROM OBJECTS where CATEGORY = '{$category}' limit {$base}, {$limit}";

Consider using prepared statements instead of placing variables directly to query and not escaping them.

score 1 · Answer 5 · answered Jun 28 '16 at 05:41

use intval in php...ref.link http://php.net/manual/en/function.intval.php

$category = $_POST['category'];
$base= intval($_POST['base']);
$limit= intval($_POST['limit']);

$sql = "SELECT id, name, url
FROM OBJECTS where CATEGORY='$category' limit $base , $limit";

syntax error in converting varchar into int

5 Answers5