gpt4 book ai didi

PHP 多重查询问题

转载 作者:行者123 更新时间:2023-11-30 01:32:54 24 4
gpt4 key购买 nike

我需要查询一个表,并使用该信息查询另一张表。最后,我需要对第一个表运行查询,显示表和查询的结果。

除了 $summary = mysql_result($y,$j, 'WebDesc'); 之外一切正常;相反,$sql3 正确回显,当我运行手动查询,它会提取WebDesc的数据。我在手册中看到 mysql_query 不支持“多个查询”,但我真的不知道这意味着什么。前两个查询($sql$sql2)可以很好地协同工作,并且我使用 mysql_query 编写了具有多个查询的其他脚本。

$sql = "SELECT * FROM T_AdditionalInfo WHERE Description LIKE '" . $page_title . "%'" . " AND (ProductType='FG8' OR ProductType='FG1') AND Active='Yes'";
$x = mysql_query($sql);
$table_data = "";

while ($result = mysql_fetch_array($x)) {
$kilnnum = $result['ItemNo'];
} //kilnnum should be set to ItemNo of last matching kiln

$sql2 = "SELECT * FROM T_Accessories WHERE KilnNo='$kilnnum'";
$x = mysql_query($sql2);
$rows = mysql_num_rows($x);

for ($j = 0 ; $j < 4 ; ++$j) //increment through 0-3 first 4 rows of data
{
$item_no = mysql_result($x,$j, 'PartNo'); //get PartNo from T_Accessories
$sql3 = "SELECT ItemNo,WebDesc FROM T_AdditionalInfo WHERE ItemNo='$item_no'"; //Pull data from T_AdditionalInfo for above PartNo/ItemNo
$y = mysql_query($sql3);

$title_w_spaces = mysql_result($x,$j, 'Description'); //Still pulling title from T_Accessories to match image names
$title = str_replace(" ", "-", $title_w_spaces); //Still using title for heading from T_Accessories
$summary = mysql_result($y,$j, 'WebDesc'); //Pulling description from T_AdditionalInfo
if ($title <> "") {
$table_data .= "
<div>
<h6> $title_w_spaces </h6>
<img src='/images/" . $title . ".jpg' alt='" . $title ."' title='" . $title . "' class='alignnone size-full' width='114' />
<p>" . $summary . "</p>
</div>";
} //end if
} //end for

最佳答案

正如其他人所建议的那样,我还建议将多个选择合并到一个 JOIN 语句中。正如其他人也说过的那样,我建议使用 mysqliPDO 。这两者都将有效地帮助消除 SQL 注入(inject),并防止您使用已已弃用且将在下一版本中删除的代码。

这里是一个关于如何重写查询和循环逻辑的示例(示例使用 PDO,因为自从我使用 mysqli 以来已经有一段时间了)。我想我可能错过了部分连接,因为我不能 100% 确定表之间的关联。

$sql = "
SELECT
Description,
WebDesc
FROM T_AdditionalInfo info JOIN T_Accessories acc ON info.ItemNo = acc.PartNo
WHERE info.Description LIKE :title
";
$db = false;
$table_date = '';
try {
$db = new PDO(...);
}
catch(PDOException $e) {
$db = false
//Failed to connect to DB for some reason
}
if($db !== false) { //Did we connect?
if(($stmt = $db->prepare($sql)) !== false) { //Prepare the statement
$stmt->bindParam(':title', '%abc%'); //Bind the parameter
if($stmt->execute() !== false) { //Execute the statement
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { //Loop through the result set
$table_w_spaces = $row['Description']; //Get title
$title = str_replace(' ', '-', $title_w_spaces); //alter title
$table_date .= "
<div>
<h6>{$table_w_spaces}</h6>
<img src="/images/{$title}.jpg" alt="{$title}" title="{$title}" class="alignnone size-full" width="114" />
<p>{$row['WebDesc']}</p>
</div>
";
}
}
else {
//Execution of statement failed
print_r($stmt->errorInfo());
}
}
else {
//An error occurred when preparing SQL statement, probably a SQL error
print_r($stmt->errorInfo());
}
}

这可能是我有点超前了,但我猜你可能会经常做这样的事情。一旦您熟悉了 PDO 或 mysqli,创建一个类来消除大量重复性任务可能会很有帮助。此外,如果您应该更改为另一台数据库服务器(从 MySql、PostGre 或 MSSQL),甚至更改您的服务器名称,都可以在一个位置轻松更改。如果下面的示例类一开始看起来有点令人困惑,请不要担心。看看下面的例子,你应该就清楚了。它基本上是一种消除所有重复任务并将它们集成到两个特定函数 executeQueryexecuteNonQuery 中的方法。

