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
– ArtisticPhoenix, Commented Jun 28, 2016 at 5: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
– Sgt AJ, Commented Jun 28, 2016 at 5:41
@ArtisiticPhoenix {(int)$base} will not work, because { must be followed by $ in order for PHP to place variable there. — Justinas
– Justinas, Commented Jun 28, 2016 at 5: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
– ArtisticPhoenix, Commented Jun 28, 2016 at 5: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
– Ctc, Commented Jun 28, 2016 at 6:10

Ctc · Accepted Answer · 2016-06-28 05:38:24Z

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)";

Drone · Accepted Answer · 2016-06-28 05:39:33Z

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-28 05:46:16Z

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.

Justinas · Accepted Answer · 2016-06-28 05:40:34Z

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.

Naisa purushotham · Accepted Answer · 2016-06-28 05:41:03Z

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";

Collectives™ on Stack Overflow

syntax error in converting varchar into int

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Related