MySQL select with variable not working

Question

$select = "SELECT name FROM table_name WHERE location ='".$loc."' ";
$findname = mysql_query($select) or die(mysql_error());

I keep getting this error! I have tried everything!!!

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 'WHERE location ='Florida'' at line 1

$loc is determined by the following:

<input type="text" name="loc"> in the HTML
$loc = $_POST['loc']; in the PHP

Seems to me like something is wrong with your $loc, can you show the example of the variable? Seems to me the variable ' is escaping and makes your statement to be SELECT name FROM table_name WHERE ='' (2x single quoted) Florida' — Sky
– Sky, Commented Mar 18, 2014 at 7:30
The variable is just from a post field where you enter Florida. Simply $location = $_POST['location']; @Sky — user3430837
– user3430837, Commented Mar 18, 2014 at 7:51

Kodr.F · Accepted Answer · 2014-03-18 07:32:23Z

1

Try this , use mysql_escape_string or mysql_real_escape_string mysql safe string functions

$select = "SELECT `name` FROM `table_name` WHERE `location` ='".mysql_escape_string($loc)."' ";
$findname = mysql_query($select) or die(mysql_error());

answered Mar 18, 2014 at 7:32

Kodr.F

14.5k13 gold badges55 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kodr.F Over a year ago

your problem is in table_name make sure you have added correct table name

SajithNair · Accepted Answer · 2014-03-18 07:32:59Z

0

$select = "SELECT name FROM table_name WHERE location ='".mysql_real_escape_string($loc)."' ";
$findname = mysql_query($select) or die(mysql_error());

answered Mar 18, 2014 at 7:32

SajithNair

3,8771 gold badge20 silver badges23 bronze badges

1 Comment

user3430837 Over a year ago

This gives me: ...right syntax to use near 'WHERE location ='Florida'' … same error @SajithNair

Krishna M · Accepted Answer · 2014-03-18 07:34:38Z

0

Just check for any single quotes or double quotes in the location variable. That might be a problem.

Use str_replace(find,replace,string) to replace single and double quotes in the string.

Example, when you can contain a double quote in the $loc variable.

$select = "SELECT name FROM table_name WHERE location ='Flo"rida' ";

The query will end at Flo.

answered Mar 18, 2014 at 7:34

Krishna M

1,2152 gold badges18 silver badges34 bronze badges

2 Comments

user3430837 Over a year ago

There is no single quote or double quote in my input string for location.

Krishna M Over a year ago

try using "SELECT table_name.name FROM table_name WHERE table_name.location ='".$loc."' ";

codelover · Accepted Answer · 2014-03-18 08:33:23Z

0

use mysql_real_escape_string after WHERE location =

edited Mar 18, 2014 at 8:33

answered Mar 18, 2014 at 8:21

codelover

3172 silver badges11 bronze badges

Comments

user3430837 · Accepted Answer · 2014-03-18 08:34:48Z

0

I fixed it. I was using a variable for the table name and was referencing a null value. Thanks for the help!

answered Mar 18, 2014 at 8:34

user3430837

655 bronze badges

Collectives™ on Stack Overflow

MySQL select with variable not working

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related