piznac Posted September 9, 2008 Share Posted September 9, 2008 I have three tables: Table A contains all product id and a option id. Table B contains all option types,. ie name,desc,option id, product id. Tabke c contains the actual sub options ie name,desc, add price, option id, sub option id. Products Table A Table B Table C pro_id ------------prod_id Name Name option_id--------option_id----option_sub_id desc desc image price image Ok what Im trying to do is list custom options of a given product. I may have gone about this backwards or just plain strange, but here is what Im hoping to do: Based on a product Id var: Table B.name Table B.desc <br> Then list each option based of Table B.option_id: Table C.name Table C.desc ANd repeat this Table B.name Table B.desc <br> Then list each option based of Table B.option_id: Table C.name Table C.desc Table C.name Table C.desc Table C.name Table C.desc Table B.name Table B.desc <br> Then list each option based of Table B.option_id: Table C.name Table C.desc Now Im confised,. here is what I have tried: <?php function pro_options($id){ $sql = "SELECT * FROM `pro_op_main` WHERE `pro_id` = '$id' "; $result = mysql_query($sql); $row = mysql_fetch_array($result); $num_rows = mysql_num_rows($result); do{ $opid = $row['op_id']; //get subid data $sql2 = "SELECT * FROM `prod_op` WHERE `op_id` = '$opid' "; $result2 = mysql_query($sql2); $row2 = mysql_fetch_array($result2); $num_rows2 = mysql_num_rows($result2); do{ $op_id = $row2['op_id']; $opname = $row2['name']; $op_id2 = explode(" ",$op_id); foreach($op_id2 as $key){ //get option values $sql3 = "SELECT * FROM `prodsub_op` WHERE `prodsub_op_id` = '$key' "; $result3 = mysql_query($sql3); $row3 = mysql_fetch_array($result3); $num_rows3 = mysql_num_rows($result3); echo "$opname<br />"; echo $row3['name']; } }while($row2 = mysql_fetch_array($result2)); }while($row = mysql_fetch_array($result)); } ?> && <?php function pro_options($id){ $sql = "SELECT * FROM `pro_op_main` WHERE `pro_id` = '$id' "; $result = mysql_query($sql); $row = mysql_fetch_array($result); $num_rows = mysql_num_rows($result); //make array of results id do{ $opid = $row['op_id']; $opidarray = explode(" ",$opid); print_r($opidarray); foreach($opidarray as $id){ $sql2 = "SELECT * FROM `prod_op` WHERE `op_id` = '$id' "; $result2 = mysql_query($sql2); $row2 = mysql_fetch_array($result2); $num_rows2 = mysql_num_rows($result2); do{ echo "<br />"; echo "<b>"; echo $row2['name']; echo "</b>"; echo "<br />"; echo $row2['desc']; echo "<br />"; $id2 = $row2['op_id']; $op_sub_id = explode(" ",$id2); foreach($op_sub_id as $newid){ //get option values $sql3 = "SELECT * FROM `prodsub_op` WHERE `prodsub_op_id` = '$newid' "; $result3 = mysql_query($sql3); $row3 = mysql_fetch_array($result3); $num_rows3 = mysql_num_rows($result3); do{ echo "<br />"; echo $row3['name']; echo "<br />"; }while($row3 = mysql_fetch_array($result3)); } }while($row2 = mysql_fetch_array($result2)); } }while($row = mysql_fetch_array($result)); ?> What this is returning is correct but the table c results are not returning under the correct table B headers. Can you guys understand this? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted September 9, 2008 Share Posted September 9, 2008 and your problem is? Quote Link to comment Share on other sites More sharing options...
piznac Posted September 9, 2008 Author Share Posted September 9, 2008 Sorry was tabbing and hit the eneter key by accident,. please check again. Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted September 9, 2008 Share Posted September 9, 2008 Stop doing queries in queries and do a properly written JOIN query Google this because you will need to read some tutorials on MySQL JOINs Quote Link to comment Share on other sites More sharing options...
piznac Posted September 9, 2008 Author Share Posted September 9, 2008 Yeah thats what I figured you guys would say lol. Would something like this work <?php function pro_options($id){ // Construct our join query $query = "SELECT * ". "FROM pro_op_main , prod_op ,prodsub_op ". "WHERE pro_op_main.pro_id = '$id' && pro_op_main.op_id = prod_op.op_id && prod_op.op_id = prodsub_op.prod_subid"; $result = mysql_query($query) or die(mysql_error()); // Print out the contents of each row into a table while($row = mysql_fetch_array($result)){ echo $row['prod_op.name']. " - ". $row['prod_op.desc']; echo "<br />"; echo $row['prodsub_op.name']; echo "<br />"; } } ?> Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted September 9, 2008 Share Posted September 9, 2008 No u need to use a JOIN and join on Product ID, Option ID etc. There may be a need to also use GROUP_CONCAT depending on how your system works. Quote Link to comment Share on other sites More sharing options...
piznac Posted September 9, 2008 Author Share Posted September 9, 2008 Ok I will start the research process,. thanks for the tips,. if you have any more Im all ears! Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted September 9, 2008 Share Posted September 9, 2008 well based on what you have written you want to really pick 1 product to use in a query and then make the options the primary item for it Quote Link to comment Share on other sites More sharing options...
piznac Posted September 9, 2008 Author Share Posted September 9, 2008 Im a bit confused by the last comment. I want to have option categories for each product and options for each category. What is is displaying right nwo is this: Top Draft Stop NO Top Draft Stop HOOD-recommended for exterior mount aplications 3 FT Top Draft Stop Curtain Color Orientation NO Hood Where it should display: Top Draft Stop NO Top Draft Stop 3 FT Top Draft Stop HOOD-recommended for exterior mount aplications NO Hood Curtain Color Orientation Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.