Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: Multiple columns lookup.

    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...
    Last edited by tjp12100; 05-19-11 at 20:57. Reason: added comments

  2. #2
    Join Date
    May 2008
    Posts
    277
    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($search0);
        
    $val[$col] = 1;
        
    $vals[] = '(' implode(','$val) . ')';
    }

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •