How can I speed up these queries?

matthewst

This piece of code searches my database for all ads sold to a customer. When it finds them it then searches the database to find out if an ad has been approved or not. Then it moves on to the next ad. All this works, it just takes FOREVER. Is there any way to speed these queries up?

$ads = mysql_query("SELECT id FROM ad_order WHERE cust_id='$table_id' AND status != '99' ORDER BY company", $db_link);
		while($row = mysql_fetch_array($ads)) { 
		$get_order = $row['id'];

$sql = mysql_query("SELECT order_id FROM copy WHERE order_id='$get_order' AND rep_approve='1'");
	$num_rows = mysql_num_rows($sql);
	$approveNum=$approveNum+$num_rows;
	}

NogDog

You could get all the approved orders for those ad_order conditions with:

$query = "
SELECT id FROM ad_order
   INNER JOIN copy ON copy.order_id = ad_order.id
   WHERE 
      ad_order.cust_id='$table_id' AND 
      ad_order.status != 99        AND 
      copy.rep_approve = 1
   ORDER BY ad_order.company
";

matthewst

I need to echo the number sold and the number approved. Do you know how I could echo both with that query?

I can't get this to do it.

$query = "SELECT id FROM ad_order INNER JOIN copy ON copy.order_id = ad_order.id WHERE ad_order.cust_id='$table_id' AND ad_order.status != '99' AND copy.rep_approve = '1' ORDER BY ad_order.company";
		$num_rows = mysql_num_rows($query);
		$approveNum=$approveNum+$num_rows;

echo mysql_num_rows($query);
echo $approveNum; $approveNum=0;

jamesm6162

Firstly you shouldn't call mysql_num_rows on the querystring itself.

$result= mysql_query($query);
$num_rows = mysql_num_rows($result);

echo $num_rows;

Secondly to get the amount of Approved ads AND the amount of all ads sold, you will need to do 2 queries.

The first one stays the same to get all approved ads.

The second is your original query

$query = "SELECT * FROM ad_order WHERE cust_id='$table_id' AND status != '99'"

Then do the same with this query to obtain the amount of ADS (Total) sold to the particular customer.