How to substitute a variable for a field name in a SQL query

Question

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 10-Aug-17 8:58am

Member 13347492

Updated 11-Aug-17 1:30am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

W∴ Balboos, *GHB* · Answer 1 · 2017-08-11T01:30:00

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.