CODEWITHSUNDEEP
phpmysql
user1022585
user1022585

Reputation: 13641

mysql select based on a SUM

I need to do a mysql query but Im not sure how to go about it.

I want to select rows that are greater than the php value of $x, by adding 2 of the table fields together (after dividing them).

The pseudo code:

 SELECT * FROM table WHERE (field_a/50 + field_b/20)  > $x

The $x is a php value, its just the sum i need.

Anyone have tips?

Upvotes: 2

Views: 116

Answers (2)

Doug
Doug

Reputation: 6442

It's pretty much the pseudo-code you wrote:

$x = 100; // example
$x = mysql_real_escape_string($x); // "securing" the value.

$sql = "SELECT * FROM table WHERE (field_a/50 + field_b/20)  > '$x'";

$result = mysql_query($sql);

Using parameters is more elegant, but you got the idea. This way you can mess the SQL query if $x is empty or have any not expected value. I handled this issue escaping the value using mysql_real_escape_string before using it in the query.

EDIT

The same example using MySQLi:

$stmt = $mysqli->prepare("SELECT * FROM table WHERE (field_a/50 + field_b/20) > ?");

// "i' is a format string, each "i" means integer
$stmt->bind_param("i", $x); // use "d" instead of "i" for double

$stmt->execute();
$stmt->bind_result($col1, $col2);

Upvotes: 3

Marc B
Marc B

Reputation: 360572

That's pretty much exactly how you'd go about it. Other than making sure $x is properly numeric before building the query string, you've got it.

One minor suggestion - if you do this sort of query frequently, you might want to consider cacheing the value of the field_a/field_b stuff in a separate field. It's a slight denormalization, but it would allow this field to be indexed and probably improve performance. Right now you're doing a where on a derived/calculated value, which makes it impossible to use indexes.

Upvotes: 0

Related Questions