Reusing an Array

[gregs]

  $page .= "<select name=\"year\">\n<option selected value=\"".$year."\">".$year."</option>\n";
   $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>\n"; }
$page .= <<<END
        </select>
      </td>
      <td>State: 
END;
  $page .= "<select name=\"state\">\n<option selected value=\"".$state."\">".$state."</option>\n";
  $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>\n"; } 

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?

[webdev1958]

sounds like you need mysql_data_seek()

[StarLion]

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.

[gregs]

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.

[webdev1958]

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.

[SpacePhoenix]

Are both queries accessing the same table?