Page 1 of 1

Help me make this query better!

Posted: Tue Apr 13, 2010 1:36 am
by invisibled
ok so, i have this code (see below), it displays a bunch of info for items in a shopping cart. Now, obviously, this is a fairly gross way to do it, but it's the only way i could get it to work. I know how to do some basic join's and unions but this is just silly. Can anybody help me turn these 5 query's into 1 or 2? Altering tables is not an option, as we are working with an existing warehouse database.

Code: Select all

//Update cart_contents view
if(isset($_GET['update_cart'])):
	
	$user_ip = $_SERVER['SERVER_ADDR'];
	$sql = mysql_query("SELECT * FROM cart_contents WHERE user_ip = '$user_ip' ");
	while($row = mysql_fetch_assoc($sql)):
		extract($row);
		
		//
		$sql2 = mysql_query("SELECT CategoryID FROM ItemCategories WHERE ItemID = '$ItemID' ");
		$row2 = mysql_fetch_assoc($sql2);
		$CategoryID = $row2['CategoryID'];
		
		//
		$sql3 = mysql_query("SELECT ThumbNailImage, Name FROM Categories WHERE CategoryID = '$CategoryID' ");
		$row3 = mysql_fetch_assoc($sql3);
		
		//
		$sql4 = mysql_query("SELECT * FROM application_mappings WHERE item_id = '$ItemID' ");
		$row4 = mysql_fetch_assoc($sql4);
		$jetting_id = $row4['jetting_id'];
		
		//
		$sql5 = mysql_query("SELECT MAKE, MODEL FROM Jetting WHERE ID = '$jetting_id' ");
		$row5 = mysql_fetch_assoc($sql5);
		
		print'
			<ul class="id_'.$ItemID.'">
				<li class="item_col">
					<img src="lib/img/products/'.$row3['ThumbNailImage'].'" width="113" height="91" alt="'.$row3['Name'].'" />
					<span class="item">'.$row3['Name'].'</span>
					<span class="for_vehicle">for '.$row5['MAKE'].' '.$row5['MODEL'].'</span>
				</li>
				<li>
					<ul class="quantity">
						<li>'.$quantity.'</li>
						<li class="btns">
							<div class="add" title="Add Quantity">&nbsp;</div>
							<div class="minus" title="Remove Quantity">&nbsp;</div>
						</li>
					</ul>
				</li>
				<li>$150.00</li>
				<li class="remove"><div class="remove_item" title="Remove Item">&nbsp;</div></li>
			</ul>
		';
	endwhile;
	
endif;

Re: Help me make this query better!

Posted: Tue Apr 13, 2010 5:56 pm
by Sofw_Arch_Dev
I'll give it a whirl. Can you post the schema for the database?