Jump to content

SQl results loop trouble


piznac
 Share

Recommended Posts

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?

 

                               

Link to comment
Share on other sites

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

	}
?>

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.