last post coding help

[mattm1712]

im trying to select the latest post posted by eaach member on my site,

 

the database is layed out in the umage attached,

 

  $username = $_SESSION['user'];

$last = mysql_query("SELECT MAX(id) FROM comments WHERE submittedby='$username'");

$lastdate = mysql_fetch_assoc($last);

  $ld= $lastdate['date'];

  echo $ld;

 

 

whats wrong with this or can i not do it this way?

cheers mat

 

[attachment deleted by admin]

[litebearer]

Just some rambling untested, unproof-read (ie may contain typos)  thoughts (presuming your date field contains a timestamp) -

for only one user...

$username = $_SESSION['user'];
$query = "SELECT * FROM comments WHERE submittedby = '$username' ORDER BY date DESC LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$date = date("Y-m-d", $row['date']);
?>
<table>
<tr><td>Title </td><td><?PHP echo $row['title']; ?></td></tr>
<tr><td>Date </td><td><?PHP echo $date; ?></td></tr>
<tr><td>Submitted by </td><td><?PHP echo $row['submittedby']; ?></td></tr>
<tr><td>Comments </td><td><?PHP echo $row['comments']; ?></td></tr>
</table>

or for all users...

$query = "SELECT * FROM comments GROUP BY submittedby  ORDER BY date DESC";
$result = mysql_query($query);
echo "<table>";
WHILE($row = mysql_fetch_array($result)) {
$date = date("Y-m-d", $row['date'];
?>
<tr><td>Title </td><td><?PHP echo $row['title']; ?></td><td>Date </td><td><?PHP echo $date; ?></td><td>Submitted by </td><td><?PHP echo $row['submittedby']; ?></td><td>Comments </td><td><?PHP echo $row['comments']; ?></td></tr>
<?PHP
}
echo "</table>";