Re: [PHP] Mysql statement works in phpmyadmin but not in php page

On Thu, Feb 11, 2010 at 09:49:02PM -0600, Joseph Thayne wrote:

> I was going to write an example as to what should happen instead of what
> actually does when id dawned on me why MySQL works the way it does.  One of
> the biggest complaints people have with MySQL is in speed.  

The much-vaunted speed of MySQL is the biggest complaint? Sheesh.

> To demonstrate
> what I just realized, take the following statement that will select the hour
> from a given time as well as the value from the hour field:
> 
> SELECT HOUR('13:42:37') as thehour, hour FROM mytable;
> 
> Not a big deal and pretty straight forward.  What about the following?
> 
> SELECT HOUR(mydate) as thehour, hour FROM mytable;
> 
> Still pretty simple to determine which are the functions and which are the
> field names.  However, take the following:
> 
> SELECT HOUR(NOW()) as thehour, hour FROM mytable;
> 
> As humans, glancing at it, it makes perfect sense to us as to which is
> which.  However, try telling a computer how to interpret the above
> statement.  You could look for parenthesis.  That would work fine on the
> first two statements, but once you get to the third, you have to worry about
> recursion and all possible permutations of the data that could come through.
> This exponentially increases the complexity and processing time/power
> required to run the query.  Granted, that query is a simple one, but plug it
> into a query filled with multiple joins, and you have the potential of a
> nightmare.  So why focus on adding in functionality that adds so much
> complexity and will end up requiring that much extra support when a simple
> character (the tick mark) will take care of the work for you and you can
> then focus on other things such as data integrity and general processing
> speed?

I understand what you're saying, and you may be right about why MySQL
was built this way. However, it's like telling the programmers not to
build a better parser; just make the user backtick stuff so we don't
have to write a proper parser. For a one-off script only I was going to
use, I'd do this. But not for a professional level product used by
millions, speed or no speed. Imagine if K&R had tried to shortcut the C
parser this way; the C parser is almost endlessly re-entrant and must
accommodate some seriously obfuscated code. Which it does reliably.
Besides, if you've got a parser which understands joins, parsing things
like the distinction between hour (field name) and hour (function call)
is a piece of cake.

If a programmer working for me tried to pawn this off as a "done", I'd
make him redo it. Again, maybe it's just me.

Anyway, we're way off topic....

Paul

-- 
Paul M. Foster