Database results displaying last id first.

[Love2c0de]

Good evening,

 

I am pulling some data from a database but it seems to be displaying the data with id 2 first, rather than the data with id 1 and then 2 etc.

 

Here is my table setup, I have one table called products which holds some general information and a table called product_images which hold product info along with the image name etc.

 

What's happening is that the bird house data is being displayed before the planters data even though the planters data is the first ID.

 

Here is my code which retrieves all the information:

<?php
//if the code gets this far, we know that the user is viewing the product page and has clicked a link to view. save value to variable.
$product = isset($_GET['order']) ? $_GET['order'] : "";
//connect to server, select db, query table.
$connection = mysql_connect("localhost","root","") or die("Error connecting to server.");
$database = mysql_select_db("gardenable",$connection) or die("Error selecting the database.");

//setup variables ready to be used with the database data.
$count = 0;//used to iterate through $product_array to store results.

//holds the full directory of where this file is located.
$full_dir = dirname(__FILE__);

//holds an array of directories/files found inside the core folder.
$dir_files = scandir($full_dir);

//Searches string starting at end, finding first position of '\' and returns everything to the end 'core'. Replaces any occurences of '\' with an empty string.
$core = str_replace("\\","",(strrchr($full_dir,"\\")));

//holds the index of where the images folder was found, used to securely select the right index as hardcoding will lead to errors as index may change if files/dirs added.
$image_folder = (array_search("product_files",$dir_files)) ? array_search("product_files",$dir_files) : die("Could not locate the images folder.");

//holds the full directory of where the images are stored. Will be used in the 'src' attribute to display images.
$image_directory = $core."/".$dir_files[$image_folder]."/";

$sql = "SELECT P.productID, P.product_name, P.product_details, I.imgID, I.product_price, I.imgName, I.product_dims, I.product_ref, I.product_desc "
     . "FROM products AS P,product_images AS I "
     . "WHERE P.productID = I.productID ";
    
if ($product != "") { $sql .= " AND P.product_name = '{$product}'"; }
$sql .= " ORDER BY P.product_name";

$query = mysql_query($sql) or die("error selecting records: " . mysql_error() );

         $priorProduct = "";
         $output = "<div id='product_holder'>";
        
         while ($row = mysql_fetch_array($query)) {
        
             $productName = ucwords(str_replace("_"," ",$row["product_name"]));
             if ( $priorProduct != $productName ) {  
                
                 $output .= "<hr class='form_hr' />";
                 $output .= "<h2 id='product_heading'>{$productName}</h2>\n";
                 $output .= "<p id='product_description'>{$row["product_details"]}</p><br/>\n";
                 $priorProduct = $productName;
             }  
       
             $output .= "<div class='prod'>";
             $output .= "<a href='?page=display&id={$row['imgID']}' target='_blank'>"."<img src='{$image_directory}{$row['imgName']}' alt='{$row['product_name']}' title='{$row['product_desc']}' border='0' /></a>";
             $output .= "<p id='p_id'>Product ID: <span class='white'>{$row['product_ref']}</span></p>";
             $output .= "<p class='dims'>".$row['product_dims']."</p>";
             $output .= "<p class='price'>£".$row['product_price']."</p>";
             $output .= "</div>";
         }
         $output .= "<hr id='last' />";
         $output .= "</div>";

?>

I have attached 2 images of the 2 tables below.

 

Is it the way I am retriving the data?

 

Kind regards,

 

L2c.

Edited July 7, 2013 by Love2c0de

[_EmilsM]

 

$sql = "SELECT P.productID, P.product_name, P.product_details, I.imgID, I.product_price, I.imgName, I.product_dims, I.product_ref, I.product_desc "
     . "FROM products AS P,product_images AS I "
     . "WHERE P.productID = I.productID 
        ORDER BY P.productID ASC";

[Love2c0de]

Fantastic, thank you for your reply.

 

Can't believe I missd that.

 

Cheers!

 

L2c.