Can I construct a query this way?

Hi guys,

Can I construct a query like this?
DELETE * FROM table WHERE column < numbervalue - number
If not, how would I accomplish something like this?

Thanks,
Tony

 

Yes, you can.
You should put the (numbervalue - number) in brackets to be sure, but I'm pretty sure it works without them.

 

Thanks! If anyone else has any input, it is welcome.

 

DELETE FROM table WHERE column < 10;

 

That's not what I meant. I meant like DELETE FROM table WHERE column < var minus var2

-Tony

 

The database will evaluate (var - var2) as a single value, so it works the same as if you said column < 10.

 

thanks
-tony

 

What if I use PHP?
Do I still have to use quotes like this:
SELECT * FROM table WHERE column = ('$foo' - '$bar')

-Tony

 

Since this is in PHP, you follow PHP rules for generating a string, not SQL rules for generating a query. So it's

$query = "SELECT * FROM table WHERE column = '".$foo - $bar."';";

This is assuming that "column" is text and $foo and $bar are numeric. If "column" is numeric, you don't quote (it's not optional - it will throw an error if you do quote), so

$query = "SELECT * FROM table WHERE column = ".$foo - $bar.";";

If $foo and $bar are non-numeric, $foo - $bar doesn't make any sense, so there's no query you can make with them unless you want to delete the string in $bar from the string in $foo - which requires string manipulation, not arithmetic.

IOW, PHP strings have to be quoted. SQL string values have to be quoted, but SQL numeric values can't be quoted. (You can use " or ' in PHP to quote a string. In "", a variable will be evaluated, and replaced with its value. In '', variables will be the literal string name of the variable, i.e., $foo will be $foo, not the value of $foo. If you want ' as a character in the string, use " as the quotation mark for the string. "'" is a string containing one single quote.)

All the PHP stuff about string quoting is in the PHP manual at http://php.net/manual/en/language.types.string.php

(For SQL quoting, see any SQL tutorial.)

 

THIS REFERS TO COLUMN AND VALUES AS NUMERIC!!!
If they are ALL numeric, I can just do this, I guess:
$val = $foo-$bar;
mysql_query("SELECT * FROM table WHERE column > $val");

I suppose this question didn't need to be asked, the fix is one extra line of code.
-Tony