mySql query does not find the row

Question

I'm new to both PHP & mySQL, but I suspect some apostrophe related bug here (maybe).

For some reason the first query always seems to return null because the

echo "result: $result\n";

never prints any data. At the first call that is expected, but at the second call the player has been added to the db. My database works in the sense that I can see that rows with correct ids are added to the database (via phpMyAdmin).

Can you help me spot the error?

<?php
require_once('Db.php');

$db = new Db();

// Quote and escape form submitted values
$id = $db->quote($_POST['id']);
$score = $db->quote($_POST['score']);

$result = $db->query("SELECT `id` FROM `xxxxxx`.`Player` WHERE `id` = `$id`");

echo "result: $result\n"; // Never prints any data

if($result->num_rows == 0) {
  // Row not found. Create it!
  $result = $db->query("INSERT INTO `xxxxxx`.`Player` (`id`,`score`) VALUES (" . $id . "," . 0 . ")");
}

?>

Because `$result` must be an associative array or object and can not print using `echo`. Try with `var_dump($result);` or 'print_r($result);' — prava, Mar 13 '15 at 09:44
See http://stackoverflow.com/tags/php/info and search for "I have a typical "does not work" problem". — Salman A, Mar 13 '15 at 09:54

n-dru · Accepted Answer · 2015-03-13T09:54:28.707

1

First, drop those backticks from id in WHERE clause, otherwise it will take the field name from id column instead of 'id'. Then you need to fetch data from $result:

$result = $db->query("SELECT id FROM `xxxxxx`.`Player` WHERE id = '$id'");
$row = $result->fetch_array();
echo $row['id'];

Or if there are more rows than one:

while($row = $result->fetch_array())
{
    echo $row['id'];
}

edited Mar 13 '15 at 09:54

answered Mar 13 '15 at 09:44

n-dru

9,285
2
29
42

you can consider @ajaykumartak also in your answer – user1844933 Mar 13 '15 at 09:53
@user1844933 oh, I didn't notice that that $id had backticks as well, thanks for pointing out – n-dru Mar 13 '15 at 09:57
@SalmanA yes, I noticed, thanks for remark, I was in a hurry - I see I got punished already :) – n-dru Mar 13 '15 at 09:59
Excellent guys! In case someone else stumbles on the same kind of problem this page explains the use of back-ticks: http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Fredrik Johansson Mar 13 '15 at 14:41

score 0 · Answer 2 · answered Mar 13 '15 at 09:51

You are using backticks in your query for $id. Remove them and try again. Your query should be

$result = $db->query("SELECT `id` FROM `xxxxxx`.`Player` WHERE `id` = $id");

OR

$result = $db->query("SELECT `id` FROM `xxxxxx`.`Player` WHERE `id` = ".$id."");

mySql query does not find the row

2 Answers2