CODEWITHSUNDEEP
phpmysqlarrays
Dylan Cross
Dylan Cross

Reputation: 5986

php MySQL query not returning anything

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.

Upvotes: 0

Views: 3999

Answers (5)

Kermit
Kermit

Reputation: 34055

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];
}

Upvotes: 0

Richard
Richard

Reputation: 1052

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.

Upvotes: 1

codingbiz
codingbiz

Reputation: 26386

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

Upvotes: 0

VolkerK
VolkerK

Reputation: 96159

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'];
}

Upvotes: 0

Paranoid Android
Paranoid Android

Reputation: 5037

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

Upvotes: 0

Related Questions