Jump to content

Archived

This topic is now archived and is closed to further replies.

leonthelion

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

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...

Share this post


Link to post
Share on other sites
Have you tried ?
[code]<?php
$isbn = $_GET['isbn'];
$query_Recordset1 = "SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books, ebc_book_reviews WHERE ebc_books.isbn = '$isbn' ";
[/code]

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
I meant echo the query, and tell us, see if we can see an error in the MySQL query or something

Share this post


Link to post
Share on other sites
like this result? this is an echo of the query

SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books JOIN ebc_book_reviews ON ebc_books.isbn=ebc_book_reviews.isbn WHERE ebc_books.isbn = '131166'

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
to find out if there are no results use

if(mysql_num_rows($result) == 0){
echo "No Results Found";
}else{
//continue
}

Share this post


Link to post
Share on other sites
remove the first mysql_fetch_assoc right after mysql_query. that one is advancing the row pointer by one.

Share this post


Link to post
Share on other sites

×

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.