Re: Looking for more optimal way to do multiple similar inserts at

-{ Rene Brehmer }- wrote:

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]."'";
      }

btw. i didnt realized thet 'x = "1"' is faster than 'x IN ("1")' i think you can save this line of code $where = "userID IN ('".implode("','",$_POST['rcvID'])."')";

      $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());

// send Messages $sql = ' INSERT INTO hf_privmessages (ownerID,fromID,toID,from_name,to_name,priority,date,subject,message,folder)

SELECT userID                     AS ownerID,
       ' . (int) $userID . '        AS fromID,
       userID                     AS toID,
       "' . $username . '"          AS from_name,
       nickname                   AS to_name,
       "' . $_POST['priority'] . '" AS priority,
       NOW()                        AS date,
       "' . $subject . '"           AS subject,
       "' . $message . '"           AS message,
       "inbox"                      AS folder

WHERE userID IN (' . implode( ', ', $_POST['rcvID'] ) . ') '; mysql_query ... ; // place copy $toID = implode(',',$_POST['rcvID']); if ( count($_POST['rcvID']) > 1 ) {

    $to_name = 'multiple';

} else {

    // get recipient name
    $to_name = 'multiple';

} $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()); your databes lags normalization!!! -- Sebastian Mendel www.sebastianmendel.de www.warzonez.de www.tekkno4u.de www.nofetish.com www.sf.net/projects/phpdatetime www.sf.net/projects/phptimesheet