loading

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

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.

sort by: active | newest | oldest
Jayefuu6 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.

Jayefuu Jayefuu6 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
Jayefuu Jayefuu6 years ago
Oops that's not very helpful. Here's a link to the large version:

https://www.instructables.com/file/FVKAEV6GJQEJXOD/?size=LARGE
bobbywhite4 years ago
Make it with sql server in recovery ?

http://www.repairsql.recoverytoolbox.com open, display, read and even restore sql database files
should this:
mysql_select_db ( $database, $connect );

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

?
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.
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?
sirkut6 years ago
Change everywhere you search for id with category.