Return last row's data

[TomMW]

I'm stuck on what I believe to be a simple thing. What I want to do is return the DatePosted field from the last row in the table blog.

This is what I have, which does give me the total number of posts or comments in the table, but it doesn't display anything for the Last Post.

 

<?php

$con = mysql_connect($database_host,$username,$password);

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db($database_name, $con);

$result = mysql_query("SELECT * FROM blog");

$blogcomments=mysql_num_rows($result);

 

$sql = "SELECT * FROM blog WHERE CommentNo =" . $blogcomments .")";

$result = mysql_query($sql);

echo '<p>Total: '. $blogcomments .'</p>';

echo '<p>Last Post: ' . $result['DatePosted'].'</p>';

?>

[Jessica]

You missed a step.

mysql_fetch_assoc

[TomMW]

Not sure I understand where or how to use the mysql_fetch_assoc() for this?

[Jessica]

Too bad no one linked directly to the manual which has actual examples.

[TomMW]

I looked at the sample code the first time around. And actually revised my script to:

 

<?php

$con = mysql_connect($database_host,$username,$password);

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db($database_name, $con);

$result = mysql_query("SELECT * From blog");

$blogcomments=mysql_num_rows($result);

 

$sql = '"SELECT * FROM blog WHERE CommentNo="'. $blogcomments. '"';

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {

echo $row["DatePosted"];

}

 

echo '<p>Last Post: ' . $row['DatePosted'].'</p>';

echo '<p>Total: '. $blogcomments .'</p>';

?>

 

But now I get Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\Websites\CitrusTwistKits\blog.php on line 258, which is while ($row = mysql_fetch_assoc($result)) {

[Jessica]

Now time to read the link in my signature on debugging SQL

[mweldan]

last row order by ascending or descending?

 

// why not try something like this?
select DatePosted from blog order by id desc limit 1; 

[TomMW]

Revised code again. Ugly I know but this time it works as I need it to:

 

<?php

$con = mysql_connect($database_host,$username,$password);

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db($database_name, $con);

$result = mysql_query("SELECT * From blog");

$blogcomments=mysql_num_rows($result);

 

$result = mysql_query("SELECT * FROM blog");

while($row = mysql_fetch_array($result))

{

$post = $row['DatePosted'];

}

echo '<p>Last Post: ' . $post .'</p>';

echo '<p>Total: '. $blogcomments .'</p>';

?>

[Jessica]

If all you need is the very last post you really should LIMIT it in the query. You also should do a COUNT in your other query to just get the number of rows. 

Edited February 25, 2013 by Jessica

[Jessica]

If all you need is the very last post you really should LIMIT it in the query. YUo

[Jessica]

If all you need is the very last post you really should LIMIT it in the query. YUo

[P5system]

Your query should be like

SELECT * From blog order by postedDate desc limit 1