CODEWITHSUNDEEP
phpmysql
Dan
Dan

Reputation: 997

SELECTing from MySQL based on Previous Refined MySQL Query

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

Upvotes: 1

Views: 303

Answers (3)

Nils
Nils

Reputation: 2061

If you can do something with SQL it's most time the best and fastest option. The query bellow will select all users except for one where the age (in years) is more then 30 years.

I could not write the SQL for the distance because I don't know your data and thus don't know how to calculate it. However I added some place where it can be added.

The "limit" clause will fetch the records from 10 to 20 in the database.

   SELECT 
       * 
    FROM 
       table
    WHERE 
       user != $user
       and (
           (year(now()) - year(table.birthday))  > 30
           or (some match to calculate distance)
        )
    limit 10, 20

If you need more data from another table you can join them together like this.

   SELECT 
       * 
    FROM 
       table
       another_table
    WHERE 
       another_table.user = table.user
       table.user != $user
       and (
           (year(now()) - year(table.birthday))  > 30
           or (some match to calculate distance)
        )
    limit 10, 20

Making several select and looping data in any language is slow compared to selecting in a database.

Upvotes: 1

doublesharp
doublesharp

Reputation: 27599

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;"

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Use IN instead of =

SELECT * FROM $table
WHERE user_id IN (select user_id from other_table where some_condition) 
ORDER BY joindate DESC

Upvotes: 5

Related Questions