INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MySQL Table Join Data Display in PHP File

MySQL Table Join Data Display in PHP File

(OP)
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.="<p>ISBN: {$row['ISBN']}</p>";
$info.="<p>Author(s): {$row['Authors']}</p>";
$info.="<p>Price: {$row['ListPrice']}</p>";
$info.="<p>Publish Date: {$row['PubDate']}</p>";
$info.="<p>Publisher: {$row['Publisher']}</p>";

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







}
echo $info;

mysqli_free_result($result);


}

mysqli_close($conn);
}

?>

</body>
</html>


Can anyone help me? Thank you in advance.

RE: MySQL Table Join Data Display in PHP File

Quote:

".$_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

RE: MySQL Table Join Data Display in PHP File

First, I cannot stress enough what Chris points out.

Quote (ChrisHirst)


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:

CODE --> PHP

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

CODE --> MYSQL

$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

RE: MySQL Table Join Data Display in PHP File

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close