基于先前精炼的MySQL查询从MySQL中选择

Question

really didn't want to ask this because I'm sure its simple but:

I have a database of users which include fields for postcode(zipcode) and Date of Birth.

A visitor can search for a user by age and distance to their own location. To do this, I have to SELECT all users, then calculate their age, and then calculate their distance, something like this:

$result = queryMysql("SELECT user FROM table WHERE user !='$user' ORDER BY joindate DESC ");
$num = mysql_num_rows($result);

for ($j = 0 ; $j < $num ; ++$j)
{
$row = mysql_fetch_row($result);
if ($row[0] == $user) continue;

$query = "SELECT * FROM table WHERE user='$row[0]'";
$res=mysql_query($query);
$users=mysql_fetch_assoc($res);

//Get Date of Birth and Calculate Age
$dob = $users['age'];
$age = ...

//Get Profile's Postcode and Calculate Distance 
$profilepc = $views['postcode'];
$distance = ...

if(($distance <200) AND ($age >18 AND <30)) {

}

So far, no problem. However I then want to run another query (for pagination) selecting ONLY those users who fit within the age and distance parameters set by the visitor, which i can echo in the above IF statement, but i don't know how to include in a new Query.

So, HOW do I place the results of the first query into something(an array?), and then user the user_id's (which are unique) from that first query to only select the required users for my pagination? Something like:

SELECT * FROM $table WHERE user_id=(filtered user_id's) ORDER BY joindate DESC 

I hope that makes sense. Thanks

Answer 1

You can use a subquery for your IN clause, or if you already have a PHP array of IDs:

// assuming your array of IDs is
$user_ids = array(1, 2, 3, 4);

// use implode() to create a comma separated list for use in your IN clause
$query = "SELECT * FROM {$table}
WHERE user_id IN (" . implode(',', $user_ids) . ") 
ORDER BY joindate DESC;"