what's wrong with this join query?

[co.ador]

There should be a way to put it all in one query but I am going to focus on using two queries now while the solutions of integrating it in one comes. I will research on that.

 

I am going to start integrating it with two queries.

[mattal999]

This might be slightly off topic now, but the problem with your original query was due to a rogue DESC. The original:

 

$sql = mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6");

The fixed:

 

$sql = mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' LIMIT 6");

[co.ador]

 

Yes I didn't specify the field in the DESC Clause. Maybe if I would put DESC category, then it will specify to DESC category field.

 

is this correct?

[mattal999]

It would have to be:

 

$sql = mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' ORDER BY products.category DESC LIMIT 6");

[co.ador]

You are right the ORDER BY clause was missing.

 

Now from what Nightsly was suggesting on having to queries instead of one gigantic query where I could take care of categories and products category field changed now to name field and use either or. Now after dividing it into to queries I obtained this, but it is only displaying the price and product_name field, but beyond that it seems the code is getting confused.

<?php

$dynamicList = "";
$sql= mysql_query("SELECT * FROM products ORDER BY date_added DESC LIMIT 6");
$productCount = mysql_num_rows($sql); 
if ($productCount>0 )
     {
     while($row = mysql_fetch_array($sql))
	          {
				$category_id= $row["category_id"];  
	            $id= $row["id"];
				$product_name= $row["product_name"];
				$price = $row["price"];
				$category = $row["category"];
				$subcategory = $row["subcategory"];
				$location = $row["location"];
				$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
                   $dynamicList .= '

<div class="dynamicList">

<div class="alone">
<div class="dynamicListF"><a href="product.php?id=' . $id . '"><strong>' . $product_name . '</strong></a></div>
<a href="product.php?id=' . $id . '">
<img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '                   "align="left" width="178" height="230" border="1" /></a><br />
     
<div class="Divinfo">
     <div >
       <p >Price:</p>
       <p >  $' . $price . '</p> 
     </div>';
 ?>
  <?php 
  $sql3= mysql_query("SELECT name FROM categories WHERE id = ". $category_id . ""); 
$productCount = mysql_num_rows($sql3); 
if ($productCount>0 )
     {
'<div class="clear"></div>
     <div >
       <p >Category:</p>
       <p> ' . $name . '</p> 
     </div>';
 }
 ?>
     <?php 

 '<div>
 <div class="clear"></div>
        <p >Sub:</p>
        <p> ' . $subcategory. '</p> 
     </div>

   <div class="clear"></div>
  <div class="city">
        <p> ' . $location. '</p>  
      </div>

     </div>
  </div>  


</div>


</div>
';
}} else {
$dynamicList = "We have no products listed in our store yet";
}
mysql_close();
?>

 

[mikhl]

You need to use a join so that it looks like:

 

SELECT * FROM products JOIN categories ON(id_of_the_products_tbl = id_of_the_categories_tbl WHERE products.category = '$categoryurl' DESC LIMIT 6"

[co.ador]

The INNER JOIN  ON made it so fa r the query below join all the fields of both when category_id in the products table is equal to the id in the categories table

 

<?php

SELECT * FROM products INNER JOIN categories ON category_id = categories.id

 

?>

 

[co.ador]

 

co.ador's Avatar

 

Join Date: Apr 2009

Posts: 994

 

 

I thought I had it but not, Now it is displaying a error when I specify the fields in the SELECT clause

 

SELECT products.id, categories.id, category_id, name, product_name, price, subcategory, location, date_added FROM products INNER JOIN categories ON category_id = categories.id"

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' at line 1