0

I have two tables books and users. books has a column id and users has a column called book_id. users table is populated by info about users who bought books from the store including the column book_id which referees to the id from books table.

My questions is, I have been trying to do this but with no success. 

$query = mysql_query("SELECT * FROM books, users WHERE books.id='$id' OR users.id='$id' ");
while($row = mysql_fetch_array($query)){
    $info .= $row[0] . " " . $row[1] . "<br/>";
}
echo $info;

Table books has some data but users is empty and I am getting nothing as an output!!! What am I missing??

Additional attempts:

SELECT * FROM books, users WHERE books.id='$id' returns empty results

SELECT * FROM books WHERE books.id='$id' returns 5 rows

Khalid
  • 194
  • 1
  • 1
  • 7
  • 2
    Have you echoed out your query to make sure it is correct? Did you run it from the mysql command line to see if it works there? Do you get any error messages? Whjat have you done to debug this? – John Conde Mar 20 '13 at 00:52
  • I add `or die(mysql_error());` at the end of the `query` statement and it didnt complain – Khalid Mar 20 '13 at 00:58
  • What about the other troubleshooting steps i mentioned? – John Conde Mar 20 '13 at 00:58
  • yup tried it as a mysql command and get `MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0006 sec )` – Khalid Mar 20 '13 at 01:01
  • Then your expectations do not reflect the reality that is your database. Start removing parts of your `where` clause and see what happens. – John Conde Mar 20 '13 at 01:03
  • That's my issue. This statement does return 5 rows `SELECT * FROM books WHERE books.id='$id'` but why when I add the OR statement it returns 0. – Khalid Mar 20 '13 at 01:05
  • [Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. – TNK Mar 20 '13 at 02:12
  • Do you need to get all records from two table? – TNK Mar 20 '13 at 02:16
  • yes, both tables are small. one has 3 columns and the other 4 – Khalid Mar 20 '13 at 02:29

1 Answers1

0

You're missing the correct JOIN.

Table books has some data but users is empty and I am getting nothing as an output

Since users table contains no records you need to use LEFT | RIGHT or FULL join to get the result set, depending on what you're trying to accomplish.

Therefore try to change your query to

SELECT * 
  FROM books b LEFT JOIN
       users u ON b.id=u.book_id
 WHERE ...

Here is SQLFiddle

peterm
  • 91,357
  • 15
  • 148
  • 157