如何使用MYSQLi连接三个表? [英] How can I join three tables using MYSQLi?

问题描述

我知道之前曾有人问过这个问题，但我希望有人可以在这里帮助我处理我的特定实例(现在是上午5:30，这是明天应交.我在这里是最后一根绳子).这是我的代码:

I know this question has been asked before, but I am hoping someone can help me with my particular instance here (it's 5:30 AM and this is due tomorrow. I'm on my last rope here). Here is my code:

error_reporting(E_ALL);  ini_set('display_errors', 1);

$db = new mysqli('localhost', 'Brendan', 'password', 'Library');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$title = $_GET["title"];

$sql = "SELECT * FROM `BOOK` WHERE `TITLE` like " . $title . " JOIN MANAGES
    ON BOOK.SERIAL_NUM = MANAGES.SERIAL_NUMBER JOIN LIBRARIAN
    ON MANAGES.ID_NUMBER = LIBRARIAN.ID_NUMBER";

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}


while($row = $result->fetch_assoc()){
    echo $row['MANAGES.ID_NUMBER'] . "<br>";
}

当我运行它时，我得到了:

when I run it, I get this:

运行查询时出错[SQL中存在错误 句法;检查与您的MySQL服务器版本相对应的手册 在'JOIN MANAGES ON BOOK.SERIAL_NUM =附近使用正确的语法 第1行的MANAGES.SERIAL_NUMBER']

There was an error running the query [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN MANAGES ON BOOK.SERIAL_NUM = MANAGES.SERIAL_NUMBER' at line 1]

推荐答案

首先是正确的，联接之后必须到达哪里. 另外，请始终确保您保护应用程序免受 sql注入的影响，并使用准备好的语句来绑定参数:

First of all it's correct, that the where has to come after the joins. Additionally always make sure you protect your application from sql injections and use a prepared statement to bind the parameter:

$sql = 'SELECT * FROM `BOOK` JOIN MANAGES
    ON BOOK.SERIAL_NUM = MANAGES.SERIAL_NUMBER JOIN LIBRARIAN
    ON MANAGES.ID_NUMBER = LIBRARIAN.ID_NUMBER 
    WHERE `BOOK`.`TITLE` like ?';

$stmt = $db->prepare($sql);
$stmt->bind_param('s', $title);
$stmt->execute();

$result = $stmt->get_result();

在您的情况下，您直接在用户的SQL查询中包含$title，它由用户定义或可以由用户定义.

In your case you directly include $title which is defined by the user or could be defined by the user in your sql query.

这篇关于如何使用MYSQLi连接三个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！