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"
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
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
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.
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.
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.
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?
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…
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)
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.
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.