Page 1 of 1

Displaying Query Results

Posted: Tue Jul 04, 2006 6:38 pm
by mcox05
I have another question for all you PHP masters! Now that I can display all the information about a particular query from a database, I want to know the more commonly used technique of listing items on a page that link to a dynamic query which is displayed by a php page. If that was hard to follow then it will make more sense in a second.

Think about online shopping places. They have picture links or texts links that, when clicked on, load a dynamic php page that queried for particular results based on the link that was clicked.

So I want to make a list of links like so,

HTML Code:
<html>
<body>
<br><a href="">Earring-RA-01</a>
<br><a href="">Earring-RA-02</a>
<br><a href="">Earring-RA-03</a>
<br><a href="">Earring-RA-04</a>
<br><a href="">Earring-RA-05</a>
</body>
</html>

Now each one of these links will link to a product.php info page that queries the database for information specific to the link clicked.
How do I do this...? NOTE: The linkage part is the ONLY thing I'm concerned with, I already know how to actually display the the data once I have it.

P.s the queries will look like this for me

Code: Select all

$sql = "SELECT * FROM `Earrings` WHERE `Product_Name` LIKE 'Earring-RA-01' LIMIT 0 , 30";
$sql = "SELECT * FROM `Earrings` WHERE `Product_Name` LIKE 'Earring-RA-02' LIMIT 0 , 30";
$sql = "SELECT * FROM `Earrings` WHERE `Product_Name` LIKE 'Earring-RA-03' LIMIT 0 , 30";
$sql = "SELECT * FROM `Earrings` WHERE `Product_Name` LIKE 'Earring-RA-04' LIMIT 0 , 30";
$sql = "SELECT * FROM `Earrings` WHERE `Product_Name` LIKE 'Earring-RA-05' LIMIT 0 , 30";

Posted: Tue Jul 04, 2006 8:08 pm
by bdlang
Generally speaking, you link the ID in the table storing the data to the URL, e.g.

Code: Select all

<a href="http://example.com/products.php?id=4">Product # 4</a>
Your receiving script is named products.php, and the GET string contains one variable, id with the value 4.

Your database table contains product data linked with a PRIMARY KEY INT value, something like this

Code: Select all

-- untested
--
CREATE TABLE IF NOT EXISTS `products` (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
product VARCHAR(50) NOT NULL UNIQUE,
price FLOAT(5,2) NULL DEFAULT 0.00,
);
(untested)

Code: Select all

<?php
// products.php

// assumes MySQL, replace with your database functions

// some basic logic makes certain the id value was passed in the GET string
if ( isset($_GET['id']) && ( ctype_digit($_GET['id']) !== FALSE ) ) {
    // assign the $ID value to be used in the query
    $ID = intval($_GET['id']);
} else {
    // set to a default product, the first one in the database
    $ID= 1;
}

// SQL statement using the PK value to find the record
$sql= 'SELECT `product`, `price` FROM `products_table` WHERE `ID` = ' .$ID;
// the actual query
$res= mysql_query($sql) OR die(mysql_error());

// make certain there was a product record returned
if ( mysql_num_rows($res) == 1 ) {
    // retrieve and display the product information
    $product_info= mysql_fetch_assoc($res);
    echo $product_info['product'] .' $' . round($product_info['price'],2);
} else {
    // no record retrieved with that ID value
    echo 'No such product...';
}
?>
So you see the PHP script retrieves data based on the PK value, and it's already indexed and ready for an efficient query. Having to use LIKE on every query to the database on a non-indexed text value like 'Earring-RA-05' is not desirable.'

When you create the links, it's very simple to run through a loop like this to grab the values and a description, e.g.
(untested)

Code: Select all

<?php
// index.php

$sql= 'SELECT `ID`, `product` FROM `products` ORDER BY `product`';
$res= mysql_query($sql) OR die(mysql_error());
while ( $row= mysql_fetch_assoc($res) ) {
    echo '<a href="http://example.com/products.php?id=' .$row['ID']. '">' .$row['product']. '</a><br />';
}
?>
All the links will be automatically created with the `products` table PK and a description.

Posted: Tue Jul 04, 2006 8:20 pm
by RobertGonzalez
Very well explained. The only thing I would add is, when you know you will have a default value of something, set it first, and change it if something else works better. So in bdlang's code, I would change this...

Code: Select all

<?php
if ( isset($_GET['id']) && ( ctype_digit($_GET['id']) !== FALSE ) ) {
    // assign the $ID value to be used in the query
    $ID = intval($_GET['id']);
} else {
    // set to a default product, the first one in the database
    $ID= 1;
} 
?>
to this..

Code: Select all

<?php
// Set s default value
$ID = 1;

// Now if we need to change it, do so
if ( isset($_GET['id']) && ( ctype_digit($_GET['id']) !== FALSE ) ) {
    $ID = intval($_GET['id']);
}
?>
Otherwise, that was a very nice explanation of what to do.

Posted: Tue Jul 04, 2006 8:27 pm
by bdlang
Thanks, yeah my else {} block takes care of that, but your way is probably better. ;)

Posted: Wed Jul 05, 2006 1:07 am
by RobertGonzalez
Don't know if it is better or not, but I am of the mind that if there is a default, set the default. If it needs to be changed, change it if it meets the appropriate criteria. I try not to put defaults into an else. That is just my coding style. Not necesarily better or worse, just a different logic.