CODEWITHSUNDEEP
phpmysql
user1393064
user1393064

Reputation: 411

Retrieving data from database from PHP

Trying to retrieve related data from the relationships table, after user clicks on the the bookname on the previous page. Nothing is being printed on the page even though in the database there is data.

The table schema is:

relationshipID,bookone,booktwo,relation,relationlike,relationdislike

<html>
    <head>
    <title>Retrieve Relationships</title>
    </head>
    <body>

    <dl>

    <?php
    // Connect to database server
    mysql_connect("latcs7.cs.latrobe.edu.au","12ice06","EsnYkuxuwh9RbtQuRcQt") or die (mysql_error ());

    // Select database
    mysql_select_db("12ice06") or die(mysql_error());
        $sTitle=0;
    // Get data from the database depending on the value of the id in the URL
    $title = (isset($_GET['title']) && is_string($_GET['title'])) ? $_GET['title'] : null;
$sTitle = mysql_real_escape_string($title);
$strSQL = "SELECT R.bookone, B.title, B.author,
        R.booktwo, B.title, B.author,
        R.relation, R.relationlike, R.relationdislike 
        FROM relationships R
        INNER JOIN books B ON R.bookone = B.bookid";     

$rs = mysql_query($strSQL) or die(mysql_error());
    // Loop the recordset $rs

while($row = mysql_fetch_array($rs)){
    // Write the data of the book


    echo "<dt>Book One:</dt><dd>" . $row["bookone"] . "</dd>";
    echo "<dt>Title:</dt><dd>" . $row["title"] . "</dd>";
    echo "<dt>Author:</dt><dd>" . $row["author"] . "</dd>";
    echo "<dt>Book Two:</dt><dd>" . $row["booktwo"] . "</dd>";
        echo "<dt>Title:</dt><dd>" . $row["title"] . "</dd>";
    echo "<dt>Author:</dt><dd>" . $row["author"] . "</dd>";
    echo "<dt>Relationship:</dt><dd>" . $row["relation"] . "</dd>";
    echo "<dt>Likes:</dt><dd>" . $row["relationshiplikes"] . "</dd>";
    echo "<dt>Dislikes:</dt><dd>" . $row["relationshipdislikes"] . "</dd>";
}

echo $strSQL;
    // Close the database connection
    mysql_close();
    ?>

    </dl>
    <p><a href="search_bookl.php">Return to the list</a></p>

    </body>

    </html>

Upvotes: 0

Views: 764

Answers (2)

Bjoern
Bjoern

Reputation: 16304

My advice:

a) Please reform the variable in your SQL-Statement like this:

$strSQL = "SELECT [...]
           WHERE books.bookid=relationships.bookone 
               AND relationships.bookone='".$sTitle."'";

b) Make sure your variable $sTitle isn't empty. If it still is an issue, echo the whole SQL-Statement (echo $strSQL;) for further debugging.

c) While on it, please reform the whole SQL statement. The following statement does the same and is much more readable:

$strSQL = "SELECT R.bookone, R.booktwo, 
              R.relation,
              R.relationlike, R.relationdislike
           FROM relationships R
           INNER JOIN books B ON R.bookone = B.bookid
           WHERE R.bookone='".$sTitle."'";

d) Instead of outputting the data after if($row = mysql_fetch_array($rs)){, use a while-statement instead, something like

while ($row = mysql_fetch_array($rs)) {
    // Write the data of the book
    // Insert your echos here
}

e) Question: Is there a specific reason table books is INNER JOINed, but not used in a condition?

Upvotes: 0

xdazz
xdazz

Reputation: 160833

if($row = mysql_fetch_array($rs)){

    // Write the data of the book
    echo "<dt>Book One:</dt><dd>" . $row["bookone"] . "</dd>";
    echo "<dt>Book Two:</dt><dd>" . $row["booktwo"] . "</dd>";
    echo "<dt>Relationship:</dt><dd>" . $row["relation"] . "</dd>";
    echo "<dt>Likes:</dt><dd>" . $row["relationshiplikes"] . "</dd>";
    echo "<dt>Dislikes:</dt><dd>" . $row["relationshipdislikes"] . "</dd>";
}while($row!=0);

Should be 

while($row = mysql_fetch_array($rs)){
    // Write the data of the book
    echo "<dt>Book One:</dt><dd>" . $row["bookone"] . "</dd>";
    echo "<dt>Book Two:</dt><dd>" . $row["booktwo"] . "</dd>";
    echo "<dt>Relationship:</dt><dd>" . $row["relation"] . "</dd>";
    echo "<dt>Likes:</dt><dd>" . $row["relationshiplikes"] . "</dd>";
    echo "<dt>Dislikes:</dt><dd>" . $row["relationshipdislikes"] . "</dd>";
}

Upvotes: 2

Related Questions