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. 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?
Joseph
-----Original Message-----
From: Paul M Foster [mailto:paulf@xxxxxxxxxxxxxxxxx]
Sent: Thursday, February 11, 2010 9:15 PM
To: php-general@xxxxxxxxxxxxx
Subject: Re: Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 09:44:47AM +1030, James McLean wrote:
> On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne <webadmin@xxxxxxxxxxxxx>
wrote:
> > As for the backticks, they are required because of MySQL, not because of
> > phpMyAdmin. The issue was not that phpMyAdmin uses backticks, it is
that
> > MySQL pretty much requires them when naming a field the same as an
internal
> > function to my knowledge. If someone else knows of another way to
designate
> > to MySQL that a field named HOUR is the name of a field rather than the
name
> > of the internal function, I would love to know.
Backticks are also required to preserve casing in MySQL, if you name
something in mixed or upper case; MySQL lowercases table and field names
otherwise. It's a silly misfeature of MySQL.
I can't conceive of why a DBMS would assume something which should be
understood in the context of a field name should instead be interpreted
as a function call. Buy maybe that's just me.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
