MySQL Select...Where Clause Returns Syntax Error

Question

I've been racking my brain with this problem, and after searching Google and Stack Overflow a hundred times each I've decided to just ask about it outright.

I'm trying to make a page that uses PHP and MySQL to search a database as the user types in a keyword. I've used several tutorials on the subject, and they all appeared upfront and simple, but have not given any prediction for the trouble I've been having.

When I use "SELECT * FROM charlist", it returns all rows, as it should. But when I use "SELECT * FROM charlist WHERE Character ='" . $character . "'", I get the following 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 '= 'X'' at line 1

X is whatever the user typed in, and blank if nothing is typed in.

What am I doing wrong?

Here is the full code:

<?php
$con = mysqli_connect("xxxx", "xxxxxxxx", "xxxxxxx", "xxxxxxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysqli_error($con));
  }
$character = $_POST[character];
mysqli_select_db($con, "xxxxxxxx");

$sql = "SELECT * FROM charlist WHERE Character = '" . $character . "'";

$result = mysqli_query($con,$sql);
if (!$result) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}

echo "<table border='1'>
<tr>
<th>Character</th>
<th>Player</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
    echo '<tr style="border-color:#';
    echo $row[Color];
    echo ';">';
    echo '<td style="border-style:solid;border-width:3px;"><a href="';
    echo $row[url];
    echo '">';
    echo $row[Character];
    echo '</a></td>';
    echo '<td>';
    echo $row[Player];
    echo'</td>';
    echo '</tr>';
}
echo '</table>';

mysqli_close($con);
?>

If you now want to use $character = $_POST['character']; then you can remove this line and write extract($_POST); — Nanhe Kumar
– Nanhe Kumar, Commented Aug 17, 2013 at 1:50

Satya · Accepted Answer · 2013-08-17 01:41:09Z

1

change this line

$character = $_POST[character];

to

$character = $_POST['character'];

and you should be throught

answered Aug 17, 2013 at 1:41

Satya

8,9016 gold badges41 silver badges57 bronze badges

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

1 Comment

Yaniv Over a year ago

This did not help, unfortunately. Would it still be an error with using "[character]" if I can echo the POSTed information without any problems?

Matt · Accepted Answer · 2013-08-17 01:39:32Z

0

Try escaping $character using:

$sql = "SELECT * FROM charlist WHERE Character = '" . mysqli_real_escape_string($character) . "'";

In case there are quotes in the character name breaking the query.

answered Aug 17, 2013 at 1:39

Matt

814 bronze badges

Comments

Manohar Bomma · Accepted Answer · 2013-08-17 01:40:15Z

0

use the query as

"SELECT * FROM charlist WHERE Character ='$character'"

answered Aug 17, 2013 at 1:40

Manohar Bomma

3211 gold badge3 silver badges17 bronze badges

2 Comments

Amal Over a year ago

Please explain how's that different from what the OP currently uses?

Manohar Bomma Over a year ago

php will replace the words having $ prefix with variable inside double quotes here character is to be match we need single code. so inside single quotes value of the variable

anon · Accepted Answer · 2013-08-17 01:46:39Z

0

Turning on PHP error reporting would have helped:

$character = $_POST[character];
//       -----------^--------^

should be:

$character = $_POST['character'];

Also, inserting a variable directly into your query is a very bad practice and makes your site vulnerable to SQL injection. Always treat user input with care!

$sql = mysqli_real_escape_string($con, $sql);

Hope this helps!

answered Aug 17, 2013 at 1:46

anon

1 Comment

Yaniv Over a year ago

The change did not prevent the error. Also, even though I have it written as "[character]", it still grabs the POSTed information just fine. I can echo it without a problem. The trouble is trying to get sql to select it.

Collectives™ on Stack Overflow

MySQL Select...Where Clause Returns Syntax Error

4 Answers 4

1 Comment

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related