22217Views8Replies

Author Options:

How do I display specific rows from a SQL database using $_GET? Answered

For my latest website, I have created a MySQL database to store user-submitted data. Currently for the homepage a PHP script connects to the database and selects a random row to display on the page.

The table is set out like this:

ID               Quote                 Author                Category

21              blah                    anonymous        Life
22              hdhshs               anonymous        Food


I wanted to create a page where when a user goes to www.example.com/post.php?id=45 , it displays the row with the ID number of 45. That I have done successfully.
But now I want to create a page where if you go to, say: www.example.com/cat.php?catergory=Life, where it lists all rows under Life. But after tweaking the code for the Post page, it doesn't seem to work.

Code for Post Page:

<?php
$user="**********";
$password="**********";$database="**********";
$connection=mysql_connect('**********',$user,$password);@mysql_select_db($database) or die( "Unable to select database");


 $sql = "SELECT * FROM `quotes` WHERE `id` = " . mysql_real_escape_string ( $_GET['id'] );
 mysql_select_db ( $database, $connect ); if ( @mysql_query ( $sql ) )
 { $query = mysql_query ( $sql );
  $row = mysql_fetch_assoc ( $query );
  echo '<p class="confession">' . $row['quote'] . '<p><br /><p>#' . $row['id'] . '</p><br />'; }
 else { die ( mysql_error () );
 }?>

What do I need to change in the above code, to display a list of every row in the database with a category of whats in the URL? 

This is my first time running a MySQL database, so sorry if the code is a bit squiffy.

Discussions

0
None
Jayefuu

Best Answer 7 years ago

Your problem isn't with your sql statement, it's with the way you're trying to display the results with PHP.

You need a while loop in there to loop round and round printing the fetched results until there are none left.

Try this:
$id = $_GET['id'];
$result=mysql_query(SELECT * FROM `quotes` WHERE `id` = $id);
//loops through and prints for each of the rows returned by the query
While($row = mysql_fetch_array($result)) {
   echo '<p class="confession">' . $row['quote'] . '<p><br /><p>#' . $row['id'] . '</p><br />';
}

I can't promise that that'll work straight off, I'm doing it off the top of my head and often get the syntax wrong, but that should get you going.

Reply to my comment if it doesn't work or you want it further explained. Alternatively you can come and find me in the Instructables chatroom most evenings (UK time), or Nachomahma is also usually in there most of the day and is pretty handy with PHP.

0
None
JayefuuJayefuu

Answer 7 years ago

Here's a screenshot of one of the first times I did what you're attempting.

As such it might not be the best example, but it's simple and unobscured by lots of fancy mysql statements. Screenshotted not copied and pasted so it keeps the colour formatting and tabs, probably more useful than just a load of text.

possum_hint.png
0
None
JayefuuJayefuu

Answer 7 years ago

Oops that's not very helpful. Here's a link to the large version:

https://www.instructables.com/file/FVKAEV6GJQEJXOD/?size=LARGE

0
None
DavezDesignz

7 years ago

should this:
mysql_select_db ( $database, $connect );

not be this:
mysql_select_db ( $database, $connection );

?

0
None
DavezDesignzDavezDesignz

Answer 7 years ago

Also this:
$sql = "SELECT * FROM `quotes` WHERE `id` = " . mysql_real_escape_string ( $_GET['id'] );

shoould probably be:
$sql = "SELECT * FROM `quotes` WHERE `category` = '" . mysql_real_escape_string ( $_GET['category'] )."';

if you are trying to search for all the records with the category Life.

0
None
sirkut

7 years ago

Change everywhere you search for id with category.