Retrieving data from database from PHP

Question

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>

Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi. If you care to learn, here is a quite good PDO-related tutorial. — user1233508
– user1233508, Commented May 22, 2012 at 5:37
unfortunately its the language we were told to use for this assignment. — user1393064
– user1393064, Commented May 22, 2012 at 5:38
Have you tried this query directly in mysql? does it return any rows? — J A
– J A, Commented May 22, 2012 at 5:43
Is there something like if(condition){code}while(condition); for loops in PHP? — Taha Paksu
– Taha Paksu, Commented May 22, 2012 at 5:45

xdazz · Accepted Answer · 2012-05-22 05:46:27Z

2

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>";
}

answered May 22, 2012 at 5:46

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xdazz Over a year ago

Check your sql, do you really mean books.bookid=relationships.bookone AND relationships.bookone='{$sTitle}' ? books.book_id and $sTitle ? they same?

user1393064 Over a year ago

no, bookone is a foreign key of the books table, it references title in the book table. I'm trying to get the book title and author to print with the relationship it has with other books.

user1393064 Over a year ago

my problem is now that its only taking 1 title/author per relationship where I need it to print out bookOnes title and author, then booktwos title and author any ideas?

Bjoern · Accepted Answer · 2012-05-22 06:05:58Z

0

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?

edited May 22, 2012 at 6:05

answered May 22, 2012 at 5:52

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

2 Comments

user1393064 Over a year ago

this is what the debugger printed out 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=''

Bjoern Over a year ago

Then your variable $sTitle is empty. Make sure $_GET['title'] is really set and available.

Collectives™ on Stack Overflow

Retrieving data from database from PHP

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related