Jump to content
Larry Ullman's Book Forums


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About Hillbilly

  • Rank
  1. Thanks Larry, One last question regarding the above. I just have one small issue in that some of the columns names in Winch Specifications table and the Hoist Specifications table are the same. Is there any way to add a prefix to the column names of the entire table? Eg: SELECT psw.* AS psw_*, psh.* AS psh_* ... (rather than listing every singles column with AS)
  2. OK - I managed to return the products and the specifications in a big table as follows: IN product_ids VARCHAR (300) // the product ids and sku's sent concatenated in an array eg: "1-WIN01","123-HOIS98","206-WIN651" // I concatenated the sku and ids as if I used the id on its own then the WHERE clause failed as it would find "1" in "123" BEGIN set @query = concat("SELECT c.category, pr.*, psh.*, psw.* FROM products AS pr INNER JOIN categories AS c ON c.id = pr.category_id LEFT JOIN product_specs_hoists AS psh ON psh.id = pr.specs_id LEFT JOIN product_specs_winches AS psw ON psw.id = pr.specs_id WHERE CONCAT(pr.id,pr.sku) IN (" , product_ids ,") AND pr.enabled=1"); PREPARE stmt FROM @query; EXECUTE stmt ; END So now I can simply use the category result on the php page to decide which specifications to display. If ($row['category']== "winches" { // echo Winches Specifications; } else { //echo hoist specifications } Am I re-inventing the wheel here - is there an easier way to do this?
  3. Thanks for responding Larry, Basically I would like to return a list of products with their technical specifications. The database has a list of products, each product belongs to a category and each category has a separate table for it's technical specifications. For example: Product (Winch 1) - Category (Winches) - Specifications (Winches Technical Specs) Product (Hoist 1) - Category (Hoists) - Specifications (Hoists Technical Specs) Product (Winch 2) - Category (Winches) - Specifications (Winches Technical Specs) etc I have a situation where I need to call products from both winches and hoists categories at the same time but I am unsure of how to write the Stored Procedure so it knows which Technical Specs table join to for each product? P.S. The products table is linked to the categories table with an index (category_id) and to its specifications by an index (specs_id). So in this example "Winch 1" and "Hoist 1" could have the same Specs_id column value but these would be referring to different tables. I hope that makes sense?
  4. 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.
  5. I am sure this is a stupid question but I'm tearing my hair out. I am trying to email data that I have cleaned (from a multi-line text box) with escape_data. The problem is the data is sent with \r\n instead of actual line breaks. Eg: message in email will read "Hello\r\nThis is the 1st parapgraph\r\n\r\nThis is another" I have tried to remedy this below but it does not work: if ($_POST['Information'] != '') { $Information = escape_data($_POST['Information'], $dbc); $Informationformatted = str_replace(array("\r\n", "\r", "\n"), "<br />", $Information); } else { $contact_errors['Information'] = 'Please enter your message'; } //message in email $body = $Informationformatted; $headers = 'MIME-Version: 1.0'. "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1'."\r\n"; $headers .= 'From:'.$Email; mail ('email@email.com', 'Message', $body, $headers); I am still getting the same output: "Hello\r\nThis is the 1st parapgraph\r\n\r\nThis is another" I have also tried $Informationformatted = nl2br($Information); This did not work either. (Or would be something to do with the headers?)
  • Create New...