Databases
StackOverflow2013
Postsdbo
POPHP / MYSQL sql selection
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP / MYSQL sql selection
    text
    copied!<p>need some help figuring this out.</p> <p>My function:</p> <pre><code>function cleanDatabase() { $allowedTime = $this-&gt;time - 10; $query = mysql_query("SELECT * FROM `chatsessions` WHERE `last_active` &lt; ".$allowedTime." AND `public` = 0"); if( mysql_num_rows($query) &gt; 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) &gt; 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"); } } } </code></pre> <p>The function does</p> <ol> <li><p>select unactive chats the are NOT public 1</p></li> <li><p>Fetch messages from the selected chat</p></li> <li><p>insert messages to history</p></li> <li><p>if inserted delete old messages</p></li> <li><p>delete chat sessions what dont have public = 1</p></li> </ol> <p>What it needs to do: </p> <ol> <li><p>select unactive chats the are NOT public 1</p></li> <li><p>Select messages from cID when the last row with that cID is removed (public rows wont get removed so those messages should always stay)</p></li> <li><p>insert messages to history</p></li> <li><p>if inserted delete old messages</p></li> <li><p>delete chat sessions what dont have public = 1</p></li> </ol> <p>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</p> <p>edit: step 2 of how it should work</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload