leonthelion Posted August 4, 2006 Share Posted August 4, 2006 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]<?phpmysql_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> </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><?phpmysql_free_result($Recordset1);?>[/code]I'm in Dreamweaver and know just enough PHP to get myself in trouble... Quote Link to comment Share on other sites More sharing options...
hackerkts Posted August 4, 2006 Share Posted August 4, 2006 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] Quote Link to comment Share on other sites More sharing options...
leonthelion Posted August 4, 2006 Author Share Posted August 4, 2006 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'); ?><?phpmysql_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> </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><?phpmysql_free_result($Recordset1);?>[/code] Quote Link to comment Share on other sites More sharing options...
onlyican Posted August 4, 2006 Share Posted August 4, 2006 1)you got your brain onA good idea when building scripts is to echo things to see what is going onb) Are you going to trust the website user and not do anything to the _GET["isbn"]; this is very riskyThird) 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> </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]<?phpwhile($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> </td>\n" ."<td>".$row_Recordset1['review']."</td>\n" ."<td>".$row_Recordset1['rating']."</td>\n" ."<td>".$row_Recordset1['visible']."</td>\n"."</tr>\n";}?>[/code] Quote Link to comment Share on other sites More sharing options...
leonthelion Posted August 4, 2006 Author Share Posted August 4, 2006 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=546546I'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'); ?><?phpmysql_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> <?phpwhile($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> </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><?phpmysql_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? Quote Link to comment Share on other sites More sharing options...
onlyican Posted August 4, 2006 Share Posted August 4, 2006 [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 InjectionAs for the query, very strangeadd this somewhereecho $query_Recordset1;This is just to check everything is working on the query as should be Quote Link to comment Share on other sites More sharing options...
leonthelion Posted August 4, 2006 Author Share Posted August 4, 2006 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'); ?><?phpmysql_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> <?phpwhile($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> </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><?phpecho $query_Recordset1; //testingmysql_free_result($Recordset1);?>[/code] Quote Link to comment Share on other sites More sharing options...
onlyican Posted August 4, 2006 Share Posted August 4, 2006 I meant echo the query, and tell us, see if we can see an error in the MySQL query or something Quote Link to comment Share on other sites More sharing options...
leonthelion Posted August 4, 2006 Author Share Posted August 4, 2006 like this result? this is an echo of the querySELECT 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' Quote Link to comment Share on other sites More sharing options...
hackerkts Posted August 5, 2006 Share Posted August 5, 2006 Hmm.. I went to check http://www.experiencebaja.com/reviews.php?isbn=131166I sawisbn title reviewer reviewer country review rating visible131166 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. Quote Link to comment Share on other sites More sharing options...
onlyican Posted August 5, 2006 Share Posted August 5, 2006 to find out if there are no results useif(mysql_num_rows($result) == 0){echo "No Results Found";}else{//continue} Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted August 5, 2006 Share Posted August 5, 2006 remove the first mysql_fetch_assoc right after mysql_query. that one is advancing the row pointer by one. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.