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