Search the Community

Hi, (Thanks for the great book ) I am trying to write a a stored procedure that will perhaps loop through an array of product ids and then JOIN tables in the database depending on what category the product is in (their may be multiple products in different categories). I am not sure if this possible - am I barking up the wrong tree? Very simplified - this obviously doesn't work but perhaps you can get an idea of what I'm trying to achieve) IN: product_ids_array VARCHAR (300) //Array of ids sent with the call BEGIN // start loop here somehow ? Would I have to split ID array first? DECLARE master_category SMALLINT SELECT master_cat_id INTO master_category FROM products WHERE id IN (products_ids_array); IF master_category = 1 THEN SELECT p*, sp* FROM products AS p INNER JOIN specs_table_1 as sp ON sp.id = pr.specs_id WHERE pr.id IN (products_ids_array); ELSE SELECT p*, sp* FROM products AS p INNER JOIN specs_table_2 as sp ON sp.id = pr.specs_id WHERE pr.id IN (products_ids_array); ENDIF; end loop here ?? END Any help would be much appreciated.

Hi Larry, Pretty much got all my links working, got .htaccess and mod_rewrites on Apache working, got images loading, got css working but I was wondering why some data from the database is being returned while other data in the same table and page returns nothing. It's probably easier to just show the pages and see if you have any suggestions. Here is my Coffee page: If I select Dark Roast nothing is returned from the database and it loads the error page: If I choose Kona it appears to pull data from the database and work. Don't understand why one works and the other doesn't. Thanks for look at this!

Hi, I hope everyone is well. When running the stored procedure for creating select_products below in phpMYADMIN I get the error: "#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 10". When I look at the line, the syntax seems to be correct. Line 10 is where "ORDER BY name ASC;" lies, and where the error message is stating the problem resides. Can anyone point me in the right direction? Many Thanks, CREATE PROCEDURE select_products(type VARCHAR(6), cat TINYINT) BEGIN IF type = 'coffee' THEN SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole, sc.price) AS name, 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 WHERE general_coffee_id=cat AND stock>0 ORDER by name ASC; ELSEIF type = 'other' THEN SELECT ncc.description AS g_description, ncc.image AS g_image, CONCAT("O", ncp.id) AS sku, ncp.name, ncp.description, ncp.image, 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 WHERE non_coffee_category_id=cat ORDER by date_created DESC; END IF; END$$

Hello Larry, I am working my way through Effortless E-Commerce and can't seem to create a stored procedure using the downloadable code from your site. My issue I believe is my version of phpMyAdmin 2.8.0.1 isn't supporting delimiters. I continually get this error. Is there a way to work around this? ErrorSQL query: DELIMITER $$ CREATE PROCEDURE select_categories( TYPE VARCHAR( 6 ) ) BEGIN IF TYPE = 'coffee' THEN SELECT * FROM general_coffees ORDER BY category; MySQL said: #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 'DELIMITER $$ CREATE PROCEDURE select_categories (type VARCHAR(6)) BEGIN IF ty' at line 1

hi Interesting book especially the stored procedures. I'd be interested to hear from anyone who is interested in extending or generalising the database; thus creating more variables to search upon. After all, not all of us want to sell coffee do we? I'm especially interested in filters or searching by specific categories see: http://acookson.org/wp-content/uploads/2013/01/shop-screenshot.png for an example of what's on my mind. In my fictitional jeans shop example, I was playing around with MySQL workbench to come up with some possibilities for an ER diagram: http://acookson.org/wp-content/uploads/2013/01/ecommerce3.png It's totally at prototype stage and was looking for some inspiration really; maybe an on-going discussion around this topic. best wishes Andrew