Repeated narrowing mysql selections

[axeldrummer]

Hello all!

I'm trying to make a program that narrows to a target through mysql selections and subsequent subselections on certain attributes (columns). I hope I can get some help :P

Here is the situation:

I have the attributes/characteristics a = 1, b = 3, c = 4, d = 6 (for example) that are predefined and I'm trying to find the best match out of a database table.

Each row of the table contains these attributes (or columns) a, b, c, d.

First, I randomly select an attribute to match up. Let's say attribute a is selected, then I will search the table where a = 1 as well. This will return a subset matching this requirement. Then, I will randomly choose another attribute (precluding attribute a). I will perform a search on the subset based on the newly chosen attribute. If successful, this returns an even smaller resulting subset.

This process of generating smaller subsets with newly chosen attributes each time continues until nothing is returned (none in subset match). Then, we merely return a random row in the previously generated subset.


$trait_array = array('a', 'b', 'c', 'd');

$x = rand(0, count($trait_array)-1);

$selection = $trait_array[$x]; //randomly chosen column

$query = "SELECT * FROM table WHERE $selection = '$some_value'";
$result = @mysql_query($query);
$row = @mysql_fetch_assoc($result);

if ($row) {

//go further by using another randomly selected attribute ...

} else {

//just randomly select from the subset

}



In order to keep narrowing down subsets, I have to add additional AND clauses to each new SELECT (i.e. a=1 AND d=6 AND ...). How do you do this exactly in code of a mysql query?

Secondly in code, how do I preclude the previous attribute each time that I randomly select the next attribute?

I suspect that this must be a recursive solution, or is there an easier way? Thanks so much for the help!

[firepages]

rather than recursive queries just do a query per parameter , store the matches and parse those ... note that to get a match on a,b,c&d below is harder than you might expect , so you would have to check if there is an array produced for array_interect() for 4 matches , if not try 3 matches etc.


<?php
$arr = array( 'a', 'b','c','d' ) ;
foreach( $arr as $k ){
$v = rand ( 0,10 ) ;
$q = mysql_query( "SELECT $id FROM $table WHERE `$k`='$v'" ) ;
while( $r = mysql_fetch_row( $q ) ){
$range[$k][] = $r[0] ;
}
}
$arr = array_intersect( $range['a'] , $range['b'] , $range['c'] , $range['d'] ) ;
print_r( $arr ) ;
/*if lucky $arr will be an array of table id's that match your criteria*/
?>


I am sure there are better ways , but I am sure there are worse ones as well ;)