Jump to content

query 2 tables and filter results by $_GET variable - I'm stuck (Dreamweaver)


leonthelion

Recommended Posts

I need to pull data from two tables, one with book details, the other with review information. Linked together by ISBN numbers in both tables. Currently its giving me all matching rows, however, I want to pass an ISBN number from URL?isbn=1234567890123 and only show all reviews matching that ISBN.

I can't add the extra code to use $_GET without it crashing:

[code]<?php
mysql_select_db($database_ebc, $ebc);
$query_Recordset1 = "SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books, ebc_book_reviews WHERE ebc_books.isbn = ebc_book_reviews.isbn ";
$Recordset1 = mysql_query($query_Recordset1, $ebc) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS" />
<title>Untitled Document</title>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="5">
  <tr>
    <td>isbn</td>
    <td>title</td>
    <td>reviewer</td>
    <td>reviewer country </td>
    <td>review</td>
    <td>rating</td>
    <td>visible</td>
  </tr>

    <?php do { ?>
  <tr>
      <td><?php echo $row_Recordset1['isbn']; ?></td>
      <td><?php echo $row_Recordset1['title']; ?></td>
      <td><?php echo $row_Recordset1['reviewer']; ?></td>
      <td>&nbsp;</td>
      <td><?php echo $row_Recordset1['review']; ?></td>
      <td><?php echo $row_Recordset1['rating']; ?></td>
      <td><?php echo $row_Recordset1['visible']; ?></td>
      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?></tr>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
[/code]

I'm in Dreamweaver and know just enough PHP to get myself in trouble...
Link to comment
Share on other sites

sorry, no good yet...

with no variable passed, it now shows nothing, or with a variable, it shows all matching rows, not just the ones passed by the query...

example is at http://www.experiencebaja.com/reviews.php?isbn=131166 with a valid isbn code for testing, if you try without you'll see nothing show up...

I'll include the full code in case Dreamweaver is the culprit, but it's likely to be me :P

[code]<?php require_once('dbconnection/ebc.php'); ?>
<?php
mysql_select_db($database_ebc, $ebc);
$isbn = $_GET['isbn'];
echo $isbn; //for testing
$query_Recordset1 = "SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books, ebc_book_reviews WHERE ebc_books.isbn = '$isbn' ";
$Recordset1 = mysql_query($query_Recordset1, $ebc) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS" />
<title>Untitled Document</title>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="5">
  <tr>
    <td>isbn</td>
    <td>title</td>
    <td>reviewer</td>
    <td>reviewer country </td>
    <td>review</td>
    <td>rating</td>
    <td>visible</td>
  </tr>

    <?php do { ?>
  <tr>
      <td><?php echo $row_Recordset1['isbn']; ?></td>
      <td><?php echo $row_Recordset1['title']; ?></td>
      <td><?php echo $row_Recordset1['reviewer']; ?></td>
      <td>&nbsp;</td>
      <td><?php echo $row_Recordset1['review']; ?></td>
      <td><?php echo $row_Recordset1['rating']; ?></td>
      <td><?php echo $row_Recordset1['visible']; ?></td>
      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?></tr>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
[/code]
Link to comment
Share on other sites

1)
you got your brain on
A good idea when building scripts is to echo things to see what is going on

b) Are you going to trust the website user and not do anything to the _GET["isbn"]; this is very risky

