Display fields from different tables.

[co.ador]

CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(225) NOT NULL,
`price` varchar(16) NOT NULL,
`details` text NOT NULL,
`category` varchar(255) NOT NULL,
`subcategory` varchar(225) NOT NULL,
`date_added` date NOT NULL,
`location` varchar(255) NOT NULL,
`category_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_name` (`product_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;




Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category` varchar(25) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

As you can see there are two tables categories and products, I am looking for a query to display the category from the categories table not the products table. So far I have the following but it will display the category field of the products table. How can I point in the query that I want to display the category field of categories table?

Even If I call it different names one category and another p_category I still confused how can I structure the query to display in the code.

"SELECT * FROM products, categories ORDER BY date_added DESC LIMIT 6" 

[co.ador]

This query made it

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

[co.ador]

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

[r937]

first of all, the query you posted does not have " anywhere in it, although it does have a dangling " right at the end

secondly, it is important that in any query with more than one table, you should properly qualify each column with its table name, just like you did for products.id, categories.id

also, it is important to use column aliases, so that when the result set comes into php, you can distinguish columns which have identical column names from each other using their aliases

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

[co.ador]

Thank you r937,

Yes, Something I was looking were columns aliases. So far before you have posted all I found were table aliases but I was not sure how to apply in the columns even though I knew it was possible from pass experiences. I have that query it works pretty good the only thing is that it is returning all the rows in categories even though there is only one product name ON that condition.

For instance. If there is one product named "good plate" with one category_name assign on the row of the join, it supposed to return that category_name only but instead it will return all the category_name found on the categories table, repeating the product_name as it iterate all the category_name colums.

<?php
$dynamicList = "";
$sql= mysql_query("SELECT products.id AS products_id
                                                 , categories.category_id
                         , categories.name AS category_name
                         , categories.id AS category_id
                         , product_name
                         , products.price
                         , products.subcategory
                         , products.location
                         , products.date_added 
                         FROM products 
                         INNER JOIN categories 
                         ON categories_id = category_id 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"]; 
                    $name= $row["category_name"]; 
                    $id= $row["products_id"];
                    $product_name= $row["product_name"];
                    $price = $row["price"];
                    $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>

<div class="clear"></div>
     <div >
       <p >Category:</p>
       <p> ' . $name . '</p> 
     </div>
    

     
     <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();
?>

[r937]

the php does me no good, sorry, i don't do php

the problem is in your sql anyway

remember i said you should be using properly qualified columns? and yet you coded this --

ON categories_id = category_id

there's your problem

[co.ador]

In other words it is returning all the rows in column category_name and only on that column after that everything is returning normal.

[co.ador]

Thank you for that typo, still the problems persist I think it is in the while loop,

<?php 
$idc = $_GET['id'];
"SELECT products.id AS products_id
                         ,products.category_id
                         ,categories.name AS categories_name
                         ,product_name
                         ,products.price
                         ,products.subcategory
                         ,products.location
                         ,products.date_added 
                         FROM products 
                         INNER JOIN categories 
                         ON ".$idc." = category_id"

?>

This is another file containing the same query and there is where the problems is. The ".$idc. " variable contains the same value as categories.category_id Still the problems persist on this one.

[r937]

i have a suggestion which i think will help you a great deal

first, copy the SELECT statement out of your php code into a text editor

choose some id value, which you will use instead of

$idc = $_GET['id'];

and then substitute that value into the SELECT statement

then run the query directly in mysql, through the mysql workbench or whatever frontend utility you have

then please do share what happens, and i will help you understand it

[co.ador]

thank you r937 for the advice.

i did run it with the value of 1

SELECT products.id AS products_id, products.category_id, categories.name AS categories_name, product_name, products.price, products.subcategory, products.location, products.date_added
FROM products
INNER JOIN categories ON 1 = category_id
LIMIT 0 , 30

That's the query result or the numbers of rows it brought back.

Now it seems to me that it is the SQL the problem and not the php according to what I want to achieve, doesn't mean the query we have now is wrong, it just that it is not displaying the results I want. The result set goes as fallows it repeated the products_id 1 by the total numbers of categories_name when I really looking to display how many categories_name rows there are that matches that critiria then after finding that then display the categories_name rows that match that together with the other values in the products table that I choose in the SELECT table.

[co.ador]

Do you need the dump or what we are discussing so far is ok?

[r937]

here's your problem --

FROM products
INNER JOIN categories ON 1 = category_id

the ON condition is not appropriate for what you want

your ON condition is true for only one category, but this category is then matched with every single product

let me know if you don't understand why that's wrong

[co.ador]

I thought about that, before and I thought about putting WHERE condition. I will research about that...

I want to be on for one category and then display that row based on the category it matched. Now it stills display more than one category_name.

It seems the ON clause is not specific, Please explain I don't understand.

[co.ador]

I changed it to WHERE clause but it won't run on the Run SQL query Database script. It has an error in your SQL syntas Error number #1064.

SELECT products.id AS products_id
,categories.id AS categori_id
,products.category_id
,categories.name AS categories_name
,product_name
,products.price
,products.subcategory
,products.location
,products.date_added
FROM products,categories
WHERE categories.id = category_id ORDER BY `products`.`location` ASC

I used the WHERE clause instead and it worked really good. It only brought back the categori_id and Category_id matches....


URRA!

Now I haven't test it in the code don't know how its going to work to what we have discussed so far. I have a question why ON was returning all the rows?

[r937]

I have a question why ON was returning all the rows?

because it was incorrect

here's what you have --

  FROM products
     , categories 
 WHERE categories.id = products.category_id

here's the equivalent explicit JOIN --

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

compare to what you tried --

  FROM products
INNER
  JOIN categories 
    ON 1 = products.category_id 

see the difference?

[co.ador]

I see it

FROM products INNER JOIN categories


Here,

SELECT * FROM products, categories

WHERE categories.id = products.category_id


Select everything or the fields specify where 1=1 or this field match = this one.

Definitely there is a different statement. They speak different things.

[co.ador]

For another page I was using the same query but it was not working because it needed to conditions so I added an AND as below.

"SELECT products.id AS products_id
, products.category_id AS category_id
, categories.name AS category_name
, categories.id
, products.product_name
, products.price
, products.subcategory
, products.location
, products.date_added
FROM products, categories
WHERE category_id = categories.id AND category_id = ".$idc.""

Before I only had WHERE category_id = ".$idc." Which was returning all the category_name even the ones I didn't want so I put to display only if those two conditions are met.

I am glad I have started to learn to speak this language..

[r937]

I am glad I have started to learn to speak this language..

me too

don't forget -- use explicit JOIN syntax with ON conditions (not the comma-list style with WHERE conditions for the join), and also please remember to qualify your columns properly (products.category_id = categories.id, not category_id = categories.id)

[co.ador]

ok I will take those in mind specially qualifying the columns, which some how got confusing when I Alias categories.id AS something else and the query was saying there was not column as such then I had to take the Alias and it worked. Just for that column after the = sign.

[co.ador]

SELECT products.id AS products_id
, products.category_id AS category_id
, categories.name AS category_name
, categories.id AS categori_id
, products.product_name
, products.price
, products.subcategory
, products.location
, products.date_added
FROM products, categories
WHERE category_id = categori_id


if you notice I Alias categories.id AS categhori_id and put after the = sign that one query right there returned me an error saying there was not column call categori_id. thought if it happens to you before. In that case I took the alias out and left it as categories.id and it worked. It was funny because it ended resulting like that only for that column.

[r937]

please allow me to make some suggestions

1. always use explicit JOIN syntax, not the old style comma-list join with the join conditions in the WHERE clause

2. always properly qualify all columns with their table names when there is more than one table in the query

3. use column aliases in the SELECT clause, so that when the result set comes into php, you can distinguish columns which have identical column names from each other using their aliases

4. if you write the query in a way which guarantees that two columns are always going to be equal, you do not really need to return both columns in the SELECT clause

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

if you take a close look, you will see that his query is identical to the one i gave you in post #4

please don't change anything back again

[co.ador]

The first point I don't understand you mean that to use
this approach with JOIN

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

Instead of joining as below.

FROM products, categories
WHERE category_id = categories.id AND category_id = ".$idc."

I don't know if it is my version of Mysql server or what but The INNER JOIN on this particular situation didn't work.

Don't know why.

[r937]

sorry, "didn't work" is not a mysql error message that i'm familiar with

[co.ador]

WEll, I know you were giving me a suggestion,

Really don't know what you mean, but we have discuss why it didn't work because INNER JOIN would return all the rows as well. When WHERE is more specific. That's what I understood.

Sorry if I miss understood you,

I do think you are very knowledgable,

Thank r937.

[r937]

this --

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

is equivalent to this --

FROM products, categories
WHERE category_id = categories.id

if you add this to the comma-style join query --

AND category_id = ".$idc."

then you must obviously also add it to the JOIN syntax query !!

so this --

FROM products, categories
WHERE category_id = categories.id
AND category_id = ".$idc."

is equivalent to this --

FROM products INNER JOIN categories
ON categories.id = products.category_id
WHERE category_id = ".$idc."

doesn't that make more sense now?