Multiple columns lookup.

tjp12100 · #1 (**permalink**) 05-19-11, 18:03

Hi,
It's been several years since I have taken any programming classes so I am really rusty at this... I have searched but haven't been able to apply anything ive found to my situation:

My table looks something like this:
ID A B C D E F G
1 0 1 0 0 0 1 1
2 1 0 0 0 0 1 1
3 0 0 1 0 1 1 0
4 0 0 0 1 1 0 0

I have an array with some number of column titles(Lets say for instance $search[A,B,D]... but I wont know what's in there.

I need to find each row that has a 1 in each of those columns but a 0 in the others. It should return something like this:

ID A B C D E F G
2 1 0 0 0 0 1 1
1 0 1 0 0 0 1 1
4 0 0 0 1 1 0 0

I know this should be simple but I have got nothing.

Code:

$excl=0;

foreach ($search as $f) 
{
 // Retrieve all the data from the "cells" table 
 $result = mysql_query("SELECT * FROM cells") or die(mysql_error());
 
  //Going through each row....
  while($row = mysql_fetch_array($result)){
  $some=$search;
  
  //cycling through each of the search criteria...
  foreach($some as $e)
  {
    //if the column being searched contains a 1 then add 1 to excl
    if($row[$f]){
      $excl = $excl+$row[$e];
     }
  } 
  
  //at this point I think every search term has been run on the row
  // if excl==1 then then this row meets what I am looking for.
  // <1 means it didnt contain the search term and
  // >1 means it had the others as well...

  if($excl==1){
    echo "<tr>...";
    foreach($row as $field)
       print "\t<td>$field</td>\n"; 
    echo "</tr>...";
  }
 } 
 $excl=0;
}


echo "</table>";
}

That sorta works, but prints twice... for some reason.

Thanks in advance for the help...

futurity · #2 (**permalink**) 05-23-11, 18:13

It's an extraordinarily bad idea to fetch every row from the database and manually search them. Best to let the database do the work and return only those rows that match:

PHP Code:


			
$vals = array();



foreach ($search as $col) {

    $val = array_fill_keys($search, 0);

    $val[$col] = 1;

    $vals[] = '(' . implode(',', $val) . ')';

}



$cols = implode(',', $search);

$vals = implode(',', $vals);



$sql = "select * from cells where ($cols) in ($vals)";

Internet Services

Web Development

Digital Marketing

Consumer Tech