DELETING PAST SQL DATA with php

[Guest]

Hello

I  have a chat program that I would like to delete the oldest chat  after 5 chats. So that only the 5 recent chats appear and anything after 5 is deleted. Thank you. This in the code.

//Quering database

$id=$_SESSION['id'];

$collect=mysql_query("SELECT * FROM chats WHERE userid='$id'");

$collectnum=mysql_num_rows($collect);

if($collectnum>5)

{

//delete old chats if user has more than 5 chats

$delete=mysql_query("DELETE * FROM chats WHERE userid='$id' ORDER BY ASC  LIMIT 1");

//I hoped the above code would delete the oldest chat in the database for the user but after the 5 chat the delete function does not work as intended.

ASC in the code hopes to delete things in assending order making the fifth chat be deleted first.

//My problem is that the above code does not work.

}

else

{

while(rows=mysql_fetch_assoc($collect))

{

//All CHATS OF THE INDIVIDUAL USER.

}

}

[David_Powers · Accepted solution]

Tony404 wrote:

I  have a chat program that I would like to delete the oldest chat  after 5 chats. So that only the 5 recent chats appear and anything after 5 is deleted.

Moved to the Dreamweaver Application Development forum, which is more appropriate for this sort of question.

I wonder why you want to delete the older material? If you simply want to display on the the five most recent ones, assuming the primary key of the chats table is chat_id, you can do this:

$id = $_SESSION['id'];
$result = mysql_query("SELECT * FROM chats WHERE userid=$id
                       ORDER BY chat_id DESC LIMIT 5");

However, if you really want to delete the older records, you can do it like this:

$id = $_SESSION['id'];
$result = mysql_query("SELECT COUNT(*) FROM chats WHERE userid = $id");
// if more than 5 results, delete the old ones
if (mysql_num_rows($result) > 5) {
  // get the primary key of the sixth oldest
  $result = mysql_query("SELECT chat_id FROM chats WHERE userid = $id
                         ORDER BY chat_id DESC LIMIT 5,1");
  $row = mysql_fetch_assoc($result);
  $no6 = $row['chat_id'];
  mysql_query("DELETE FROM chats WHERE userid = $id
               AND chat_id <= $no6");
}

You can then get all records, and display them. Replace "chat_id" in the preceding code with the name of the primary key column in your chats table.