Looking for more optimal way to do multiple similar inserts at once

From: 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()); 


« previous php.db (#37226) next »