Jump to content


Photo

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


  • Please log in to reply
11 replies to this topic

#1 leonthelion

leonthelion
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 August 2006 - 09:13 AM

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:

<?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);
?>

I'm in Dreamweaver and know just enough PHP to get myself in trouble...

#2 hackerkts

hackerkts
  • Members
  • PipPipPip
  • Advanced Member
  • 593 posts
  • LocationSingapore
  • Age:18

Posted 04 August 2006 - 10:20 AM

Have you tried ?
<?php
$isbn = $_GET['isbn'];
$query_Recordset1 = "SELECT ebc_books.title, ebc_book_reviews.* FROM ebc_books, ebc_book_reviews WHERE ebc_books.isbn = '$isbn' ";


Regards,
hackerkts

To be a coder, you must learn how to think and not to give up so easily.


#3 leonthelion

leonthelion
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 August 2006 - 11:55 AM

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

<?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);
?>


#4 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 04 August 2006 - 12:04 PM

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

to
<?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";

}

?>

Tell me the problem, I will try tell you the solution

#5 leonthelion

leonthelion
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 August 2006 - 12:28 PM

Thanks onlyican,

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

http://www.experienc...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:

<?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);
?>

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

#6 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 04 August 2006 - 12:54 PM

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


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
Tell me the problem, I will try tell you the solution

#7 leonthelion

leonthelion
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 August 2006 - 01:12 PM

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?

<?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);
?>


#8 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 04 August 2006 - 01:21 PM

I meant echo the query, and tell us, see if we can see an error in the MySQL query or something
Tell me the problem, I will try tell you the solution

#9 leonthelion

leonthelion
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 August 2006 - 01:25 PM

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'

#10 hackerkts

hackerkts
  • Members
  • PipPipPip
  • Advanced Member
  • 593 posts
  • LocationSingapore
  • Age:18

Posted 05 August 2006 - 01:33 AM

Hmm.. I went to check http://www.experienc...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.

Regards,
hackerkts

To be a coder, you must learn how to think and not to give up so easily.


#11 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 05 August 2006 - 02:19 AM

to find out if there are no results use

if(mysql_num_rows($result) == 0){
echo "No Results Found";
}else{
//continue
}
Tell me the problem, I will try tell you the solution

#12 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 05 August 2006 - 03:40 AM

remove the first mysql_fetch_assoc right after mysql_query. that one is advancing the row pointer by one.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users