select query not working on linked tables (mysql)_问答_开发者

 $sql=mysql_query("SELECT b.book_id,u.user_id,user_name,book_name,review_date,book_rating,review
                  FROM tbl_books b,tbl_user u,tbl_book_reviews br
                  WHERE b.book_id=br.book_id 
                  AND u.user_id=br.user_id
                  AND moderated='n'
                  ORDER BY review_date ASC
                  LIMIT 1");

I wanted to know if the structure of the select query above is wrong? because it returns empty for tbl_books.book_id and tbl_user.user_id. However i get the correct values for the other fields.

Below is the complete code for moderating book reviews:

    <?php
session_start();

if (!isset($_SESSION['user_id']) || !isset($_SESSION['user_name']) || !isset($_SESSION['password'])) {
    echo "Your session has timed out.";
    exit();}
$errormsg="";
include "scripts/connect_to_mysql.php";
?>
<html>

<head>

<link rel="stylesheet" type="text/css" href="reset.css" media="screen" />
<link rel="stylesheet" type="text/css" href="style1.css" media="screen" />
<title>Moderate Reviews</title>

<body>

<div id="layout_wrapper">
<div id="layout_container">
<div id="layout_content">

    <div id="site_title">
        <h1><a href="home.php"></a></h1>
        <h2></h2>
    </div>

    <?php include "header.php";?>

<?php include "navi_admin.php";?>

        <div class="clearer">&nbsp;</div>

    <div id="main">

        <div class="post">

            <div class="post_top">
                <div class="post_title"><h2>Moderate Reviews</h2></div>
            </div>

            <div class="post_body">



    <?php
            if (isset($_POST['mod_save']))
    {
    $b_id=mysql_real_escape_string($_POST['book']);
    $u_id=mysql_real_escape_string($_POST['user']);
       if ($_POST['mod_action']=="a"){
         $sql=mysql_query("UPDATE tbl_book_reviews SET moderated='a' WHERE moderated='n' WHERE book_id='$bid' AND user_id='$uid'");
         $errormsg="Your moderation action has been successfully saved.  You may now moderate the next review below.";
         }
       else if($_POST['mod_action']=="r")
       {
          $sql=mysql_query("UPDATE tbl_book_reviews SET moderated='r' WHERE book_id='$bid' AND user_id='$uid' AND moderated='n'");
         $errormsg="Your moderation action has been successfully saved.  You may now moderate the next review below.";
       }
       else
       {
         $errormsg="Please select a moderation action.";
       }
    }
?>  
    <?php
 $sql=mysql_query("SELECT b.book_id,u.user_id,user_name,book_name,review_date,book_rating,review
                  FROM tbl_books b,tbl_user u,tbl_book_reviews br
                  WHERE b.book_id=br.book_id 
                  AND u.user_id=br.user_id
                  AND moderated='n'
                  ORDER BY review_date ASC
                  LIMIT 1");
 $Rs=mysql_num_rows($sql);
 if($Rs<1)
     {
        $errormsg="There is no new book reviews that need moderation.";
     }
else
  {
       while ($row=mysql_fetch_array($sql))
       {
       $bid=$row['b.book_id'];
       $bname=$row['book_name'];       
       $review_author=$row["user_name"];
       $uid=$row['u.user_id'];
       $date=$row["review_date"];
       $whenReview= strftime("On %b %d, %Y",strtotime($date));
       $review_body=$row["review"];
       $rating=$row["book_rating"];
  }
  }

    ?>
    <div><i>Breadcrumbs: </i><a href="admincontrol.php">Admin Home</a></div><br/>
    <?php if ($errormsg=="There is no new book reviews that need moderation.")
             {echo $errormsg;
             echo '<br/><br/>';}
          else
          {?>
    <table>
    <tr><td><?php echo $whenReview;?>&nbsp; <?php echo $review_author;?> gave a rating of <?php echo $rating;?> on <?php echo $bname;?> and said:</td></tr>
    <tr><td><?php echo $review_body;?></td></tr>
    <tr><td><form action="" method="POST" name="mod_form">
    Choose your action: <select name="mod_action" id="mod_action">
    <option value="n" selected>Please select</option>
    <option value="a">Accept</option>
    <option value="r">Reject</option>
    </select>
    <input name="book" id="book" value="<?php echo $bid;?>"开发者_StackOverflow/>
    <input type="hidden" name="user" id="user" value="<?php echo $uid;?>"/>
    <input type="submit" name="mod_save" id="mod_save" value="Save Moderation"/>
    </form>
    </td></tr>
    </table>
    <?php }?>
    </div>

        <div class="clearer">&nbsp;</div>


    <?php include "footer.php";?>

</div>
</div>
</div>
</div>
</div>
</body>
</html>

 [/code]

The structure of the query is not wrong. This is wrong:

$bid=$row['b.book_id'];

and this is wrong:

$uid=$row['u.user_id'];

Use

$bid=$row['book_id'];

and

$uid=$row['user_id'];

instead.

On the other hand, the query is not very readable. Use explicit joins instead of implicit ones:

SELECT b.book_id,u.user_id,user_name,book_name,review_date,book_rating,review
FROM tbl_books b
INNER JOIN tbl_book_reviews br ON b.book_id=br.book_id 
INNER JOIN tbl_user u ON u.user_id=br.user_id
WHERE  moderated='n'
ORDER BY review_date ASC
LIMIT 1

This has the advantage that the join condition is close to the table that is joined, instead of in the WHERE clause.