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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql statement works in phpmyadmin but not in php page
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:pa...@quillandmouse.com]
Sent: Thursday, February 11, 2010 9:15 PM
To: php-general@lists.php.net
Subject: Re: [PHP] 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
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
Re: [PHP] 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 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
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Yeah, I am a lot more descriptive now. I ran into it quite a bit when I was first starting out. James McLean wrote: On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne 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. Ahh I see :) Wasn't aware of that. Personally i've always been over-descriptive when designing my tables which is possibly why I've never run into that limitation :) Thanks.
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne 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. Ahh I see :) Wasn't aware of that. Personally i've always been over-descriptive when designing my tables which is possibly why I've never run into that limitation :) Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 9:31 AM, Jochem Maas wrote:
> Op 2/11/10 10:51 PM, James McLean schreef:
>> My personal preference these days is to use Curly braces around
>> variables in strings such as this, I always find excessive string
>> concatenation such as is often used when building SQL queries hard to
>> read, and IIRC there was performance implications to it as well
>> (though I don't have access to concrete stats right now).
>>
>> In your case, the variable would be something like this:
>>
>> $query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
>> ({$v_id}, {$hour}, {$visits}, '{$date}')";
>
> actually IIRC the engine compiles that to OpCodes that equate to:
>
> $query = 'INSERT INTO upload_history (v_id,hour,visits,date) VALUES
> ('.$v_id.', '.$hour.', '.$visits.', '\''.{$date}.'\')';
Interesting point, but the original code is still more readable, the
opcode's aren't our problem (at least in this case) :)
Cheers
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Op 2/11/10 10:51 PM, James McLean schreef:
> On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne wrote:
>>
>> Actually, the syntax is just fine. I personally would prefer it the way you
>> mention, but there actually is nothing wrong with the syntax.
>>
>>> The ,'$date1'"." is not correct syntax, change it to ,'".$date."'
>
> My personal preference these days is to use Curly braces around
> variables in strings such as this, I always find excessive string
> concatenation such as is often used when building SQL queries hard to
> read, and IIRC there was performance implications to it as well
> (though I don't have access to concrete stats right now).
>
> In your case, the variable would be something like this:
>
> $query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
> ({$v_id}, {$hour}, {$visits}, '{$date}')";
actually IIRC the engine compiles that to OpCodes that equate to:
$query = 'INSERT INTO upload_history (v_id,hour,visits,date) VALUES ('.$v_id.',
'.$hour.', '.$visits.', '\''.{$date}.'\')';
>
> Much more readable and maintainable IMO.
>
> No need for the trailing semicolon in SQL that uses an API like you
> are using so save another char there too.
> Backticks around column names are not required and IMO again they just
> make the code hard to read. Just because phpMyAdmin uses them, doesn't
> mean we all need to.
>
> Cheers
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
That is a good idea to use the curly braces. I consistently forget
about them, and fell like an idiot every time I am reminded of them.
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.
James McLean wrote:
On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne wrote:
Actually, the syntax is just fine. I personally would prefer it the way you
mention, but there actually is nothing wrong with the syntax.
The ,'$date1'"." is not correct syntax, change it to ,'".$date."'
My personal preference these days is to use Curly braces around
variables in strings such as this, I always find excessive string
concatenation such as is often used when building SQL queries hard to
read, and IIRC there was performance implications to it as well
(though I don't have access to concrete stats right now).
In your case, the variable would be something like this:
$query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
({$v_id}, {$hour}, {$visits}, '{$date}')";
Much more readable and maintainable IMO.
No need for the trailing semicolon in SQL that uses an API like you
are using so save another char there too.
Backticks around column names are not required and IMO again they just
make the code hard to read. Just because phpMyAdmin uses them, doesn't
mean we all need to.
Cheers
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne wrote:
>
> Actually, the syntax is just fine. I personally would prefer it the way you
> mention, but there actually is nothing wrong with the syntax.
>
>> The ,'$date1'"." is not correct syntax, change it to ,'".$date."'
My personal preference these days is to use Curly braces around
variables in strings such as this, I always find excessive string
concatenation such as is often used when building SQL queries hard to
read, and IIRC there was performance implications to it as well
(though I don't have access to concrete stats right now).
In your case, the variable would be something like this:
$query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
({$v_id}, {$hour}, {$visits}, '{$date}')";
Much more readable and maintainable IMO.
No need for the trailing semicolon in SQL that uses an API like you
are using so save another char there too.
Backticks around column names are not required and IMO again they just
make the code hard to read. Just because phpMyAdmin uses them, doesn't
mean we all need to.
Cheers
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Actually, the syntax is just fine. I personally would prefer it the way you mention, but there actually is nothing wrong with the syntax. The ,'$date1'"." is not correct syntax, change it to ,'".$date."' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Thank you.
You were right on the money, "hour" was the problem and the tick marks
solved it. I spent 3 hours trying to figure out why I never got an error but
there was no insert and php myadmin does add the tick marks automatically.
Probably a good habit to always use the tick marks.
Learn something new everyday.
On Thu, Feb 11, 2010 at 4:26 PM, Joseph Thayne wrote:
> Try putting tick marks (`) around the field and table names. So your SQL
> query would then look like:
>
>
> INSERT INTO `history` (`v_id`, `hour`, `visits`, `date`) VALUES (45, 0, 59,
> '2010 01 27');
>
> This is a good practice to get into. The problem is that MySQL allows you
> to create tables and fields with the same name as functions. If the tick
> marks are not there, then it assumes you mean to try using the function. In
> your case, hour is a function in mysql. I would assume that the reason it
> works in phpmyadmin is that it filters the query somehow to add the tick
> marks in.
>
> Joseph
>
>
> james stojan wrote:
>
>> I'm at my wits end trying to make this mysql statement insert work in
>> PHP. I'm not getting any errors from PHP or mysql but the insert fails
>> (nothing is inserted) error reporting is on and is reporting other
>> errors. When I echo out the query and manually paste it into PHP
>> myAdmin the query inserts without a problem. I know that I am
>> connecting to the database as well part of the data being inserted
>> comes from the same database and that the mysql user has permission to
>> do inserts (even tried as root no luck).
>>
>> $query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
>> (".$v_id.",".$hour.",".$visits.",'$date1'".");";
>>
>> $r2=mysql_query($query) or die("A fatal MySQL error
>> occured.\nQuery: " . $query . "\nError: (" .
>> mysql_errno() . ") " . mysql_error());
>>
>> This is an echo of $query and runs in phpmyadmin.
>>
>> INSERT INTO history (v_id,hour,visits,date) VALUES (45,0,59,'2010 01 27');
>>
>>
>> Any idea what is going on here?
>>
>>
>>
>
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Also, in PHP you should NOT put the last semi-colon at the end of your SQL
statement. http://www.php.net/manual/en/function.mysql-query.php
On Feb 11, 2010, at 1:26 PM, Joseph Thayne wrote:
> Try putting tick marks (`) around the field and table names. So your SQL
> query would then look like:
>
> INSERT INTO `history` (`v_id`, `hour`, `visits`, `date`) VALUES (45, 0, 59,
> '2010 01 27');
>
> This is a good practice to get into. The problem is that MySQL allows you to
> create tables and fields with the same name as functions. If the tick marks
> are not there, then it assumes you mean to try using the function. In your
> case, hour is a function in mysql. I would assume that the reason it works
> in phpmyadmin is that it filters the query somehow to add the tick marks in.
>
> Joseph
>
> james stojan wrote:
>> I'm at my wits end trying to make this mysql statement insert work in
>> PHP. I'm not getting any errors from PHP or mysql but the insert fails
>> (nothing is inserted) error reporting is on and is reporting other
>> errors. When I echo out the query and manually paste it into PHP
>> myAdmin the query inserts without a problem. I know that I am
>> connecting to the database as well part of the data being inserted
>> comes from the same database and that the mysql user has permission to
>> do inserts (even tried as root no luck).
>>
>> $query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
>> (".$v_id.",".$hour.",".$visits.",'$date1'".");";
>>
>> $r2=mysql_query($query) or die("A fatal MySQL error
>> occured.\nQuery: " . $query . "\nError: (" .
>> mysql_errno() . ") " . mysql_error());
>>
>> This is an echo of $query and runs in phpmyadmin.
>>
>> INSERT INTO history (v_id,hour,visits,date) VALUES (45,0,59,'2010 01 27');
>>
>>
>> Any idea what is going on here?
>>
>>
>
> --
> 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
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
james stojan wrote on 11/02/2010 22:21:
$query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
(".$v_id.",".$hour.",".$visits.",'$date1'".");";
The ,'$date1'"." is not correct syntax, change it to ,'".$date."'
--
Kind regards
Kim Emax - masterminds.dk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Try putting tick marks (`) around the field and table names. So your
SQL query would then look like:
INSERT INTO `history` (`v_id`, `hour`, `visits`, `date`) VALUES (45, 0,
59, '2010 01 27');
This is a good practice to get into. The problem is that MySQL allows
you to create tables and fields with the same name as functions. If the
tick marks are not there, then it assumes you mean to try using the
function. In your case, hour is a function in mysql. I would assume
that the reason it works in phpmyadmin is that it filters the query
somehow to add the tick marks in.
Joseph
james stojan wrote:
I'm at my wits end trying to make this mysql statement insert work in
PHP. I'm not getting any errors from PHP or mysql but the insert fails
(nothing is inserted) error reporting is on and is reporting other
errors. When I echo out the query and manually paste it into PHP
myAdmin the query inserts without a problem. I know that I am
connecting to the database as well part of the data being inserted
comes from the same database and that the mysql user has permission to
do inserts (even tried as root no luck).
$query="INSERT INTO upload_history (v_id,hour,visits,date) VALUES
(".$v_id.",".$hour.",".$visits.",'$date1'".");";
$r2=mysql_query($query) or die("A fatal MySQL error
occured.\nQuery: " . $query . "\nError: (" .
mysql_errno() . ") " . mysql_error());
This is an echo of $query and runs in phpmyadmin.
INSERT INTO history (v_id,hour,visits,date) VALUES (45,0,59,'2010 01 27');
Any idea what is going on here?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
