SQl results loop trouble

[piznac]

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?

 

                               

[cooldude832]

and your problem is?

[piznac]

Sorry was tabbing and hit the eneter key by accident,. please check again.

[cooldude832]

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

[piznac]

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 />";
}

	}
?>

[cooldude832]

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.

 

[piznac]

Ok I will start the research process,. thanks for the tips,. if you have any more Im all ears!

[cooldude832]

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

[piznac]

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