obsessed with removing query from loop need help

[Ujj]

Hi again,

 

being obsessed with removing queries from loop to optimize my code as suggested, here is my a situation i am stuck while trying to avoid query inside loop

 

this is my t_item table

 

and this is item_detail

 

now i am running query like this to find out the lowest priced item from each track_item row of table t_item of each user_item of table t_item.

 

after that i also need to find out price for each user_item of t_item table..

 

 

$sqlAdjustPrice = "select * from t_item where `user_id` = 63";
$rst=mysql_query($sqlAdjustPrice);
while($row1=mysql_fetch_array($rst))

{
	$myItem = $row1['user_item'];	
	$trackedItem = $row1['track_item'];
	$adjustPrice = $row1['adjust_price'];
	$groupID = $row1['g_id'];


if($adjustPrice=='y')	{

$sql = "SELECT  price FROM item_detail WHERE itemID in ($trackedItem)  AND groupID='6' ORDER BY price asc LIMIT 1";	
$rs=mysql_query($sql);
$row = mysql_fetch_array($rs);
$lowestPricedTrackedItem = round($row['price'],2);	

	 $sql = "SELECT price FROM item_detail  WHERE `itemID` = '".$myItem."' and `user_id`='63' and groupID='6'";
      $rs=mysql_query($sql);
	$row = mysql_fetch_array($rs);
	$myItemPrice = $row['price'];


}

 

how can i avoid loop here please ?

[Christian F.]

Please read up on the MySQL JOIN syntax, as it is the proper way of dealing with these kind of situations.

[Jessica]

You should not be storing comma delimited data in a field, each of those needs it's own row.

[Ujj]

Please read up on the MySQL JOIN syntax, as it is the proper way of dealing with these kind of situations.

 

i tried but no success as I need to run this query for every user_item with condition where track_item IN.

[xyph]

You should not be storing comma delimited data in a field, each of those needs it's own row.

[ManiacDan]

Just in case it takes 3 people to say it:  You are doing this wrong.  You cannot store comma-delimited lists in a single column.  Why not?  Because of this thread.  It causes this problem.  This problem cannot be solved.  Your database is not designed right.

[KevinM1]

You should not be storing comma delimited data in a field, each of those needs it's own row.

 

For more info, Intro to Database Normalization: http://mikehillyer.com/articles/an-introduction-to-database-normalization/