Return array of numbers from my db, use them as a test in another query

Question

I'm struggling with some SQL/php syntax issues here.

I'm trying to write two functions.

The first will query a database and return an array of numbers that match specific criteria.

The second will query a database and use the values from the first function to help select the data I'm looking for.

Here's what I have of the first function so far:

$sql = "SELECT requestee_id FROM user_relationships WHERE requester_id = '$uid' AND rtid = '13'";   
 $resulto = mysql_query($sqlo) or die(mysql_error());
  while($rowo = mysql_fetch_array($resulto)){

                                             }

How do I finish this so it returns an array of requestee_id values?

Here's my second query:

$query = "SELECT * FROM {votingapi_vote} vv WHERE ". drigg_votingapi_query('vv', FALSE) ." AND vv.content_type = 'node' AND vv.content_id = %d";

I want to add another WHERE condition to this query, WHERE vv.uid is a value inside the array from my first function. This second function returns a list of people who voted for a story. I want to return, not everyone who voted, but only the people who are friends of a given user.

Suggestions for finishing the first query and fitting it's values into the second one?

Answer 1

You already have an array from your first query, it's the $rowo variable, you can see what's inside by running this command:

echo("<pre>".print_r($rowo,true)."</pre>");

You can then use the SQL IN clause to constrain your second query by the result of the first. As long as your array looks something like this:

array (
    [0] => 12
    [1] => 43
    [2] => 45
    [3] => 60
)

Then you can use this command:

$in_clause=implode(",",$rowo); // converts the array to 12,43,45,60
$query = "SELECT * FROM {votingapi_vote} vv WHERE ". drigg_votingapi_query('vv', FALSE) ." AND vv.content_type = 'node' AND vv.content_id = %d AND your_id IN ".$in_clause.";";

This code may not work depending on the contents of the array of your first query, so please run the print_r code as suggested and post the contents of your array back here.

Hope this helps.

Answer 2

You can use a subquery to do it in just one query:

$query = "SELECT * FROM
    {votingapi_vote} vv
WHERE
    ". drigg_votingapi_query('vv', FALSE) ."
AND
    vv.content_type = 'node'
AND
    vv.content_id = %d
AND
    vv.uid IN (SELECT
            requestee_id
        FROM
            user_relationships
        WHERE
            requester_id = '$uid'
        AND
            rtid = '13')";