executing the query once

[php_begins]

I have the following query where $userid is about 100,000 userids separated by commas.

 

<?
$getpostid=mysql_query("SELECT DISTINCT post.username,post.threadid,thread.threadid,thread.forumid from post,thread WHERE post.username='$username' AND post.threadid=thread.threadid AND post.userid IN ($userid) AND thread.forumid IN ($forumids)") or die (mysql_error());
?>

can i just print all the usernames from above result separated by ';' without looping it multiple times?

 

Could someone help me out how to do above in an efficient manner?

[xyph]

You need to explain exactly what you're trying to accomplish. How did you get this list of 100,000 users?

[php_begins]

the userids is stored in a variable called $userid.

forumids are stored in $forumid.

Now i want to print all usernames by querying the post and thread table . I do not want to print the usernames in a while loop.it might take a lot of time.

[xyph]

It WILL take a lot of time.

 

From what I understand, the engine will check the entire table for each value within the IN() function. That's 100,000 table look-ups.

 

Why are you doing this? What are you trying to accomplish?

[php_begins]

    I need to see if the user has posted at least 1 post in one of these forums; 12, 9, 96, 176

    and last activity is more then six months ago, meaning they have not logged in within the last 6 months. and then print those usernames

  Given current table structure of post,thread,user and forum. This is the only way i could think of so far.Here is my complete code:

 

$forumids='12,9,96,176';
$getusername=mysql_query("SELECT userid from user WHERE posts >=1 AND lastactivity <='$newDate' ORDER by lastactivity") or die (mysql_error());
if(mysql_num_rows($getusername) > 0)
{
   $flag=1;
   while($getusername_result=mysql_fetch_assoc($getusername))
   {
      if($flag == 1)
      {
         $userid=$getusername_result['userid'];
         $flag=2;
      }
      else
      {
         $userid.=",".$getusername_result['userid'];
      }
   }
}
else
{
  //Nothin
}
echo $userid;
      //$getpostid=mysql_query("SELECT DISTINCT post.username,post.threadid,thread.threadid,thread.forumid from post,thread WHERE post.username='$username' AND post.threadid=thread.threadid AND post.userid IN ($userid) AND thread.forumid IN ($forumids)") or die (mysql_error());
      while($getpostid_result=mysql_fetch_assoc($getpostid))
      {

         $postusername=$getpostid_result['username'];
         echo $postusername."<br>";

      }

[xyph]

You would want to do this in a single query, using a join. You should Google for an introductory article (better than a tutorial IMO).

 

MySQL Joins Introduction is probably a good search.