Delete things with MySQL

[Ignatius881]

Hello.

 

I was a problem. I'm trying to help in a webpage, it has a chat, but that chat shows conversations made several hours ago, even several days ago. In fact, that chat doesn't delete any conversation, do you understand me?

 

Well, I want the chat to delete conversations made 3 hours ago or more automatically, so, if anything was written 3 hours ago or more and you log in or you press F5, that thing dissapears. But I dont know how.

 

I'll show the piece of code:

 

$sql = "SELECT count(id_msg) FROM ".$ji."";  // sql
$link=mysql_connect($server,$dbuser,$dbpass);
$result2 = mysql_db_query($database,$sql,$link);
$rows = mysql_fetch_row($result2);
$number=$rows[0];
$tt=$number-100;

if($tt<0){
$tt=0;
}


// we show all messages which are in the database
$query="SELECT * FROM ".$ji." ORDER BY id_msg ASC LIMIT ".$tt.",".$number."";

$result=mysql_db_query($database,$query,$link);

 

Where $ji is the name of the chat (chat1, chat2, chat3...).

 

Well, I modified the piece of code in this way:

 

$sql = "SELECT count(id_msg) FROM ".$ji."";  // sql
$link=mysql_connect($server,$dbuser,$dbpass);
$result2 = mysql_db_query($database,$sql,$link);
$rows = mysql_fetch_row($result2);
$number=$rows[0];
$tt=$number-100;

if($tt<0){
$tt=0;
}


// we show all messages which are in the database
$query="SELECT * FROM ".$ji." ORDER BY id_msg ASC LIMIT ".$tt.",".$number."";
$time=round((time() - strtotime($row2['hour'])) / 60 / 60 / 24);
if ($time>=3){
$queryB="DELETE * FROM ".$ji" ORDER BY id_msg ASC LIMIT ".$tt.",".$number." WHERE hour='$time'";
$resultB=mysql_db_query($database,$queryB,$link);
}

$result=mysql_db_query($database,$query,$link);

 

But it breaks the chat. Any help? Thanks.

[mikosiko]

But it breaks the chat.

 

how?... any error message?.... how are you debugging your code?

 

at first glance I don't see $row2[hour'] defined in any place in the posted code.

 

 

 

 

[Ignatius881]

But it breaks the chat.

 

how?... any error message?.... how are you debugging your code?

 

at first glance I don't see $row2[hour'] defined in any place in the posted code.

 

How? Well, if I don't modify the code, it shows conversations, but if I modify the code, it shows nothing.

 

$row2['hour'] is defined in other places, and in the MySQL file.

[requinix]

There can be distinct advantages to storing all the chat messages. Instead, modify your queries to only show the most recent ones.

...WHERE date >= NOW() - INTERVAL 3 HOURS

[mikosiko]

... so.. you are not debugging your code at all, otherwise you will probably see easily that you have some error(s) in the code that you are adding to your actually functional code..

 

4 suggestions for you:

a) add the 2 lines under my signature immediately after your first <?php line  those should allow you to display some errors that you could have in your code.

b) use at least as a temporary alternative a die("Error : " . mysql_error()) sentence after your mysql_query()  p.e

$resultB=mysql_db_query($database,$queryB,$link) or die("Error : " . mysql_error());

c) read about the exact syntax for DELETE here ... it point is directly related to your problem

 

d) also will worth for you or your future plans know this

 

requinix point is something also valid to consider

[Ignatius881]

I tried everything, and nothing works

 

Thanks anyway.

[Ignatius881]

Oh, too bad.

 

I had to write DELETE FROM, not DELETE * FROM

 

Well, I can delete conversations, but only manually, not everytime I press F5 or something