Display fields from different tables

co_ador · April 3, 2011, 2:32am

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 · April 3, 2011, 2:59am

This query made it

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

co_ador · April 3, 2011, 4:01am

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 · April 3, 2011, 8:04am

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 · April 3, 2011, 12:31pm

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 · April 3, 2011, 12:36pm

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 · April 3, 2011, 12:41pm

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 · April 3, 2011, 12:49pm

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 · April 3, 2011, 1:10pm

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 · April 3, 2011, 3:34pm

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 · April 3, 2011, 3:38pm

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

r937 · April 3, 2011, 6:38pm

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 · April 3, 2011, 7:00pm

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 · April 3, 2011, 7:23pm

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 · April 3, 2011, 7:31pm

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 
    [COLOR="Blue"]ON categories.id = products.category_id [/COLOR]

compare to what you tried –

  FROM products
INNER
  JOIN categories 
    [COLOR="Red"]ON 1 = products.category_id [/COLOR]

see the difference?

co_ador · April 3, 2011, 11:01pm

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 · April 4, 2011, 3:22am

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 · April 4, 2011, 5:01am

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 · April 4, 2011, 11:46am

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 · April 4, 2011, 12:29pm

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.

Display fields from different tables

Table structure for table categories

Table structure for table `categories`