PHP not showing MySQL results with variable in query

Question

I have been using the same code for years and all of a sudden I'm having problems that I cannot figure out. I am making a very simple query to MySQL in PHP using a variable in the statement. When I use the variable, it returns no results. When I manually type in the value of the variable instead, it works. I use this syntax all day long and never have had a problem. What on earth is wrong?

$name = "Fred";
$query = "SELECT * FROM database WHERE name='".$name."'";
$result = mysqli_query($connection, $query);
if (mysqli_num_rows($result) != 0) {
  echo "Found record.";
}

If I replace the $name variable with Fred, it finds the record. If I echo the query with the variable before it executes and place that exact statement into MySQL directly in phpMyAdmin, I also get the result. If I leave the statement as-is with the variable in place, I get no result. Please help.

You just should be using prepared statements. Your code is highly unsafe — Rotimi, Mar 28 '20 at 13:08
Please read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and use **prepared statememts** — nbk, Mar 28 '20 at 13:10
My question wasn't about SQL injections or prepared statements. I'm trying to find out why this isn't working. — DaltonRandall, Mar 28 '20 at 13:34
@stealthyninja it is being set; what I was saying is that if I replace the variable itself in the query with the contents of the variable, it works. When I use the variable instead, it does not. — DaltonRandall, Mar 28 '20 at 13:40
Check for errors; you're not doing that. Consult the duplicates. — Funk Forty Niner, Mar 28 '20 at 13:41

Fifth Horseman · Answer 1 · 2020-03-28T15:10:00.517

0

your query states SELECT * FROM database WHERE name='".$name."', this means that your table name is database, now i dont know how you actually created this table but database is a MYSQL reserved keyword change the name of your table to something else or just change your query to

$query = "SELECT * FROM `database` WHERE name='$name'";

assuming that your database connection is fine your code should now work

also worth noting, whenever acquiring data from a database use prepared statements instead of raw data as it makes you vulnerable to sql injection, in your case your code should be something like this

$name = "Fred";
$stmt = $dbconnection->prepare("SELECT * FROM table_name WHERE name=?")
$stmt->bind_param("s", $name);
$stmt->execute();

$result = $stmt->get_result();
if($result->num_rows != 0)
{
  echo "Found record.";
}

this is more secure

edited Mar 28 '20 at 15:10

answered Mar 28 '20 at 13:21

Fifth Horseman

128
12

Both `'".$var."'` and `'$var'` do the same thing. That isn't what the problem is with their code, it's definitely something else. They're not checking for errors at all. – Funk Forty Niner Mar 28 '20 at 13:41
look at the way he enclosed the variable $name and tell me if it;s correct, i tested my answer a couple of times and it works okay, double quotes or single quotes it should work the difference is just how you place the quotes, why the downvote – Fifth Horseman Mar 28 '20 at 13:45
@FunkFortyNiner It's not throwing an error, it is returning 0 rows as an answer. – DaltonRandall Mar 28 '20 at 13:47
@codefather Single quotes doesn't work either. – DaltonRandall Mar 28 '20 at 13:47
avoid this ".$name." or '.$name.' just use the quotes without the two dots inside – Fifth Horseman Mar 28 '20 at 13:49
@codefather Yes, I have tried that. '$name' does not work. – DaltonRandall Mar 28 '20 at 13:49
show us your connection file and another thing is your database name literaly database? – Fifth Horseman Mar 28 '20 at 13:51

Tolkar · Answer 2 · 2020-03-28T15:05:26.137

You shouldn't use mysqli excepted for old projects you can't upgrade, it's outdated and suffers from potential sql injection vulnerabilities.

Instead, I recommand you to learn PDO and prepared statements. Your request should look like this :

$name = 'Fred';
$sql = "SELECT * FROM my_user_table WHERE name = :name";

// You should have set your pdo instance in a script handling your database connexion and reusing it in any script making requests.
$result = $pdo->prepare($sql);
// Here you dynamically inject values in your request and tells pdo what type of data you are expecting
$result->bindValue(':name', $name, PDO::PARAM_STR);
$result->execute();

if( $result->rowCount()) {
  echo "{$result->rowCount()} result(s) found";
}
else {
  echo 'No result found';
}

Here's the official doc : https://www.php.net/manual/fr/book.pdo.php

This will also more than probably fix your problem.

That's great, but the question isn't "which do you like better, old mysqli or prepared statements?" I am attempting to get help with the code as presented. — DaltonRandall, Mar 28 '20 at 13:49

PHP not showing MySQL results with variable in query

2 Answers2