如何在SQL查询中将变量替换为字段名称 [英] How to substitute a variable for a field name in a SQL query
问题描述
我有一个包含Fields Species,Adams,Allegheny等的表格(除了第一个,每个字段名称都是一个县名 - 总共67个)。
我有一个基于另一个县名表的选择列表(67条记录)。此列表中的名称与上表的字段名称相匹配。
我希望用户选择一个县,然后只显示字段Species的内容和被选中的县。
如物种,亚当斯
或物种,碳
等。
我尝试了下面的代码,但是当我选择一个县时,会显示Species字段,但没有别的。
我知道我可以为每个县写一个单独的查询,并选择参考,但这似乎是浪费代码。
是有一个简单的方法使这个字段名称替换工作?
我尝试过:
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);
?>
推荐答案
colname_rsCountyLists = - 1;
if(isset(
colname_rsCountyLists = "-1"; if (isset(
_POST ['County'])){
_POST['County'])) {
colname_rsCountyLists =
colname_rsCountyLists =
这篇关于如何在SQL查询中将变量替换为字段名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!