Jump to content
Larry Ullman's Book Forums

Fetch Every Product


Recommended Posts

Hi Larry,

 

In the first edition on page 334, there is a query:

 

$q = '(SELECT CONCAT("O", ncp.id) AS sku, ncc.category, ncp.name, ncp.price, ncp.stock FROM non_coffee_products AS ncp INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id ORDER BY category, name)

UNION

(SELECT CONCAT("C", sc.id), gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id ORDER BY sc.general_coffee_id, sc.size, sc.caf_decaf, sc.ground_whole)';

Now when I run this query, I get an error in my application.

When I run the above query in phpmyadmin, it says that:

 

'Unknown column 'sc.size' in order clause'

 

1)There is not a 'size' column in the 'specific_coffees' (sc), table. So is 'sc.size' in the order clause, in the above query, a typo error?

 

2)If it is a typo, what should it be instead of 'sc.size'?

 

regards

Link to comment
Share on other sites

The table structure I have in my SQL file is

CREATE TABLE `specific_coffees` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `general_coffee_id` tinyint(3) unsigned NOT NULL,
  `size_id` tinyint(3) unsigned NOT NULL,
  `caf_decaf` enum('caf','decaf') DEFAULT NULL,
  `ground_whole` enum('ground','whole') DEFAULT NULL,
  `price` decimal(5,2) unsigned NOT NULL,
  `stock` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `general_coffee_id` (`general_coffee_id`),
  KEY `size` (`size_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

where specific_coffees has a size_id column. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...