gvp16

ok, I have content table which holds the basic information about a product such as: content_id, content_title ,description, image, meta_tags, visible 1 test product description.... 1234.jpg metatags... 1 and content_values to hold any additional fields that are needed content_option_id, content_option_value, content_id Part_No 1234 1 Brand Ford 1 Price 12.00 1 Colour Blue 1 each product would have a similar set up when the product is pulled from the database I would like all the attributes returned with a single query, so I dont have to add additional queries/ searches to get all the information Thanks.

im using the content_values table to store extra fields for products in the content table. my thinking was that i didnt want to have all the information in one table, im left joining them so i can search by the values for each products. If you think I can do it a better way then I would be great full for you help. Thanks.

yes on content_option_id as well, I have emptied the database a few times thought through testing, do i need to rebuild the index, if so how do i do that? this is what explain returns : Im using group at the end because the query results a list of products and its sizes/ colours, so I group on part_no2 (which is the part no minus the size or colour) and as such I get just one product instead of 4/5. on the product page there is then an option to select colour/ size. Thanks for the help guys.

I have indexes on the auto increment columns and referencing columns, eg content_id. Is that correct?

Hi, all im running this query : SELECT c.content_title, c.content_body,c.content_image,c.permalink, co_pn.content_option_value AS Part_No, co_pn2.content_option_value AS Part_No2, co_b.content_option_value AS Brand, co_b2.content_option_value AS Brand2, co_r.content_option_value AS RRP, co_wp.content_option_value AS Web_Price, co_cl.content_option_value AS Clearance, co_t.content_option_value AS Tax_ID, co_pr.content_option_value AS Price, co_col.content_option_value AS Colour, co_sz.content_option_value AS Size FROM category_items LEFT JOIN content c ON category_items.content_id = c.content_id LEFT JOIN content_values co_pn ON c.content_id = co_pn.content_id AND co_pn.content_option_id = 'Part_No' LEFT JOIN content_values co_pn2 ON c.content_id = co_pn2.content_id AND co_pn2.content_option_id = 'Part_No2' LEFT JOIN content_values co_b ON c.content_id = co_b.content_id AND co_b.content_option_id = 'Brand' LEFT JOIN content_values co_b2 ON c.content_id = co_b2.content_id AND co_b2.content_option_id = 'Brand2' LEFT JOIN content_values co_r ON c.content_id = co_r.content_id AND co_r.content_option_id = 'RRP' LEFT JOIN content_values co_wp ON c.content_id = co_wp.content_id AND co_wp.content_option_id = 'Web_Price' LEFT JOIN content_values co_cl ON c.content_id = co_cl.content_id AND co_cl.content_option_id = 'Clearance' LEFT JOIN content_values co_t ON c.content_id = co_t.content_id AND co_t.content_option_id = 'Tax_ID' LEFT JOIN content_values co_pr ON c.content_id = co_pr.content_id AND co_pr.content_option_id = 'Price' LEFT JOIN content_values co_col ON c.content_id = co_col.content_id AND co_col.content_option_id = 'Colour' LEFT JOIN content_values co_sz ON c.content_id = co_sz.content_id AND co_sz.content_option_id = 'Size' WHERE category_items.category_id = '10937' GROUP BY Part_No2 LIMIT 0,50 which basically selects basic product information from the content table and all its different options from a table a content values table, this works fine when a few results are loaded, however some times its taking up to 90 seconds to return the results. How can i improve the performance of the query? or have I just gone about it wrong? Ive indexed the tables, but im not sure if I have done it correctly Thanks.

Just tried out your modification and it works great, thanks again for the help. Learning alot these last few days.

no thats the only thing I changed, I tested it agaist both and the second version did seem to slow down. its not in the root directory, its in a directory called products, what this make a difference? Thanks.

anyone?

I currently have a .htaccess file setup with this : RewriteEngine on RewriteRule ^([^/\.]+)/?$ index.php?id=$1 and im trying something out using this RewriteEngine on RewriteRule ^([^/]+)/([^\.]+) index.php?id=$2&cat=$1 but using the new setting there is a noticeable load time difference on pages 1 or 2 seconds. Have i set up something wrong? Thanks.

