Click here to Skip to main content
14,979,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table with the Fields Species, Adams, Allegheny, etc. (except for the first, each of the field names is a county name – 67 in all).

I have a select list based on another table of county names (67 records). The names in this list match the field names of the table above.

I want the user to select a county, and then display the contents of only the fields “Species” and the county that was selected.

Such as Species, Adams
or Species, Carbon
etc.

I tried the code below, but when I select a county, the Species field displays, but nothing else.

I know I can write a separate query for each county and have the select refer to that, but that seems like a waste of code.

Is there a simple way to make this field name substitution work?

What I have tried:

$colname_rsCountyLists = "-1";
if (isset($_POST['County'])) {
  $colname_rsCountyLists = $_POST['County'];
}

$maxRows_rsCountyLists = 10;
$pageNum_rsCountyLists = 0;
if (isset($_GET['pageNum_rsCountyLists'])) {
  $pageNum_rsCountyLists = $_GET['pageNum_rsCountyLists'];
}
$startRow_rsCountyLists = $pageNum_rsCountyLists * $maxRows_rsCountyLists;

mysql_select_db($database_PaSiteGuide, $PaSiteGuide);
$query_rsCountyLists = "SELECT AOU_Order, SPECIES, SCINAME, {$colname_rsCountyLists} FROM countybirdlists";
$query_limit_rsCountyLists = sprintf("%s LIMIT %d, %d", $query_rsCountyLists, $startRow_rsCountyLists, $maxRows_rsCountyLists);
$rsCountyLists = mysql_query($query_limit_rsCountyLists, $PaSiteGuide) or die(mysql_error());
$row_rsCountyLists = mysql_fetch_assoc($rsCountyLists);

if (isset($_GET['totalRows_rsCountyLists'])) {
  $totalRows_rsCountyLists = $_GET['totalRows_rsCountyLists'];
} else {
  $all_rsCountyLists = mysql_query($query_rsCountyLists);
  $totalRows_rsCountyLists = mysql_num_rows($all_rsCountyLists);
}
$totalPages_rsCountyLists = ceil($totalRows_rsCountyLists/$maxRows_rsCountyLists)-1;

mysql_select_db($database_PaSiteGuide, $PaSiteGuide);
$query_rsCounties = "SELECT counties.CountyName FROM counties ORDER BY counties.CountyName";
$rsCounties = mysql_query($query_rsCounties, $PaSiteGuide) or die(mysql_error());
$row_rsCounties = mysql_fetch_assoc($rsCounties);
$totalRows_rsCounties = mysql_num_rows($rsCounties);
?>

<head>
</head>

<body>			

<form action="Test1.php" method="post" name="form1" target="_self" id="form1">

<p>
  <label for="select">Select:</label>
  <select name="County" id="select" onchange="this.form.submit()">
    <?php
do {  
?>
    <option value="<?php echo $row_rsCounties['CountyName']?>"><?php echo $row_rsCounties['CountyName']?></option>
    <?php
} while ($row_rsCounties = mysql_fetch_assoc($rsCounties));
  $rows = mysql_num_rows($rsCounties);
  if($rows > 0) {
      mysql_data_seek($rsCounties, 0);
	  $row_rsCounties = mysql_fetch_assoc($rsCounties);
  }
?>
  </select>
<p> </p>

</form>

<p><?php echo $colname_rsCountyLists;?></p>
<table width="372" border="0">
<tr>
  <td width="180"><?php do { ?>
      <table width="372" border="0">
        <tr>
          <td width="182"><?php echo $row_rsCountyLists['SPECIES']; ?></td>
          <td width="180"><?php echo $row_rsCountyLists['{$colname_rsCountyLists}']; ?></td>
          </tr>
      </table>
      <?php } while ($row_rsCountyLists = mysql_fetch_assoc($rsCountyLists)); ?></td>
</tr>
</table>
<p> </p>
</body>
</html>
<?php
mysql_free_result($rsCountyLists);
mysql_free_result($rsCounties);
?>
Posted
Updated 11-Aug-17 1:30am
Comments
j snooze 10-Aug-17 17:18pm
   
wouldn't it be more like
$query_rsCountyLists = "SELECT AOU_Order, SPECIES, SCINAME," . $colname_rsCountyLists . " FROM countybirdlists";
Member 13347492 11-Aug-17 6:12am
   
That doesn't work either.

1 solution

Your problem is a lot bigger than you think.

A table with 67 fields is a problem in and of itself. MySQL is a relational database and you're not making use of this.

You need a table more like:
REC_ID, SPECIES char, SPECIES DATA, . . . , COUNTY_ID int
along with a second table
COUNTY_ID int, COUNTY char

In the second table, you list each of the counties with a unique (Numeric ID). This is best done by making it an IDENTITY field. You now have a table with 66 rows, one for each county.

Now - when a county is selected you can pull a list from the first table of all species that are related to that county and pick from it. Or vis-versa.

Display the result by creating an INNER JOIN on the two tables (via COUNTY_ID) and then have, in the where clause, the unique record that has your selected species and county.

WHY IS THIS BETTER? Both lists can expand without changing your SQL or Table Schema. It works "forever".

DISCLAIMER: there are even better ways to create this association. I, for example, would have a table of counties/county_id's; species data, species_id's, and a table associating the two, County_ID, Species_ID. Then, one picks a county (or picks a species) and can find all associated matches.

Not quite an answer to your question. Maybe even more useful.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900