class Database {
private $db = false;
private $connected = false;
public function __construct($db = 'DefaultDBName', $username = 'MyUser', $password = 'MyPassword') {
$this->connect($db, $username, $password); //Auto connect when you create a new database
}
/**
* Connect to a database
*
* @param $db The database to connect to; default is DefaultDBName
* @param $username The username to connect as; default is MyUser
* @param $password The password to use when connecting; default is MyPassword
* @return True/False if the connection was successfull
*/
public function connect($db = 'DefaultDBName', $username = 'MyUser', $password = 'MyPassword') {
try {
$this->db = new PDO('drivername:Server=MYDBSERVER;Database=' . $db, $username, $password); //Create new connection
$this->connected = true; //We are connected
}
catch(PDOException $e) { //Oh no. An error
$this->db = false; //Set DB back to false, just in case
$this->connected = false; //We couldn't connect
//You can do something with the error message if you wish
}
return $this->connected
}
/**
* Prepares a SQL statement for execution
*
* @param $sql The SQL string to be prepared
* @params $params An optional array of parameters to bind to the statement
* @return The prepared statement; false if failed
*/
public function prepare($sql, $params = array()) {
$stmt = false;
if($this->connected) { //Are we connected
if(($stmt = $this->db->prepare($sql)) !== false) { //Prepare the statement
foreach($params as $param => $value) { //Bind the parameters
$stmt->bindValue($param, $value);
}
}
else {
//You can do something with $stmt->errorInfo();
}
}
return $stmt;
}
/**
* Execute a prepared statement
*
* @param $stmt A PDO statement
* @param $params An optional array of parameter values
* @return True/False
*/
public function execute($stmt, $params = array()) {
if($this->connected) { //Are we connected
if(!empty($params)) { //Can't bind an empty array of parameters
$result = $stmt->execute($params); //Execute with parameters
}
else {
$result = $stmt->execute(); //Execute without parameters
}
}
return $result;
}
/**
* Gets the results from an executed statement
*
* @param $stmt A reference to a PDO statement
* @return An array of results from the statement
*/
public function getResults($stmt) {
$results = array();
if($stmt !== false) { //Make sure we have a valid statement
while($row = $stmt->fetch(PDO::FETCH_ASSOC))) { //Get all of the data for the row
$results[] = $row; //Add the row to the results array
}
}
return $results; //Return the results
}
/**
* Executes a query and returns the results
*
* @param $sql The SQL query
* @param $parameters An array of parameters
* @return An array of results or false if execution failed
*/
public function executeQuery($sql, $params = array()) {
$results = false;
if($this->connected) { //Are we connected
if(($stmt = $this->prepare($sql, $params)) !== false) { //Prepare the statement
if($this->execute($stmt) !== false) { //Execute the statement
$results = $this->getResults($stmt); //Get the result set
}
}
}
return $results;
}
/**
* Executes a query, but returns no results
*
* @param $sql The SQL query
* @param $parameters An optional array of paraters
* @return True/False
*/
public function executeNonQuery($sql, $params = array()) {
$success = false;
if($this->connected) { //Are we connected
if(($stmt = $this->prepare($sql, $params)) !== false) { //Prepare the statement
if($this->execute($stmt) !== false) { //Execute the statement
$success = true; //Successfully executed
}
}
}
return $success;
}
}

示例

在每个示例中,假设以下内容

$sql = "
SELECT
Description,
WebDesc
FROM T_AdditionalInfo info JOIN T_Accessories acc ON info.ItemNo = acc.PartNo
WHERE info.Description LIKE :title
";
$parameters = array(':title' => '%abc%');

1)使用新的数据库类将它们放在一起

$db = new Database();
if(($stmt = $db->prepare($sql, $parameters)) !== false) {
if($db->execute($stmt)) {
$results = $db->getResults($stmt);
foreach($results as $result) {
//Do something with this result
}
}
}

2) 现在我知道我说过,通过消除准备语句、绑定(bind)参数、的所有重复性,创建此类将使一切变得更容易执行语句,然后检索结果集。下面是一个快速示例,说明如何在一行中完成上述所有操作。

$db = new Database();
if(($results = $db->executeQuery($sql, $parameters)) !== false) {
foreach($results as $result) {
//Do something with this result
}
}

3) 但是我想要使用的不返回结果集的查询怎么办?我仍然可以快速执行语句而不造成困惑吗?是的。您可以使用executeNonQuery函数。下面是一个例子。

$db = new Database();
$sql = 'update table1 set field = :value where id = :id';
$parameters = array(':value' => 'val1', ':id' => '5');
if($db->executeNonQuery($sql, $parameters)) {
//Yay! The update was successfull
}

更新

根据我与 OP 的对话(请参阅评论),这是他的查询的更新版本,应该可以获得所需的结果。查询的 LIMIT 1 部分确保仅使用一个 ItemNo,这应该只给出正在查找的 8 个配件,而不是全部 32 个(8 个配件 X 4 个 ItemNo)。

SELECT 
info.Description,
acc.WebDesc
FROM T_AdditionalInfo info JOIN T_Accessories acc ON info.ItemNo = acc.PartNo
WHERE info.ItemNo = (SELECT ItemNo FROM T_AdditionalInfo WHERE Description LIKE %abc% LIMIT 1)

嵌套的 select 语句称为子查询。由于您表明您正在使用 MySQL,因此这里是 reference page您可能会发现有用。子查询在 SQL 中对于此类操作以及使用 IN 子句 SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2) 非常有用。

关于PHP 多重查询问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17241330/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com