Thanks for pointing out the error, as it happens in this case there wont be a long string with the part number so the code will work anyway. As for intentionally leaving something out, I think thats a bit mean, as the title suggests im newbie at using regex and after a few hours of googling trying to work it out my self I gave up and turned here for help. you should have mentioned that it was incomplete and i had to finish it, after all it was a point in the right direction. requinix, thanks for the suggestion, I did think of something like that, but there are going to be between 10 and 20 patterns that I need to work on. Thanks for all the help anyway.

that looks exactly what I was after, thanks very much

Thanks thats great, but i think I need to explain what i need to do a little better, I have a field in a database full of part numbers (9000+) im pulling them out with standard sql query so i have a variable to work with $row["Part_No"]; each type of product has a different pattern to the part number : eg . components have a number like : 0000-000 clothing have a number like : 0000AS (A = colour code can be anything from A - Z, S = Size can be anything from S,M,L,XL,XXL) and spares have a number like : 00-0000 when looping through the codes I need to determine which type of product it is so i can do different things to them if($row["Part_No"] matches patten 0000-00) { do stuff } if($row["Part_No"] matches patten 00-0000) { do stuff } if($row["Part_No"] matches patten 0000CM) { do stuff } Thanks.

its more of a match rather can grab, so if a part number matches this pattern: 4 digits a hyphen then 3 digits 0000-000, there is no other information in the string other than the part number. Thanks.

yes just the numbers 3641-001 Thansks

Hi, ive not done much with regex, but have started trying to use it with no lucj I have a list of product codes that match a few patterns, eg : 3641-001 ( part & colour) 01-1234 // not important 1234CS (part, colour, size) 123456 // not important im trying to right a function that takes these patterns in to account, eg. if(part_no like '0000-000') { do something } if(part_no like '00-0000') { do something else } can anyone help? Thanks.

Excellent, thanks very much, i spent a few hours yesterday trying to figure it out! Is there one method you would pick out of the 2? eg. performance etc... Thanks.

Hi, i have 2 tables content which has : content_id, content_title,content_body eg. (1,this is a test, information about the test) and content_options which as : content_id, option, value eg. (1,price,29.99) (1,weight,15kg) (1,sale,yes) i know you can do something like, SELECT content_options.value AS price WHERE content_options.option = "price" which would give me price 29.99 by how can i join to the two in a single query? eg. get a result like : content_id | content_title | content_body | price | weight | sale 1 this is a test this is informaton about the test 29.99 15kg yes Thanks.

i dont really, but i thought it would be better than removing the punctuation all together, i thought about using addslashes, but again i would have to add it to far to many elements, and then use stripslashes to display data correctly. I will take a look into what you recommended, thanks for you help.

I think were getting our wires crossed, im looking to avoid doing sanitation on over 90 separate $_POST[""] elements that have been used in a query. rather than do $name = str_replace(" ' ", """,$_POST["name"]); $address= str_replace(" ' ", """,$_POST["address"]); $town= str_replace(" ' ", """,$_POST["town"]); and so on.... I was wondering if it was possible to do it on the $_POST array, so something like foreach $_POST as $var{ str_replace(" ' ", """,$var); } $name = $_POST["name"]; $address=$_POST["address"]; $town= $_POST["town"]; and then proceed to do the query

well everything is written in php... and im not entirely sure how to go about it so i was hoping for some advice, eg. can i simply do str_replace(" ' ", """,$_POST"); ?

Im bug fixing a script (not my own) and the use of " and ' hasn't been filtered out before an insert query is ran. there are about 4 scripts of 30 $_POST["something"] input elements that need to be checked, and i was wondering can i just do a find and replace on $_POST rather than each element? Thanks.

Thanks for all the replies guys, mjdamato i took your advise it gave it ago and it works exactly as i need it to, good learning exercise. Thanks very much.

i didnt want to set up columns for each table cell because the number of rows/ columns will be determined by the user when inputting the information, so I decided it would be better to store all the information in 1 field and then split it up for formatting, but i also need to consider the user wanting to add/ remove a column/ row after the table has been created. Thanks

ok great thanks, ill give it ago. Thanks very much

ok that seems to be exactly what I want, how would i remove a whole row (array 2 for example), also what format would I store this in a database? Thanks for you replies.