Reusing an Array

PHP
1 

  $page .= "<select name=\\"year\\">\
<option selected value=\\"".$year."\\">".$year."</option>\
";
   $query = "SELECT DISTINCT " .
           "year " .
           "FROM {{table}} " .
           "ORDER BY year ASC";
  $result = doquery($query, "teams");
  while ($row = mysql_fetch_array($result)) { $page .= "<option value=\\"".$row['year']."\\">".$row['year']."</option>\
"; }
$page .= <<<END
        </select>
      </td>
      <td>State: 
END;
  $page .= "<select name=\\"state\\">\
<option selected value=\\"".$state."\\">".$state."</option>\
";
  $query = "SELECT DISTINCT " .
           "state " .
           "FROM {{table}} " .
           "ORDER BY state ASC";
  $result = doquery($query, "teams");
  while ($row = mysql_fetch_array($result)) { $page .= "<option value=\\"".$row['state']."\\">".$row['state']."</option>\
"; }

Should there be two queries like so in this? Could it just be made like:


  $query = "SELECT DISTINCT " .
           "year, state " .
           "FROM {{table}} " .
           "ORDER BY year, state ASC";

If so, how would you reset $result to use it twice?

2

sounds like you need mysql_data_seek()

3

I’m confused. You’ve got year, and state, in the same table, but they’re not related to each other? Sounds like you need a new table, to me.

4

This is used in a FORM with dropdown boxes.
The first time it runs $query, it finds all DISTINCT years within the table TEAMS.
The second time $query runs, it is finding all the DISTINCT states within the table TEAMS.
Each record has: id, teamname, year, state, sport, the last four making it unique to all other records.
It seems that I should be able to just gather all the DISTINCT years and states with only one Query and then use $result twice,once to load and show years within its dropdown box and then again to show states in its own dropdown box.

Sorry if I have trouble explaining things.

5

Did you read the link I posted?

All you need to do is run a single query that collects all the info you need from the db and then use mysql_data_seek() to reset the result set’s pointer each time you want to reuse the result set.

6

Are both queries accessing the same table?