I've been testing a hack I've been working on. At one point during the testing, I received a MySQL error message stating there was a problem with a query. It said what the query was, but as you've probably noticed in these error messages, it only gives a line number of '1'. The problem here was that I had four of the exact same query throughout the code, and I couldn't tell which one was causing the problem.

The specific problem was that in the WHERE part, it was written like "WHERE field=$variable", and $variable didn't have a value. The problem really wasn't the query itself, it was the code before it that was supposed to generate a value for $variable.

So the problem was how to quickly find out which of the four identical queries had some bad code before it. Then I realized how to do it. In the first query, I put a "and 1=1" at the end of the WHERE part, so it was like "WHERE field=$variable and 1=1". For the next one, I did 2=2, and so on, for all four.

Because 1 always equals 1, putting that bit in has absolutely no effect whatsoever on how the code or query works. Basically, you're putting it there as a marker.

This way, when the error message showed up and the query was quoted, it would include the marker (the "and x=x" part), and that would show me which query had the bad code before it.

Maybe this was basic or nothing new for a lot of you, but I'm still kind of new at this, and I'm glad I figured it out before I had to carefully examine all the code I would have had to look at, because that would have been a pain in my ass. :) But if this helps even just one of you, it was worth it for me to post this.

Doesn't the MySQL optimizer removes stuff like 1=1 from queries?

Aparently not, because when I got the error message again, there it was, 4=4. :)

Maybe it SKIPS processing it, but it at least passes it through so it can get into an error message.

No it does remove that from the query, but only when it hits the MySQL server. In explain=1 it shows what PHP gets. :)

Well, whatever...it doesn't really matter if that part of the query gets executed or not. The important thing is that it shows up in the error message, which is the point. I stuck it there as a marker to show me which of four identical queries was causing a problem. Each had something different, so I could tell which one was the bad one. As long as it shows up in the error message, that's all that's important. And it does show up.

Hmm,

if I have a problem with a mysql query I always do an echo of the query on the line before I run it.

If I can't figgure out what the problem is then (if the query seems right) I copy and paste it into phpMyAdmin and run it there.

If it works there, then something else is wrong :)

- draz Q.

Those are good tips, but you need to know first which query is giving you problems before you can put an echo before it, or run it in phpMyAdmin. My tip helps you determine which query is the one that's giving you trouble. After you determine that, THEN you can do the stuff that you said.

ummm is there MySqlOptizer for Vb availavble?

*sigh*

People found the archive through that post in the 'Archive the vB2 forums?' thread and are now resurrecting long dead threads. Marvellous.

To beat the dead horse a little more...no. The query optimizer is a part of the MySQL server it's self, and thus any query passed to it is run through the optimizer first. It doesn't matter if it's a query vBulletin executes through PHP, from the mysql tool in SSH, or the C++ interface.