CODEWITHSUNDEEP
phpmysql
Alfie
Alfie

Reputation: 287

faster mysql query

Is there a faster way to do this?

$data1 = mysql_query(
 "SELECT * FROM table1 WHERE id='$id' AND type='$type'"
) or die(mysql_error()); 

$num_results = mysql_num_rows($data1); 
$data2 = mysql_query(
 "SELECT sum(type) as total_type FROM table1 WHERE id='$id' AND type='$type'"
) or die(mysql_error()); 

while($info = mysql_fetch_array( $data2 )){
    $count = $info['total_type'];
} 
$total = number_format(($count/$num_results), 2, ',', ' ');
echo $total;

Cheers!

Upvotes: 0

Views: 194

Answers (5)

Dan Grossman
Dan Grossman

Reputation: 52372

One line:

echo number_format(mysql_result(mysql_query("SELECT SUM(type) / COUNT(*) FROM table1 WHRE id = $id AND type = '$type'"), 0), 2, ',', ' ');

Upvotes: 0

miku
miku

Reputation: 187994

In general: SELECT * can be 'shortened' to e.g. SELECT COUNT(*), if all you care about is the number of matching rows.

Upvotes: 0

miqbal
miqbal

Reputation: 2227

$data1 = mysql_query("SELECT sum(type) as total_type,count(*) as num_rows FROM table1 WHERE id='$id' AND type='$type'"
) or die(mysql_error()); 
$info = mysql_fetch_array( $data1 );
$count = $info['total_type'];
$num_results = $info['num_rows'];
$total = ($count/$num_results); 
echo $total;

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

 SELECT COUNT(*) AS num_results, SUM(type) AS total_type FROM table1
    WHERE id = $id and type = $type

This single query will produce a one-row result set with both values that you want.

Note that you should use a parameterized query instead of direct variable substitution to avoid SQL injection attacks.

Also, I'm guessing that SUM(type) isn't what you really want to do, since you could calculate it as (num_results * $type) without the second query.

Upvotes: 0

Wolph
Wolph

Reputation: 80011

Looking at your queries, I think you're looking for something like this:

SELECT SUM(type) / COUNT(*) FROM table1 WHERE ...

Upvotes: 1

Related Questions