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:

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

8 Replies

JayefuuBest Answer (author)2011-02-09

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.

Select as Best AnswerUndo Best Answer

Jayefuu (author)Jayefuu2011-02-09

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.

Select as Best AnswerUndo Best Answer

Jayefuu (author)Jayefuu2011-02-09

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


Select as Best AnswerUndo Best Answer

bobbywhite (author)2013-04-09

Make it with sql server in recovery ?

http://www.repairsql.recoverytoolbox.com open, display, read and even restore sql database files

Select as Best AnswerUndo Best Answer

DavezDesignz (author)2011-02-04

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

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


Select as Best AnswerUndo Best Answer


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.

Select as Best AnswerUndo Best Answer


I've tried that but only one row appears, so how do I loop it so it displays all of the rows with a specific category?

Select as Best AnswerUndo Best Answer

sirkut (author)2011-02-04

Change everywhere you search for id with category.

Select as Best AnswerUndo Best Answer