CODEWITHSUNDEEP
phpmysql
kristyan markes
kristyan markes

Reputation: 37

use result of mysql query into a new query

I have this mysql table.

name    |     total
chris   |     5
jan     |     3
bob     |     2
eric    |     4

chris and jan were selected using this code

$query =  " select * from table where name = '$given_name' "; 
// &given_name = result from another query

I want to store the result of the query to a variable.

while($row=mysql_fetch_assoc($query)){
    $result = $row['name']; 
} // i want to store both chris and jan to $result

Then i will use the result of the query to another query. I want to select the remaining names. Not the ones on the first query. I want chris and jan not to be selected with this query because it is stored in $result

select * from table where name != $result ;

But one name was just stored in $result. I want them both to be stored in $result.

Upvotes: 0

Views: 102

Answers (3)

Kuya
Kuya

Reputation: 7310

Assuming that you do not know the names of the resultset you can simply (a) select the first two names from the resultset, (b) concatenate them in a string and finally (c) use "NOT IN" as your query parameter.

$numPicks = 2; // decide how many names you want in the list
// OR if you want to exclude ALL names found in the first query
$num_rows = mysql_num_rows($query);

$nameList = ''; // start with an empty string
for($i=0; $i<$numPicks; $i++) { // then use $num_rows here instead of numPicks
    $nameList .= $row['name'].',';
}
$nameList = rtrim($nameList,','); // remove trailing comma from the string

$sql = "select * from table where name NOT IN ($nameList)";

Upvotes: 1

Ahmed Numaan
Ahmed Numaan

Reputation: 1062

You can use database query like below to exclude chris and jan:

select * from table where name NOT IN( $result );

Upvotes: 1

Nick
Nick

Reputation: 147146

You could use FIND_IN_SET to see if the names had been fetched before. Firstly you need to make $result an array of all the names:

$result = array();
while ($row=mysql_fetch_assoc($query)) {
     $result[] = $row['name']; 
}

Then you can write your query to exclude the names in $result:

$sql = "SELECT * FROM table WHERE NOT FIND_IN_SET(name, '" . implode(',', $result) . "')";

Upvotes: 1

Related Questions