phpmyadmin Question posted in
The official documentation can be found here.

PHP not showing MySQL results with variable in query – Phpmyadmin

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.

Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search