通过两个选择选项过滤结果 [英] filtering results by two select options
问题描述
我的表名是 ads
就像这样:
id ad_country ad_gender ad_birthday1 05 01 2012-02-262 15 02 2011-05-293 78 02 2010-04-12
和表 field_values
像这样:
fieldid fieldtitle fieldvalue14 男 01//ads.ad_gender = field_values.fieldvalue14 女 02//ads.ad_country = field_values.fieldvalue13 阿富汗 0313 阿尔巴尼亚 0413 阿尔及利亚 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'];}
我有两个下拉选择选项
一是选择国家选项:
一个是选择年份的选项:
回声部分如下:
$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.fieldtitleAND ad_gender = 01");$row2 = mysql_fetch_array($sql2);$sql3 = mysql_query("SELECT COUNT(ad_gender) AS women 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 "有 ".$row2['male']." 男性 <br/>";echo "有 ".$row3['female']." 女性
";
但我无法解决它,我认为 if isset 语句中缺少某些内容.
我想要的是:
如果我选择国家和年份,它将显示这个国家和今年的男性和女性人数.谢谢你的时间
SELECT c.gender, COUNT(*) AS 'count'来自广告 a内部联接(SELECT fieldvalue, fieldtitle AS country FROM field_values) bON b.fieldvalue = a.ad_country内部联接(SELECT fieldvalue, fieldtitle AS 性别 FROM field_values) cON c.fieldvalue = a.ad_gender按性别分组
您可以在GROUP BY
之前添加以下过滤器:
- 年份:
WHERE YEAR(a.ad_birthday) = '2012'
- 国家:
WHERE b.country = '阿尔巴尼亚'
查看实际操作.
然后在您将 mysql_
函数转换为 mysqli_
或 PDO
之后(因为它是 已弃用),您可以简单地按性别显示结果:
更新 1
要实现此代码,您可以尝试这样的操作(未测试):
$link = mysqli_connect("localhost", "user_name", "password", "stock");如果(mysqli_connect_error()){die('连接错误('.mysqli_connect_errno().')'.mysqli_connect_error());}$stmt = mysqli_prepare($link, "SELECT c.gender, COUNT(*) AS 'count' FROM ads aINNER JOIN (SELECT fieldvalue, fieldtitle AS country FROM field_values) b ON b.fieldvalue = a.ad_countryINNER JOIN (SELECT fieldvalue, fieldtitle AS sex FROM field_values) c ON c.fieldvalue = a.ad_gender哪里 b.country = ?AND (YEAR(a.ad_birthday) = ? OR YEAR(a.ad_birthday) <> NULL) GROUP BY c.gender");mysqli_bind_param($stmt, 'ss', $country, $year) 或 die(mysqli_error($dbh));$result = mysqli_stmt_execute($stmt) or die(mysqli_error($link));while($row = mysqli_fetch_assoc($result)) {回声有".$row[count] .' ' .$row[性别] ."<br/>\n";}mysqli_close($link);
更新 2
由于某些原因您无法使用 mysqli
,下面的代码应该可以工作.请注意,它假定国家/地区不为空.
$query = "SELECT c.gender, COUNT(*) AS 'count' FROM ads aINNER JOIN (SELECT fieldvalue, fieldtitle AS country FROM field_values) b ON b.fieldvalue = a.ad_countryINNER JOIN (SELECT fieldvalue, fieldtitle AS sex FROM field_values) c ON c.fieldvalue = a.ad_genderWHERE 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)) {回声有".$row[count] .' ' .$row[性别] ."<br/>\n";}
i have table name is ads
like that:
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
and table field_values
like that :
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
... ... ...
and the code :
session_start();
if (isset($_POST['country'])){
$country = $_POST['country'];
$_SESSION['country'] = $_POST['country'];
}
if (isset($_POST['year'])){
$year = $_POST['year'];
$_SESSION['year'] = $_POST['year'];
}
and i have two drop down select options
one is to select option of countries:
<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>
and one is to select option of year :
<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>
the echo part is as follows :
$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 />";
but i couldnt come around it , i think something is missing in if isset statment.
what i want is :
if i select country and year it will echo how many male and female in this country and by this year. thx for your time
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
You can add the following filters before GROUP BY
:
- Year:
WHERE YEAR(a.ad_birthday) = '2012'
- Country:
WHERE b.country = 'Albania'
See it in action.
Then after you convert your mysql_
functions to mysqli_
or PDO
(because it is being deprecated), you can simply display the result by gender:
| GENDER | COUNT | ------------------ | Female | 2 | | Male | 1 |
Update 1
To implement this code, you can try something like this (not tested):
$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);
Update 2
Since you are unable to use mysqli
for some reason, the code below should work. Please note that it assumes that country is not empty.
$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";
}
这篇关于通过两个选择选项过滤结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!