Could someone move this thread to "Database" please. Somehow I posted it in a wrong section. Sorry.

Need help to combine 3 queries into 1.

The script below is to invite users to interest groups. Now it uses 3 queries:
1. gets user data from users table
2. checks if user is not already a member of this group
3. checks if invitation has not been sent to this user.

Is there any way how to combine all three queries into one? If somebody invites 30 users at the same time, it will be 90 queries? I think it's to much.
However, I don't know what is better - to run multiple simple queries or one complicated query which selects data from three huge tables.

Thanks.

$values = array();
$result = mysql_query("SELECT id, nickname from users WHERE id IN(". implode(', ', $_POST['user_id']).")") or die('Error1');

while(list($usid) = mysql_fetch_row($result)) {

$result2 = mysql_query("SELECT memberid FROM groups_members where memberid='$usid' and group='$group'") or die('Error2');
$count2 = mysql_num_rows($result2);

$result3 = mysql_query("SELECT invitedid FROM groups_invitations where invitedid = '$usid' and group='$group'") or die('Error3');
$count3 = mysql_num_rows($result2);

if ($count2 == 0 && $count3 == 0) {
array_push($values, "('$usid','$group','$nickname')");
}

}
$result1 = mysql_query("insert INTO groups_invitations (invitedid, group, nick) VALUES ".implode(",", $values)) or die('Error 4');

    Your third mysql_num_rows uses your second query's results. Might not be what you had in mind 😉

    $result3 = mysql_query("SELECT invitedid FROM groups_invitations where invitedid = '$usid' and group='$group'") or die('Error3'); 
$count3 = mysql_num_rows($result2);

    No time to help you on topic at hand but after I go shopping I'll give it a better look!

      Looking at the queries that you currently have, a simple solution that may or may not be efficient springs to mind:

      SELECT id, nickname FROM users
    WHERE id IN (...)
        AND id NOT IN (SELECT memberid FROM groups_members WHERE group='$group')
        AND id NOT IN (SELECT invitedid FROM groups_invitations WHERE group='$group')

      The "..." part should be replaced by your implode that involves $POST['user_id'], but note that you must sanitise $POST['user_id'] before doing that.

      With the results of this query, you can construct your array like you did then do the insertions.

        Thanks laserlight. Of ourse, I do sanitise $_POST values.

          Write a Reply...