faster mysql query

Question

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!

Wolph · Accepted Answer · 2011-01-25 01:35:57Z

1

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

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

answered Jan 25, 2011 at 1:35

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Larry Lustig · Accepted Answer · 2011-01-25 01:37:50Z

0

 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.

answered Jan 25, 2011 at 1:37

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

5 Comments

Alfie Over a year ago

This is what I'm after thank you, could you specify the parameterized query issue please?

Larry Lustig Over a year ago

If, for instance, the $type field comes from user input and the user were to type "13; DELETE FROM Users" in that field, you can see what would happen. By specifying the query with parameters (something like WHERE ID = @id AND type = @type, but it depends on your database library) and then binding properly typed values to the parameters you are safe against this kind of attack.

Dan Grossman Over a year ago

He would get an error, but nothing would be deleted. mysql_query() will not execute more than one query.

Larry Lustig Over a year ago

@Dan: Obviously, I'm not a php expert. Is mysql_query injection-proof?

Dan Grossman Over a year ago

You can still alter the query in malicious ways (set $type to "something OR 1=1"?), you just can't inject a second query to run.

miqbal · Accepted Answer · 2011-01-25 01:40:18Z

0

$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;

answered Jan 25, 2011 at 1:40

miqbal

2,2373 gold badges28 silver badges35 bronze badges

Comments

miku · Accepted Answer · 2011-01-25 01:42:13Z

0

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

edited Jan 25, 2011 at 1:42

answered Jan 25, 2011 at 1:36

miku

189k47 gold badges314 silver badges317 bronze badges

2 Comments

zerkms Over a year ago

COUNT(*) is slightly better (since it doesn't rely on the field data, as it is for COUNT(id))

Alfie Over a year ago

No way to JOIN the two queries then?

Dan Grossman · Accepted Answer · 2011-01-25 01:45:13Z

0

One line:

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

answered Jan 25, 2011 at 1:45

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Collectives™ on Stack Overflow

faster mysql query

5 Answers 5

Comments

5 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related