PHP中的多个部分查询 [英] Multiple part queries in PHP

问题描述

我一直在学习PHP大约一个星期，我需要一些指导，指出我是否应该使用数组来根据多个变量对数据进行排序。我的表格包含有关歌剧，作曲家及其国籍的信息，并包括指向相关网站的链接。

I've been learning PHP for about a week, and I need some guidance as to whether or not I should be using arrays to sort my data based on multiple variables. My table contains information about operas, the composers, their nationalities, and includes a link to a relevant site.

不要过于冗长，我需要让用户根据国籍，世纪写的数据，以及是否包含相关链接来选择数据。我想让他们更具体，但我的代码很快变得难以管理。对于3个变量，我有八个单独的查询涵盖每种情况。如果我想去4个变量，我将需要16个查询。我知道有一个更好的方法来做到这一点，但是我已经阅读过多次，数组更适合于较小的数量，不一定是包含约40,000行的表。

Without being overly verbose, I need to let a user select data based on Nationality, Century it was written, and whether or not it contains a relevant link. I'd like to let them be even more specific, but my code is quickly becoming unmanageable. For 3 variables, I have eight separate queries covering each case. If I want to go to 4 variables, I will need 16 queries. I know there is a better way to do this, but I've read multiple times that arrays are more appropriate for smaller quantities, not necessarily a table that contains around 40,000 rows.

$req=$_REQUEST['nat'];
$req2=$_REQUEST['cent'];
$req3=$_REQUEST['imslp'];

if ($req!="any" && $req2!="any" && $req3=="yes") $query="SELECT * FROM operadatabase WHERE nationality='$req' AND century=$req2 AND imslplink = imslplink";
else if ($req!="any" && $req2!="any" && $req3=="no") $query="SELECT * FROM operadatabase WHERE nationality='$req' AND century=$req2";
else if ($req!="any" && $req2=="any" && $req3=="yes") $query="SELECT * FROM operadatabase WHERE nationality='$req' AND imslplink = imslplink";
else if ($req!="any" && $req2=="any" && $req3=="no") $query="SELECT * FROM operadatabase WHERE nationality='$req'";

else if($req=="any" && $req2!="any" && $req3=="yes") $query="SELECT * FROM operadatabase WHERE century=$req2 AND imslplink = imslplink";
else if($req=="any" && $req2!="any" && $req3=="no") $query="SELECT * FROM operadatabase WHERE century=$req2";
else if($req=="any" && $req2=="any" && $req3=="yes") $query="SELECT * FROM operadatabase WHERE imslplink = imslplink";
else if($req=="any" && $req2=="any" && $req3=="no") $query="SELECT * FROM operadatabase";

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_query($result);
mysql_close();

$i=0;
for ($i; $i < $num; $i++){
    $f12=mysql_result($result,$i,"fullname");
    $f13=mysql_result($result,$i,"operatitle");
    $f14=mysql_result($result,$i,"nationality");
    $f15=mysql_result($result,$i,"century");
    $f16=mysql_result($result,$i,"imslplink");

    echo "<tr>";
    echo "<td>".$f12."</td><td>".$f13."</td>";
    if(strlen($f14) > 2) {
        echo '<td><img src="/icons/'.$f14.'.png" width="25" height="25"></td>';  
        }
    echo "<td>".$f15."</td>";
    if(substr($f16, 0, 4) == "http") {
        echo "<td><a href=".$f16.">IMSLP</a></td>";
}

最好的方法我可以说我的问题是这样的：在一个查询中，如何我根据一个变量选择一组数据，然后从该集合中选择一个基于第二个变量，然后再选择第三个变量，等等？或者，我可以嵌套查询吗？

The best way I can word my questionis this: Within a query, how can I select a set of data based on one variable, then select from that set based on a second variable, then a third, etc.? Alternatively, can I nest queries?

推荐答案

您可以创建一个包含返回的链接方法的简单类。你必须在这里分配一些if / else，并且可能必须在类中添加额外的变量，但是你可以得到这个想法。

You can create a simple class that includes returned methods for chaining. You would have to assign some if/else here and probably have to add extra variables into the class, but you get the idea.

class   Combiner
    {
        public  $base;
        public  function __construct($base = 'SELECT * FROM operadatabase')
            {
                // This saves the front of your select statement and makes it available
                // for the rest of the methods. Uses in final Write() method
                $this->base =   $base;
            }

        public  $vars;
        public  function FetchNat($value = '')
            {
                // This just checks your $_REQUEST value here
                if(!empty($value) && $value !== 'any')
                    // You'll want to sanitize this value
                    $this->vars[]   =   "nationality = '".mysql_real_escape_string($value)."'";
                // Returning this allows you to use this method/function
                // in your chain. That is why you are returning $this
                // on all your methods/functions
                return $this;
            }

        public  function FetchCent($value = '')
            {
                // This just checks your $_REQUEST value here
                if(!empty($value) && $value !== 'any')
                    // You'll want to sanitize this value
                    $this->vars[]   =   "century = '".mysql_real_escape_string($value)."'";

                return $this;
            }

        public  function FetchIMSLP($value = '')
            {
                // This just checks your $_REQUEST value here
                if(!empty($value) && $value == 'yes')
                    $this->vars[]   =   "imslplink = 'imslplink'";

                return $this;
            }

        public  $sql;
        public  function Write()
            {
                // This compiles all the variables down the line and returns a final string
                $statement  =   (isset($this->vars) && !empty($this->vars))? " where ".implode(" and ",$this->vars):"";
                $this->sql  =   $this->base.$statement;
                return $this;
            }
    }
// Create new instance of your auto-fetcher
$write  =   new Combiner();
// This is a method chain that will return an auto-compiling string as it compiles further down the line
$test   =   $write->FetchCent('')->FetchNat('test')->FetchIMSLP('yes')->Write()->sql;

print_r($test);

这将写：

SELECT * FROM operadatabase where nationality = 'test' and imslplink = 'imslplink';

这篇关于PHP中的多个部分查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！