how do i search for a AND b not a OR b

[nearenough]

i am very dim and i have this code:

 

$Track = $_GET['Track'];

 

      $RaceDate = $_GET['RaceDate'];

 

  }

{

      $searchSQL = "SELECT RaceDate, RaceNumber, RaceName, RaceDist, Track, Placing, Sorter, Horseid, Trail, Draw, Driver, Dist, Time, Comment FROM RaceTable WHERE ";

   

      // grab the search types.

      $types = array();

      $types[] = $_GET['RaceNumber']?"`RaceNumber` LIKE '$RaceNumber'":'';

      $types[] = $_GET['Track']?"`Track` LIKE '$Track'":'';

 

     

      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

 

      if (count($types) < 1)

        $types[] = "`RaceNumber` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

      $types[] = "`Driver` LIKE '%{$searchTermDB}%'";

      $types[] = "`Track` LIKE '%{$searchTermDB}%'";

 

  $andOr = isset($_GET['matchall'])?'AND':'OR';

  $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY racedate, racenumber, Sorter, Placing ASC"; // order by title.

 

 

it returns results from that track OR that date, and i need to change it to that track AND that date, which bit do i change please?

 

thank you

[Exabyte]

it won't work that way. mysql would basically be looking for a row where RaceNumber matches multiple values at the same time. what you need to do is join the table to itself:

 

check this out:

http://www.thelampblog.com/2010/05/24/mysql-self-join-a-table/

[nearenough]

i had a go at writing that and came up with:

 

      $Track = $_GET['Track'];

 

      $RaceDate = $_GET['RaceDate'];

 

 

{

      $searchSQL = "SELECT DISTINCT RaceDate.a_id

      FROM RaceTable WHERE AS RaceDate,RaceTable AS

      Track RaceDate.a_id=Track.a_id AND Racedate.b_id=$RaceDate

      AND instance.b_id=$Track";

   

      // grab the search types.

      $types = array();

      $types[] = $_GET['RaceNumber']?"`RaceNumber` LIKE '$RaceNumber'":'';

      $types[] = $_GET['Track']?"`Track` LIKE '$Track'":'';

 

     

      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

 

      if (count($types) < 1)

        $types[] = "`RaceNumber` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

      $types[] = "`Driver` LIKE '%{$searchTermDB}%'";

      $types[] = "`Track` LIKE '%{$searchTermDB}%'";

 

  $andOr = isset($_GET['matchall'])?'AND':'OR';

  $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY racedate, racenumber, Sorter, Placing ASC";

 

 

 

 

which dont work....any clues???

 

 

[Exabyte]

you should read that article again and try to understand it. it's actually pretty well written.

a_id obviously needs to be your primary key.

 

SELECT DISTINCT instance1.a_id
FROM RaceTable AS instance1,RaceTable AS instance2
WHERE instance1.a_id=instance2.a_id AND instance1.RaceNumber=[something]  AND instance2.Driver=[something]