问题描述
我一直在学习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中的多个部分查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!