Pulling Item from DB and finding more after based on the first finds.

[0o0o0]

I have a list of say.. Race Horses.

Race 1 #2 Horse A 110 //(speed Figure)
Race 1 #3 Horse B 90
Race 1 #4 Horse C 50

Race 2 #6 Horse A 90
Race 2 #7 Horse B 90
Race 2 #3 Horse C 90

Race 3 #3 Horse A 30
Race 3 #4 Horse B 60
Race 3 #2 Horse C 120 // (speed Figure)
Race 3 #7 Horse D 20

Race 4 #7 Horse A 70
Race 4 #6 Horse B 30

Race 5 #8 Horse C 60

Etc etc..

OK I can pull only the two horses with a speed figure over 100... easily.. and it will return (code below)

RACE 1 #2 HORSE A 110
Race 3 #2 Horse C 120

But what im looking for its it to "go back in at the same time and list the rest of the horses in that race..." ( for exactors and triactors betting etc)

Heres my no brainer code.. works..

$query = "SELECT `Track` ,`RaceNumber`, `Horse` FROM `DataBase` WHERE  `SpdFigure` > 100 "; 
 $result = mysql_query($query)
      or die ("Couldn't execute query.");          

  while ($row = mysql_fetch_array($result))             

 {
  extract($row);   


echo "<p>$Track $RaceNumber $Horse</p>\n";


}

But I'd love to have an outcome like this...

Race 1 win.. #2 Horse A (the one with speed Figure 110) with #3 Horse B, #4 Horse C (for 2nd or third.)
Race 3 #2 Horse A (the one with speed Figure 120) with #3 Horse B, #4 Horse C, #7 Horse D

I get the winners texted to my phone, but now im looking to get the winner with the possible horse who will come second as well.

Hope I didnt confuse this question.. pull the horses in races that have over 100 speed figure horses in them.. then go back and get the rest of the horses that are in that race with the 100+ speed figure horse.. and present it like

Race 1 win.. #2 (the one with speed Figure 110) with #3 #4

and for race number 3 ..
Race 3 win .. #2 with #3 Horse B, #4 Horse C, #7 Horse D

I dont need all the echo examples.. just need to know if you do a query using $Track $Racenumber $Horse inside another query or something.

Thanks for any help suggestions.

[jkurrle]

Try something like this:

<?php
$myArray=array();
$query = "SELECT `Track` ,`RaceNumber`, `Horse`, `SpdFigure `FROM `DataBase`"; 
$result = mysql_query($query)
  or die ("Couldn't execute query.");          

while ($row = mysql_fetch_array($result))             

  {
  extract($row);
  if (!isset($myArray[$RaceNumber]))
    {
    $myArray[$RaceNumber] = array();
    }
  $myArray[$RaceNumber][]=array("track" => $Track, "horse" => $Horse, "speed" => $SpdFigure, "raceNum" => $RaceNumber );
  } // End while ($row = mysql_fetch_array($result))
foreach($myArray as $race)
  {
  $raceHit=0;
  foreach($race as $horse)
    {
    if($horse['speed'] >= 100) $raceHit = 1;
    }
  if($raceHit == 1)
    {
    echo "Race $race[1]['raceNum'] had a horse with a speed over 100: \r\n";
    //get the speeds into a separate array
    $raceSort=array();
    foreach ($race as $horse)
      {
      $raceSort[]=$horse['speed'];
      }
    //use array_multisort to sort by fastest horse
    array_multisort($raceSort, SORT_DESC, $race);
    foreach ($race as $horse)
      {    

      echo $horse['horse'] . ", " . $horse['track'] . ", " . $horse['speed'] . "\r\n";
      }
    echo "\r\n";
    }
  } // End foreach($myArray as $race)
?>

It might work for you.

[NogDog]

You could do it in one query, maybe something like:

SELECT `Track` ,`RaceNumber`, `Horse`, `SpdFigure`
FROM `DataBase` 
WHERE  `RaceNumber` IN (
  SELECT `RaceNumber` FROM `DataBase` WHERE `SpdFigure` > 100
)
ORDER BY `RaceNumber`, `SpdFigure` DESC