MySQL Table Join Data Display in PHP File

CPJ18 · Oct 9, 2013

I have 2 MySQL tables: Books and Reviews. The ISBN is the unique identifier. I need to display ALL reviews for a specific Book Title. My problem is that when a book title is clicked on the next page shows the Book information and the first record in the Reviews table. This happens for each Book Title. Only the first record in the Reviews table is displaying instead of the reviews for a specific title based on the ISBN. Here is my code:

<!DOCTYPE html>
<head>
<title>PHP Single Item DB data display</title>
</head>

<body>

<?php
$conn = new mysqli("localhost", "it6203", "it6203", "textbooks");
//host, user, password, database
if (mysqli_connect_errno()){
echo 'Cannot connect to database: ' .
mysqli_connect_error($conn);
}
else{

$query = "SELECT * from books a join reviews b where b.ISBN and a.ISBN='".$_GET["bookid"]."'";
$result = mysqli_query($conn, $query);

if (!$result) {
die("Invalid query: " . mysqli_error($conn));
}
else {

$info="";
$hastitle=false;
while($row = mysqli_fetch_array($result))

{
if (!$hastitle){

$info.="<h1>{$row['BookTitle']}</h1>";
$info.="ISBN: {$row['ISBN']}";
$info.="Author(s): {$row['Authors']}";
$info.="Price: {$row['ListPrice']}";
$info.="Publish Date: {$row['PubDate']}";
$info.="Publisher: {$row['Publisher']}";

$hastitle=true;
$info.="Review: {$row['Content']}";
}

}
echo $info;

mysqli_free_result($result);

}

mysqli_close($conn);
}

?>

</body>
</html>

Can anyone help me? Thank you in advance.

ChrisHirst · Oct 9, 2013

".$_GET["bookid"]."'";

First off, NEVER, EVER under ANY circumstance EVER use a $_GET or $_POST value directly in a database query.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

vacunita · Oct 16, 2013

First, I cannot stress enough what Chris points out.

ChrisHirst said:
NEVER, EVER under ANY circumstance EVER use a $_GET or $_POST value directly in a database query.

You are just asking for trouble if you do that. Because you allow anybody to just modify the ISBN value that is being sent to do some really nasty things to your DB.

Research msyql injection for specifics.

Without knowing the structure of your Database its hard to offer any specific help.

You need to craft your query so it brings back all the reviews for the ISBN. Not even sure why you are using a join there. What information do you need from the book to show the reviews?

I'd just directly get reviews from the review table.

Something like:

PHP:

$cleanISBN = mysql_real_escape_string(trim($_GET['bookid']));

Code:

$query="SELECT * from reviews reviews.ISBN = " . $cleanISBN;

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

jpadie · Nov 13, 2013

ylyou don't want a join here. this is a two query situation as you want all records in reviews for a given book ISBN. so you have no need to join the two tables.

since each book is only in the book table once, with the join you are using, you get only one row back from the reviews table. if you want the book name in each row recast the query to search reviews and just include the boon title from the joined table

Code:

select r.*, b.booktitle 
from reviews r
join books b
on b.ISBN =r.ISBN
where r.ISBN 'some value'

but I can't see the point as to have the ISBN by this point you will have looked up the book id. so my advice is to do two separate queries. this will be faster for the engine than computing the join in any event I'd guess.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

MySQL Table Join Data Display in PHP File

CPJ18

Programmer

ChrisHirst

IS-IT--Management

vacunita

Programmer

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor