CODEWITHSUNDEEP
phpmysqlsqljoin
jemtan990
jemtan990

Reputation: 453

How can I join three tables using MYSQLi?

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:

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]

Upvotes: 1

Views: 4110

Answers (3)

Sumit Gupta
Sumit Gupta

Reputation: 2192

Try 

$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 '" . $title . "'";

WHERE in SQL should come after JOIN statement.

EDIT: I edit to suggest that this is not SECURE way of doing it, and since the topic is related to only suggest error in your SQL I follow what you write. But please read about SQL injection and update your code for it.

Upvotes: 3

Marcel Gwerder
Marcel Gwerder

Reputation: 8520

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();

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

Upvotes: 2

Eric
Eric

Reputation: 24904

The basic join format for MySQL is:

select ... from A
join B1 on ...
join B2 on ...
...
where ...

So, put your where after join statement, and try again.

Upvotes: 1

Related Questions