Third)
change
[code]
    <?php do { ?>
  <tr>
      <td><?php echo $row_Recordset1['isbn']; ?></td>
      <td><?php echo $row_Recordset1['title']; ?></td>
      <td><?php echo $row_Recordset1['reviewer']; ?></td>
      <td>&nbsp;</td>
      <td><?php echo $row_Recordset1['review']; ?></td>
      <td><?php echo $row_Recordset1['rating']; ?></td>
      <td><?php echo $row_Recordset1['visible']; ?></td>
      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?></tr>
[/code]

to
[code]
<?php
while($row_RecordSet1 = mysql_fetch_assoc($Recordset1)){
  echo "<tr>\n"
      ."<td>".$row_Recordset1['isbn']."</td>\n"
      ."<td>".$row_Recordset1['title']."</td>\n"
      ."<td>".$row_Recordset1['reviewer']."</td>\n"
      ."<td>&nbsp;</td>\n"
      ."<td>".$row_Recordset1['review']."</td>\n"
      ."<td>".$row_Recordset1['rating']."</td>\n"
      ."<td>".$row_Recordset1['visible']."</td>\n"
."</tr>\n";

}

?>[/code]
Link to comment
Share on other sites

Thanks onlyican,

that got the data outputting to the screen, but still not quite right, no matter which isbn is passed, eg:

http://www.experiencebaja.com/reviews.php?isbn=546546

I'm getting 3 rows of the same results, and the isbn returned from the tables isn't matching what comes from the $_GET, as you can check with the echo statement.

here is the code again:

[code]<?php require_once('dbconnection/ebc.php'); ?>
<?php
mysql_select_db($database_ebc, $ebc);
$isbn = $_GET['isbn'];
echo $isbn; //for testing
$query_Recordset1 = "SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books, ebc_book_reviews WHERE ebc_books.isbn = '$isbn' ";
$Recordset1 = mysql_query($query_Recordset1, $ebc) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
//$totalRows_Recordset1 = mysql_num_rows($Recordset1); don't need on this page
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS" />
<title>reviews.php - almost working</title>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="5">
  <tr>
    <td>isbn</td>
    <td>title</td>
    <td>reviewer</td>
    <td>reviewer country </td>
    <td>review</td>
    <td>rating</td>
    <td>visible</td>
  </tr>

  <?php
while($row_RecordSet1 = mysql_fetch_assoc($Recordset1)){
  echo "<tr>\n"
      ."<td>".$row_Recordset1['isbn']."</td>\n"
      ."<td>".$row_Recordset1['title']."</td>\n"
      ."<td>".$row_Recordset1['reviewer']."</td>\n"
      ."<td>&nbsp;</td>\n"
      ."<td>".$row_Recordset1['review']."</td>\n"
      ."<td>".$row_Recordset1['rating']."</td>\n"
      ."<td>".$row_Recordset1['visible']."</td>\n"
."</tr>\n";

}

?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>[/code]

Also, you mentioned I should be doing something to the $_GET variable when it comes in, is it a security concern?
Link to comment
Share on other sites

[quote]
Also, you mentioned I should be doing something to the $_GET variable when it comes in, is it a security concern?
[/quote]

One Word: Sql Injection

As for the query, very strange
add this somewhere

echo $query_Recordset1;

This is just to check everything is working on the query as should be
Link to comment
Share on other sites

I'll try and fix the security hole once it is up and running....

I'm getting mildly closer and distant at the same time. There is one more echo command to try and help...

if you think this may be better?

[code]<?php require_once('dbconnections/ebc.php'); ?>
<?php
mysql_select_db($database_ebc, $ebc);
$isbn = $_GET['isbn'];
echo $isbn; //for testing
$query_Recordset1 = "SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books LEFT JOIN ebc_book_reviews ON ebc_books.isbn=ebc_book_reviews.isbn WHERE ebc_books.isbn = '$isbn'";
$Recordset1 = mysql_query($query_Recordset1, $ebc) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS" />
<title>reviews.php - almost working</title>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="5">
  <tr>
    <td>isbn</td>
    <td>title</td>
    <td>reviewer</td>
    <td>reviewer country </td>
    <td>review</td>
    <td>rating</td>
    <td>visible</td>
  </tr>

  <?php
while($row_RecordSet1 = mysql_fetch_assoc($Recordset1)){
  echo "<tr>\n"
      ."<td>".$row_Recordset1['isbn']."</td>\n"
      ."<td>".$row_Recordset1['title']."</td>\n"
      ."<td>".$row_Recordset1['reviewer']."</td>\n"
      ."<td>&nbsp;</td>\n"
      ."<td>".$row_Recordset1['review']."</td>\n"
      ."<td>".$row_Recordset1['rating']."</td>\n"
      ."<td>".$row_Recordset1['visible']."</td>\n"
."</tr>\n";

}

?>
</table>
<p>

</p>
</body>
</html>
<?php
echo $query_Recordset1; //testing
mysql_free_result($Recordset1);
?>[/code]
Link to comment
Share on other sites

Hmm.. I went to check http://www.experiencebaja.com/reviews.php?isbn=131166
I saw

isbn  title  reviewer  reviewer country  review  rating  visible
131166 pizza jon   Y

I assume its working fine, now you need to check if it cannot find any query then it will echo out something like "No result found" etcs..

As for the security problem, if you don't know how to fix it, you can try to search around in this forums.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.