Page 1 of 1

Conditional SQL Queries

Posted: Sat May 17, 2008 3:39 am
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?

Re: Conditional SQL Queries

Posted: Sat May 17, 2008 11:51 am
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.

Re: Conditional SQL Queries

Posted: Sat May 17, 2008 11:56 am
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.

Re: Conditional SQL Queries

Posted: Sat May 17, 2008 4:15 pm
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?