mysql shorthand

[james438]

Is there a way to use shorthand for the following:


SELECT * FROM table WHERE col != 'this' AND col != 'that'
This would be rather handy when I want to filter out some of the results.

[djr33]

I don't think so. You could use regex if you wanted but that would take longer.

[james438]

What I mean is could I replace

SELECT * FROM table WHERE col != 'this' AND col != 'that'
with something like

SELECT * FROM table WHERE col != 'this','this1','this2'
or

SELECT * FROM table WHERE col != 'this'||'this1'||'this2'
or even

SELECT * FROM table WHERE col != $array

[traq]

No. You could use PHP to write the query - makes things like this a snap, if you're doing it dynamically/ more than once.

[james438]

Bummer, I was hoping MySQL could use some shorthand similar to what PHP uses. For some of my larger scripts I do use PHP to dynamically write the queries. For my website search script the queries can be quite large and numerous depending on the terms and tables searched.

[traq]

I don't have any experience with this, but if you have access to the MySQL server, you can write stored procedures. Don't know for sure, but I'm sure something like this could be done.

[djr33]

Well, there are two ways in which (at least in theory) this would be faster:
1. Faster to write.
2. Faster to process.

For (1), you can use PHP to generate it if you'd like.
For (2), I don't think that having more code in this case actually will be slower. MySQL is very efficient as-is, so a long query isn't a problem, but potentially making a multi-layered query (eg, a stored procedure) might end up being slower since there's more going on.

So, I don't think this would really help, except saving you the effort of automating the query in PHP.

[james438]

I'm not really looking for something that is faster. I mostly was looking for a way to make the queries simpler. It would also make it a little easier to add filters to the query as new filters become needed.

This is in regards to my security monitoring script. I want to add ip addresses to the list that should not pop up in my table as potential security attacks. I have my own ip address and google's filtered out at the moment, but I am sure others will come up in the future.

[traq]

Daniel - stored procedures are actually significantly faster than similar processing in PHP in most cases, since it's native. Obviously, there are a lot of things MySQL can't do for PHP, but the tasks it can do are faster.

[djr33]

Not to distract too much from the topic, but in what way are they faster? I'm probably missing something. Generally, if the PHP is badly written, I can see that being slow, but if it's a simple scenario where you just want to get a simple search done, I'm not sure why PHP would be slow there.

[traq]

C/c++ :)

[djr33]

So it's a difference of the PHP and C, rather than the MySQL code itself? That makes sense. But I do wonder, just relative to the MySQL part (eg, the actual searching in the query), whether they run at the same speed. The PHP loop, for example, probably is slower than a C loop, but if MySQL via PHP is slower than MySQL via C, that's worth knowing.

[traq]

the loop that constructs the query would be faster, yes - the execution of the statement would be the same. MySQL is MySQL. Of course, the actual savings probably wouldn't be noticeable unless you had a _lot_ of traffic. and most web hosts don't give you enough access to write stored procedures unless you have a private server anyway.

[james438]

SELECT * FROM table WHERE col != 'this' AND col != 'that'
can be simplified as:

SELECT * FROM table WHERE col NOT IN ('this','that')

Conversely:

SELECT * FROM table WHERE col = 'this' OR col = 'that'
can be simplified as:

SELECT * FROM table WHERE col IN ('this','that')

These were the only two shorthand functions I could find. ref (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html)