php MySQL query not returning anything

Question

I'm not sure exactly how to explain what the query does, however the problem isn't entirely with how it's set up, because it does work, in another instance, when I use it as an array, however it's not working when I use it with mysql_fetch_assoc(), so here is what my original query is(not the one im having trouble with):

SELECT * FROM 
(SELECT * FROM comments 
          WHERE postID='$id' AND state='0' 
          ORDER BY id DESC LIMIT 3
) t ORDER BY id ASC

what this does is selects the last 3 comments on a post, then orders them in another way (so they show up in the correct order, old to new) Now this is the query for echoing out the array of comments directly. But now what I want to do, is to just get the first id out of the 3 comments.

here's what I have tried to do (and by the way, this query DOES work, when i replace my previous query to echo out the results in an array, but i need to get just the id for use, i don't want an array):

$previousIDq = mysql_fetch_assoc(mysql_query("
                                   SELECT * FROM 
                                  (SELECT * FROM comments 
                                   WHERE postID='$id' AND state='0' 
                                   ORDER BY id DESC LIMIT 3
                                  ) t ORDER BY id ASC LIMIT 1"));

 $previousID = $previousIDq['id']; //this doesn't return the id as I want it to.

`var_dump($previousIDq)` will show you what's in the row you're fetching. General tip: don't nest mysql calls like that. If the query fails for whatever reason, you'll get "expected X, received boolean" warnings up the wazoo. — Marc B, Oct 29 '12 at 14:54
Never wrap `mysql_query()` in a `mysql_fetch_*()` call. Always return it to a variable to test for success. Further, it is not advisable to `SELECT *` in nested queries as you have done. Be explicit about the column names. — Michael Berkowski, Oct 29 '12 at 14:54

score 1 · Accepted Answer · answered Oct 29 '12 at 15:04

1

Your problem may be that there are no matching rows.

Also, I think you could also improve your query to this:

SELECT * FROM comments WHERE postID='$id' AND state='0' ORDER BY id DESC LIMIT 2,1

But as others say, use PDO or MySQLi, and with prepared statements. And don't SELECT * ever.

answered Oct 29 '12 at 15:04

Richard

1,024
1
7
15

That query works perfectly, I hadn't thought of doing it like that. I am aware that MYSQL_* is deprecated, and I intend to move it over to MYSQLI at some point. – Dylan Cross Oct 29 '12 at 15:12
As a rule of thumb, there's always a more elegant way of doing something in SQL. It really can be beautiful. If you're just getting to grips with database interaction in PHP, mysql_* is fine, but the inbuilt security features in MySQLi or PDO will save you so much hassle down the line. What's more important is that you should always specify which cols you want. If the table structure changes, the SQL call will give an error if the col you want is removed/changed, as opposed to potentially getting a NULL like you were! – Richard Oct 29 '12 at 15:18
Well, is that why you are suppose to specify the columns? I never understood the big deal with that, I know/have been told that it is also faster if you aren't retrieving each column. And as far to my NULL error, it turned out to be a spelling mistake in one of the variables.. sadly. – Dylan Cross Oct 29 '12 at 19:24
This is a pretty good summary of why you shouldn't (in production): http://stackoverflow.com/a/3180435/1458800 – Richard Oct 29 '12 at 21:13

score 0 · Answer 2 · answered Oct 29 '12 at 14:56

0

try a var_dump($previousID) to see what you get

It is probably giving you back an object, and you need to get your id from that object

answered Oct 29 '12 at 14:56

Paranoid Android

4,672
11
54
73

try mysqli_fetch_assoc(), with the "i" The one you are using is deprecated BTW – Paranoid Android Oct 29 '12 at 14:59
Still NULL, and the problem with that is that my entire website has previously been built with regular mysql. – Dylan Cross Oct 29 '12 at 15:00

Kermit · Answer 3 · 2012-10-29T15:05:44.730

Please stop using mysql_ functions to write new code, it is being deprecated. Use mysqli_ or PDO functions (mysqli below).

Bind your parameters to prevent SQL injection
Always use a column list (don't use SELECT *)
If you're returning > 1 row, use a while loop

`mysqli_` solution

$link = mysqli_connect("localhost", "user_name", "password", "stock");

if (mysqli_connect_error()) {
die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

$stmt = mysqli_prepare($link, "SELECT t.id FROM 
    (SELECT id FROM comments 
    WHERE postID = ? AND state = 0
    ORDER BY id DESC 
    LIMIT 3) t 
ORDER BY id ASC");

mysqli_bind_param($stmt, 's', $id) or die(mysqli_error($dbh));

$result = mysqli_stmt_execute($stmt) or die(mysqli_error($link));

while($row = mysqli_fetch_assoc($result)) {
    echo $row[id];
}

mysqli_close($link);

`mysql_` solution

$stmt = "SELECT t.id FROM 
        (SELECT id FROM comments 
        WHERE postID = $id AND state = 0
        ORDER BY id DESC 
        LIMIT 3) t 
    ORDER BY id ASC";
$result = mysql_query($stmt) or die(mysql_error());
$row = mysql_fetch_assoc($result);

while($row = mysql_fetch_assoc($result)) {
    echo $row[id];
}

score 0 · Answer 4 · answered Oct 29 '12 at 15:00

You script is too condensened for error handling, let alone debugging

$mysql = mysql_connect(...

$query = "
    SELECT * FROM 
    (SELECT * FROM comments 
    WHERE postID='$id' AND state='0' 
    ORDER BY id DESC LIMIT 3
    ) t ORDER BY id ASC LIMIT 1
";
$result = mysql_query($query, $mysql);
if ( !$result ) { // query failed
  die('<pre>'.htmlspecialchars(mysql_error($mysql)).'</pre><pre>'.htmlspecialchars($query).'</pre>');
}

$previousIDq = mysql_fetch_assoc($result);
if ( !$previousIDq ) {
    die('empty result set');
}
else {
    $previousID = $previousIDq['id'];
}

and the mandatory side note: http://docs.php.net/function.mysql-connect says: `Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.` — VolkerK, Oct 29 '12 at 15:05

score 0 · Answer 5 · answered Oct 29 '12 at 15:01

You need to separate your code to be able to debug

$query = "SELECT * FROM 
               (SELECT * FROM comments 
                     WHERE postID='$id' AND state='0' 
                                   ORDER BY id DESC LIMIT 3
                                  ) t ORDER BY id ASC LIMIT 1";

$result = mysql_query($query);

echo mysql_error(); //what eror comes out here

while($previousIDq  = mysql_fetch_assoc($result))
{
     print ($previousIDq['id']);
}

NOTE: mysql_* is depreciated, upgrade to mysqli or PDO

php MySQL query not returning anything

5 Answers5

mysqli_ solution

mysql_ solution

`mysqli_` solution

`mysql_` solution