PHP / MYSQL sql selection

Question

need some help figuring this out.

My function:

function cleanDatabase()
{
 $allowedTime = $this->time - 10;
 $query = mysql_query("SELECT * FROM `chatsessions` WHERE `last_active` < ".$allowedTime." AND `public` = 0");
 if( mysql_num_rows($query) > 0 ){
 while($row = mysql_fetch_array($query)){
 //move messages to history for evrey cID
 $query2 = mysql_query(" SELECT * FROM `messages`, `chatsessions` 
 WHERE messages.cID = ".$row['cID']." 
 AND chatsessions.cID = messages.cID
 AND chatsessions.public = 0");
 if( mysql_num_rows($query2) > 0 ){
 while($row2 = mysql_fetch_array($query2)){
 $insert = mysql_query("INSERT INTO `message_history` (`cID`, `mID`, `user_id`, `sender`, `sendtime`, `message`) VALUES 
 (".$row2['cID'].", ".$row2['mID'].", ".$row2['user_id'].", '".$row2['sender']."', ".$row2['sendtime'].", '".$row2['message']."')");
 if($insert)
 $delete = mysql_query("DELETE FROM `messages` WHERE `cID` = ".$row2['cID']);
 if(!$delete)
 return false;
 }
 }
 $query3 = mysql_query("DELETE FROM `chatsessions` WHERE `cID` = ".$row['cID']." AND public = 0");
 }
 }
}

The function does

1. select unactive chats the are NOT public 1

2. Fetch messages from the selected chat

3. insert messages to history

4. if inserted delete old messages

5. delete chat sessions what dont have public = 1

What it needs to do:

1. select unactive chats the are NOT public 1

2. Select messages from cID when the last row with that cID is removed (public rows wont get removed so those messages should always stay)

3. insert messages to history

4. if inserted delete old messages

5. delete chat sessions what dont have public = 1

The problem is there are multiple rows in chatsession with the same cID (one for every user and the permanent with pulbic=1 ) i dont know how to NOT delete messages IF there is a chatsession with that ID that has public = 1

edit: step 2 of how it should work

• MakuraYami: Solved it by adding a count to total chats with unactive chat ID and if unactive chats equal total chats the messages go to history.

Answer 1

Just trying to understand your setup, you retrieve the cID from chatsessions, which would be either be public or private ( public = 1 or 0 ).

Would the messages within the chatsession (cID) all be public or private, as they would inherit the properties from the chat session.

Or is there the ability to have a public message broadcast from a private room (if this is the case - there is a subquery / 2nd statement you can run)

EDIT:

you could reduce the whole procedure and only use 2 sql statements (no loops) - something like

function cleanDatabase() {
$allowedTime = $this->time - 10;
// update history
$query = mysql_query("INSERT INTO `message_history` (`cID`, `mID`, `user_id`, `sender`, `sendtime`, `message`) SELECT messages.cID, messages.mID, messages.user_id, messages.sender, messages.sendtime, messages.message FROM `messages`, `chatsessions` WHERE `last_active` < ".$allowedTime." AND chatsessions.cID = messages.cID AND chatsessions.public = 0");
// delte old rows
$query = mysql_query("DELETE FROM `messages`, `chatsessions` WHERE `last_active` < ".$allowedTime." AND chatsessions.cID = messages.cID AND chatsessions.public = 0");
}

I can test this better later, but should point you in the right direction?

Mark

• MakuraYami: messages are only linked to the cID they dont have private or public, if its a private room the messages have to go to history, I alredy noticed a flaw in my own logic, updated post.
• MakuraYami: That looks much cleaner! but before moving the messages I need it to check if the cID selected is the last row of that cID, if there are other it needs to only remove that row
• MakuraYami: Alright i did get rid of a few query's thanks for your time and surgestions, by the way deleting multiple tables you need to define what you want to delete too: DELETE messages.*, chatsessions.* FROM. Cheers