CODEWITHSUNDEEP
phpmysqlphpmyadmin
willdanceforfun
willdanceforfun

Reputation: 11240

A basic select query returning nothing in page, but gets results in phpMyAdmin

I have a very basic query, raceid correlates to an id from another table. 'name' is a varchar column. Note: the following query is the output of echo $sql: 

SELECT * FROM runner WHERE raceid=738 AND name="Varsity"

My PHP is this this:

$sql = 'SELECT * FROM runner WHERE raceid=' . $raceid . ' AND name="' . $name . '"';

$stmt = $db->query($sql);

$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

I have not prepared the statement in the code above for debugging I've simplified the code.

When I run it in my page, I'm getting an echo of ' 0 rows selected'. So query works ok, but isn't matching. I paste the exact same query in phpMyAdmin and I get the match I'm wanting.

I have checked charsets - both the table and name column are utf8.

If I remove the 'name="Varsity"' from the query, the query generates results from the right table - so the connection is good. There is something awry with searching for this name but everything looks ok.

What other things could I check to debug in a situation like this?

I have also tried backticking name and even to be sure, I have changed the 'name' column to something less ambiguous:

$sql = "SELECT * FROM runner WHERE raceid=$raceid AND `racername`='$racerename' ";

Prepared statement also does not work

$stmt = $db->prepare("SELECT * FROM runner WHERE raceid=:raceid AND racername=:racername");
$stmt->execute(array(':raceid' => $raceid, ':racername' => $racername));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$row_count = $stmt->rowCount();

echo $row_count; // outputs 0

As I stated before, if I remove the name/racername from the query, the query works. It retrieves many results which match the raceid. And within those results is also the racername I'm looking for. ie. db connection absolutely works, its the right db. Additionally if I remove the raceid, and just search for the racername, that works as well and retrieves all the results. so what on earth? it's a very simple query matching 2 things.

Upvotes: 0

Views: 517

Answers (2)

Don't Panic
Don't Panic

Reputation: 41820

Without addressing the SQL injection issue,

$sql = "SELECT * FROM runner WHERE raceid=$raceid AND `name`='$name'";

should take care of it if it is a quoting issue, which I think it probably is. MySQL will (usually, depending on the mode) interpret the double quotes in your statement to mean that $name is an identifier rather than a value, and name should be backticked as it is a MySQL keyword.

But addressing the SQL injection issue would be better and is not difficult with a prepared statement:

$stmt = $db->prepare("SELECT * FROM runner WHERE raceid = ? AND `name` = ?");
$stmt->execute(array($raceid, $name));

Upvotes: 0

Alex
Alex

Reputation: 17289

I guess you use PDO and as I commented

http://php.net/manual/en/pdostatement.rowcount.php

PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.

So probably you can pass this way:

$sql = 'SELECT * FROM runner WHERE raceid=' . $raceid . ' AND name="' . $name . '"';
$stmt = $db->query($sql);
$rows = $stmt->fetchAll();
echo count($rows).' rows selected';

And here is more secure variant to avoid sql injections:

$sql = 'SELECT * FROM runner WHERE raceid = :raceid AND name = :name';
$stmt = $db->prepare($sql);
$stmt->bindParam(':raceid', $raceid, PDO::PARAM_INT);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll();
echo count($rows).' rows selected';

Upvotes: 1

Related Questions