Conditional SQL Queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stragglerat
Forum Newbie
Posts: 7
Joined: Wed May 14, 2008 12:00 am

Conditional SQL Queries

Post by stragglerat »

I'm using PHP to pull items from a SQL DB. Is there a way to make one distinct item page and have a PHP script plug in an individual field entry to query just that item? That way a user can choose an item from a list, click "Details" and see a page with just that item. I know, this is confusing. I'm just trying to keep from having to make a page for every item I have. Any ideas?
dbemowsk
Forum Commoner
Posts: 82
Joined: Wed May 14, 2008 10:30 pm

Re: Conditional SQL Queries

Post by dbemowsk »

First off, you will need to do some reading on SQL queries. You would use a query similar to "SELECT * FROM {your_items_table} WHERE item_id = $item_id". This will get the data that you need for the item. I would then also look into using a template setup with PHP. With templates, you can keep a uniformed look to the page and just change the necessary data for the item. That way you are not creating separate pages for each item. I have a short tutorial on using templates on my site called "Creating and using templates with PHP".

This is one of the nice things about dynamic web pages.
Last edited by dbemowsk on Sat May 17, 2008 10:58 pm, edited 1 time in total.
nowaydown1
Forum Contributor
Posts: 169
Joined: Sun Apr 27, 2008 1:22 am

Re: Conditional SQL Queries

Post by nowaydown1 »

I might be off the mark with this so please forgive me if I completely missed what you're trying to accomplish. So it sounds like you have a list of products or something displayed in a dropdown with a 'Details' button next to the dropdown. You could have the value column of your select options set to the recordID of the item you want to display in the database. For example, let's say you had a list of cars and wanted to display details about them.

For example purposes lets say our schema is really simple. The table in our example database is called 'cars'. It has three columns, carID, name, description.

So our dropdown box code would probably go something like this:

Code: Select all

 
 
<form name="carForm" action="display_details.php" method="post">
<?php
$sqlQuery = "SELECT carID, name FROM cars ORDER BY name";
if($result = mysql_query($sqlQuery)) {
    echo "<select name=\"carSelection\">";
    while($row = mysql_fetch_array($result)) {
         printf("<option value=\"%s\">%s</option>", $row["carID"], $row["name"]);
    }
    echo "</select>";
    echo "<input type=\"submit\" name=\"showDetailsButton\" value=\"Show Details\" />";
} else {
    echo "An error occured: " . mysql_error();
?>
</form>
 
Then in another php file, such as display_details.php or something, you would have the code that displays details for that one car:

Code: Select all

 
<?php
if(isset($_POST["showDetailsButton"])) {
    $carID = $_POST['carSelection'];
    $sqlQuery = "SELECT name, details FROM cars WHERE carID = '$carID' LIMIT 1";
    $result = mysql_query($sqlQuery) or die(mysql_error());
    $row = mysql_fetch_array($result);
?>
 
<h2><?php echo $row["name"]; ?></h2>
 
<p>Details about this car:  <?php echo $row["details"]; ?></p>
    
<?php
}
?>
 
That's a really rough example that probably has syntax problems in it (I typed it without testing anything) but hopefully conveys the idea. If you were going to build something along the those same lines in a production environment, make sure you clean your $_POST data. Hope that helps.
stragglerat
Forum Newbie
Posts: 7
Joined: Wed May 14, 2008 12:00 am

Re: Conditional SQL Queries

Post by stragglerat »

Actually, you were right on with your solution, although a little late ;). What I have is a page that displays all my results in a nice little table. Under each result is a details button which I made into a submit button that POSTs to details.php. I made a template page named details.php and used the POSTed value as a variable in the SQL query. Here is the query code:

$query = "SELECT * FROM wwjitems WHERE item = '".$_POST["0"]."'";

and here is the form button from the main table page:

<form action='details.php' method='post'><input type='image' src='images/button_details.gif' name='0' value='".$row[0]."'></form> -where $row[0] is the item number

worked beautifully, although I have one reservation about using this method. If a customer wants to bookmark a particular item, when they come back to details.php they get the blank template page. Do you know of any way around this?
Post Reply