我的表名是ads，如下所示：
id ad_country ad_gender ad_birthday
1 05 01 2012-02-26
2 15 02 2011-05-29
3 78 02 2010-04-12
和类似的表field_values：
fieldid fieldtitle fieldvalue
14 Male 01 // ads.ad_gender = field_values.fieldvalue
14 Female 02 //ads.ad_country = field_values.fieldvalue
13 Afghanistan 03
13 Albania 04
13 Algeria 05
... ... ...
和代码：
session_start();
if (isset($_POST['country'])){
$country = $_POST['country'];
$_SESSION['country'] = $_POST['country'];
}
if (isset($_POST['year'])){
$year = $_POST['year'];
$_SESSION['year'] = $_POST['year'];
}
我有两个下拉选择选项
一种是选择国家选项：
<form action="" id="countryform" method="post">
<select id="country" name ="country" size="1" class="select" onchange="this.form.submit();;" >
<option value="0" >Please Select a Country</option>
<?php
$sql = mysql_query ("SELECT * FROM field_values WHERE fieldid = 13 GROUP BY fieldtitle ");
while ($row = mysql_fetch_array($sql) ){
?>
<option value="<?php echo $row['fieldtitle'] ;?>" <?php
if ($country == $row['fieldtitle']) { echo " selected='selected'"; } ?> > <?php echo $row['fieldtitle'] ;?>
</option>
<?php } ?>
</select>
<input type="hidden" name="hiddenselect" value="<?php echo $country; ?>" />
</form>
其中之一是选择年度选项：
<form action="" id="yearform" method="post">
<select id="year" name ="year" size="1" class="select" onchange="this.form.submit();;" >
<option value="0" >Please Select the Year</option>
<?php
$sql6 = mysql_query("SELECT ad_birthday,(substr(ad_birthday , 1, 4)) AS year FROM ads GROUP BY year ") ;
while ($row6 = mysql_fetch_array($sql6) ){
?>
<option value="<?php echo $row6['year'] ;?>" <?php
if ($year == $row6['year']) { echo " selected='selected'"; } ?> > <?php echo $row6['year'] ;?>
</option>
<?php } ?>
</select>
<input type="hidden" name="hiddenselect" value="<?php echo $year; ?>" />
</form>
回波部分如下：
$sql2 = mysql_query("SELECT COUNT(ad_gender) AS male FROM ads INNER JOIN field_values ON field_values.fieldvalue = ads.ad_country WHERE '".$_POST['country']."' = field_values.fieldtitle AND ad_gender = 01 ");
$row2 = mysql_fetch_array($sql2) ;
$sql3 = mysql_query("SELECT COUNT(ad_gender) AS female FROM ads INNER JOIN field_values ON field_values.fieldvalue = ads.ad_country WHERE '".$_POST['country']."' = field_values.fieldtitle AND ad_gender = 02 ");
$row3 = mysql_fetch_array($sql3) ;
echo "There are ".$row2['male']." male <br />";
echo "There are ".$row3['female']." female<br />";
但是我绕不过去了，我想这件事少了点什么。
我想要的是：
如果我选择国家和年份，它将反映出这个国家和今年有多少男性和女性。时间的thx
SELECT c.gender, COUNT(*) AS 'count'
FROM ads a
INNER JOIN
(SELECT fieldvalue, fieldtitle AS country FROM field_values) b
ON b.fieldvalue = a.ad_country
INNER JOIN
(SELECT fieldvalue, fieldtitle AS gender FROM field_values) c
ON c.fieldvalue = a.ad_gender
GROUP BY c.gender
您可以在GROUP BY之前添加以下过滤器：
- 年份：
WHERE YEAR(a.ad_birthday) = '2012' - 国家：
WHERE b.country = 'Albania'
看看它在行动中。
然后，在您将mysql_函数转换为mysqli_或PDO（因为它已被弃用）后，您可以简单地按性别显示结果：
|GENDER|COUNT|------------------|女性|2||男性|1|
更新1
要实现此代码，您可以尝试以下操作（未经测试）：
$link = mysqli_connect("localhost", "user_name", "password", "stock");
if (mysqli_connect_error()) {
die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}
$stmt = mysqli_prepare($link, "SELECT c.gender, COUNT(*) AS 'count' FROM ads a
INNER JOIN (SELECT fieldvalue, fieldtitle AS country FROM field_values) b ON b.fieldvalue = a.ad_country
INNER JOIN (SELECT fieldvalue, fieldtitle AS gender FROM field_values) c ON c.fieldvalue = a.ad_gender
WHERE b.country = ? AND (YEAR(a.ad_birthday) = ? OR YEAR(a.ad_birthday) <> NULL) GROUP BY c.gender");
mysqli_bind_param($stmt, 'ss', $country, $year) or die(mysqli_error($dbh));
$result = mysqli_stmt_execute($stmt) or die(mysqli_error($link));
while($row = mysqli_fetch_assoc($result)) {
echo "There are " . $row[count] . ' ' . $row[gender] . "<br />'n";
}
mysqli_close($link);
更新2
由于某些原因，您无法使用mysqli，下面的代码应该可以工作。请注意，它假设这个国家不是空的。
$query = "SELECT c.gender, COUNT(*) AS 'count' FROM ads a
INNER JOIN (SELECT fieldvalue, fieldtitle AS country FROM field_values) b ON b.fieldvalue = a.ad_country
INNER JOIN (SELECT fieldvalue, fieldtitle AS gender FROM field_values) c ON c.fieldvalue = a.ad_gender
WHERE b.country = " . mysql_real_escape_string($country);
if(isset($year)) $query .= " AND YEAR(a.ad_birthday) = " . mysql_real_escape_string($year);
$query .= ' GROUP BY c.gender';
$sql2 = mysql_query($query);
while($row = mysql_fetch_assoc($sql2)) {
echo "There are " . $row[count] . ' ' . $row[gender] . "<br />'n";
}