messy code gone wrong

[scottybwoy]

Hi people,

 

I've gone blind at the sight of my own ugly code.  I can't see why it get trapped in a loop + there must be a better way of writing code (sorry it's a bit long ).  Any pointers would be great.

  		if ($number_of_categories > 2) {
  			$limit = 1;
  		} else if ($number_of_categories == 2) {
  			$limit = 2;
  		} else {
  			$limit = 4;
  		}

  		$sql = "SELECT * FROM `categories` WHERE `parent_id` = " . $categories['categories_id'];
  		$qry = mysql_query($sql);

  		if (mysql_num_rows($qry) > 0) {
  			static $i;
  			$i = 1;

  			while (($cat_row = mysql_fetch_assoc(mysql_query($sql))) && ($i <= $limit)) {
  				$n2_sql = "SELECT * FROM `categories` WHERE `parent_id` = " . $cat_row['categories_id'];
  				$n_qry = mysql_query($n2_sql);

  				if (mysql_num_rows($n_qry) > 0) {
  					while (($n_cat_row = mysql_fetch_assoc($n_qry)) && ($i <= $limit)) {
  						$n2_sql = "SELECT p.products_id, p.products_model, p.products_image, p.products_price
				      				FROM products p INNER JOIN products_to_categories ptc
				      				ON ptc.products_id = p.products_id
				      				WHERE ptc.categories_id = " . $n_cat_row['categories_id'] . "
				      				AND p.products_status = 1
				      				ORDER BY p.products_ordered DESC LIMIT 1";

  						$n_cat_prod_row = mysql_fetch_assoc(mysql_query($n2_sql));
  						$info_box_contents = array();
  					$info_box_contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $prod_row['products_id']) . '">' .
																		tep_image(DIR_WS_IMAGES . $n_cat_prod_row['products_image'], $n_cat_prod_row['products_model'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) .
																		'<br />' . $n_cat_prod_row['products_model'] . ' > £' . number_format($n_cat_prod_row['products_price'], 2) . '</a>');

		  			new infoBox($info_box_contents);
		  			$i++;
  					}
  				} else {
  					$n_sql = "SELECT p.products_id, p.products_model, p.products_image, p.products_price
			      				FROM products p INNER JOIN products_to_categories ptc
			      				ON ptc.products_id = p.products_id
			      				WHERE ptc.categories_id = " . $cat_row['categories_id'] . "
			      				AND p.products_status = 1
			      				ORDER BY p.products_ordered DESC LIMIT $limit";

					while ($n_cat_prod_row = mysql_fetch_assoc(mysql_query($n_sql))) {
  						$info_box_contents = array();
  						$info_box_contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $prod_row['products_id']) . '">' .
																	tep_image(DIR_WS_IMAGES . $n_cat_prod_row['products_image'], $n_cat_prod_row['products_model'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) .
																	'<br />' . $n_cat_prod_row['products_model'] . ' > £' . number_format($n_cat_prod_row['products_price'], 2) . '</a>');

	  				new infoBox($info_box_contents);
					}

					$i = $limit + 1;
  				}
  			}
  		} else {
  		$sql = "SELECT p.products_id, p.products_model, p.products_image, p.products_price
      				FROM products p INNER JOIN products_to_categories ptc
      				ON ptc.products_id = p.products_id
      				WHERE ptc.categories_id = " . $categories['categories_id'] . "
      				AND p.products_status = 1
      				ORDER BY p.products_ordered DESC LIMIT $limit";

  			$qry = mysql_query($sql);
			while ($prod_row = mysql_fetch_assoc($qry)) {
				$info_box_contents = array();
  			$info_box_contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $prod_row['products_id']) . '">' .
																		tep_image(DIR_WS_IMAGES . $prod_row['products_image'], $prod_row['products_model'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) .
																		'<br />' . $prod_row['products_model'] . ' > £' . number_format($prod_row['products_price'], 2) . '</a>');

		  	new infoBox($info_box_contents);
			}
  		}

 

...Thanks superior coder 

[sasa]

line

while (($cat_row = mysql_fetch_assoc(mysql_query($sql)))

cause infinity loop

in each loop you query database again and again

solution

change to

$rez=mysql_query($sql);
while (($cat_row = mysql_fetch_assoc($rez))

same in line

while ($n_cat_prod_row = mysql_fetch_assoc(mysql_query($n_sql)))[(code]

[scottybwoy]

lol, cheers, I didn't think it was that simple.  Is there a better way of going about this code anyway.  Thanks