Looking for more optimal way to do multiple similar inserts at once
| From: | -{ Rene Brehmer }- | Date: | Thu, 11 Nov 2004 11:14:03 +0000 |
| Subject: | Looking for more optimal way to do multiple similar inserts at once | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-37226@lists.php.net to get a copy of this message | ||
Hi gang
I'm in need of, if at all possible, cutting down the processing time for sending messages to multiple recipients with my insite messenger. The current version runs 1 query to get the names of all recipients, then 1 query per recipient, and then 1 query to deliver a copy to the sender.
The code in question is below. $userID and $username is the current user, and are set by the login system much further up. $_POST['rcvID'] is a multi-select select box where the user can choose which users to send the message to. The rest should be pretty obvious. I use a couple flags on the messages so the system can tell received and sent messages apart in the message boxes.
// add slashes as needed
if (! get_magic_quotes_gpc()) {
$subject = addslashes($_POST['subject']);
$message = addslashes($_POST['message']);
} else {
$subject = $_POST['subject'];
$message = $_POST['message'];
}
// get names of recipients
if (count($_POST['rcvID']) > 1) {
$where = "userID IN('".implode("','",$_POST['rcvID'])."')";
} else {
$where = "userID='".$_POST['rcvID'][0]."'";
}
$rcvquery = "SELECT userID,nickname
FROM hf_users
WHERE $where";
$rcvdata = mysql_query($rcvquery) or die('Unable to get recipient names<br>'.mysql_error());
// send messages
while ($rcv = mysql_fetch_array($rcvdata)) {
$msgquery = "INSERT
INTO hf_privmessages (ownerID,fromID,toID,from_name,to_name,priority,date,subject,message,folder)
VALUES ('".$rcv['userID']."','$userID','".$rcv['userID']."','$username','".$rcv['nickname']."','".$_POST['priority']."',NOW(),'$subject','$message','inbox')";
$result = mysql_query($msgquery) or die('Unable to insert message<br>'.mysql_error());
$to_name = $rcv['nickname'];
}
// create sent copy
if (count($_POST['rcvID']) > 1) {
$toID = implode(',',$_POST['rcvID']);
$to_name = 'multiple';
} else {
$toID = $_POST['rcvID'][0];
}
$msgquery = "INSERT
INTO hf_privmessages (ownerID,fromID,toID,from_name,to_name,is_read,is_sent,priority,date,subject,message,folder)
VALUES ('$userID','$userID','$toID','$username','$to_name','1','1','".$_POST['priority']."',NOW(),'$subject','$message','sent')";
$result = mysql_query($msgquery) or die('Unable to insert message<br>'.mysql_error());