通过两个选择选项过滤结果 [英] filtering results by two select options

查看：36
本文介绍了通过两个选择选项过滤结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的表名是 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'];}

我有两个下拉选择选项

一是选择国家选项:

 
<select id="country" name="country" size="1" class="select" onchange="this.form.submit();;"><option value="0" >请选择国家</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'] ;?>"<?phpif ($country == $row['fieldtitle']) { echo " selected='selected'";?>><?php echo $row['fieldtitle'] ;?></选项><?php } ?></选择><input type="hidden" name="hiddenselect" value="<?php echo $country; ?>"/></表单>

一个是选择年份的选项:

<select id="year" name="year" size="1" class="select" onchange="this.form.submit();;"><option value="0" >请选择年份</option><?php$sql6 = mysql_query("SELECT ad_birthday,(substr(ad_birthday, 1, 4)) AS year FROM ads GROUP BY year ") ;而 ($row6 = mysql_fetch_array($sql6) ){?><option value="<?php echo $row6['year'] ;?>"<?phpif ($year == $row6['year']) { echo " selected='selected'";?>><?php echo $row6['year'] ;?></选项><?php } ?></选择><input type="hidden" name="hiddenselect" value="<?php echo $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 之后(因为它是 已弃用)，您可以简单地按性别显示结果:

<前>|性别 |计数 |------------------|女 |2 ||男 |1 |

更新 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屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