0

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.
Improve this question
4
  • 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. Commented Oct 29, 2012 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. Commented Oct 29, 2012 at 14:54
  • Use PDO!!!!!!!!! Please. Stop writing nasty code. Commented Oct 29, 2012 at 14:55
  • Marc B, when I var_dump it, it just says NULL. Commented Oct 29, 2012 at 14:55

5 Answers 5

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
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!
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.
This is a pretty good summary of why you shouldn't (in production): stackoverflow.com/a/3180435/1458800
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

Improve this answer

2 Comments

try mysqli_fetch_assoc(), with the "i" The one you are using is deprecated BTW
Still NULL, and the problem with that is that my entire website has previously been built with regular mysql.
0

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

1 Comment

and the mandatory side note: docs.php.net/function.mysql-connect says: Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.
0

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

Improve this answer

Comments

0

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];
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.