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

select unactive chats the are NOT public 1
Fetch messages from the selected chat
insert messages to history
if inserted delete old messages
delete chat sessions what dont have public = 1

What it needs to do:

select unactive chats the are NOT public 1
Select messages from cID when the last row with that cID is removed (public rows wont get removed so those messages should always stay)
insert messages to history
if inserted delete old messages
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

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. — MakuraYami
– MakuraYami, Commented Mar 8, 2012 at 14:27

Mark Willis · Accepted Answer · 2012-03-08 14:24:40Z

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

edited Mar 8, 2012 at 14:24

answered Mar 8, 2012 at 13:47

Mark Willis

1,7511 gold badge15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MakuraYami Over a year ago

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 Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

PHP / MYSQL sql selection

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related