469,362 Members | 2,548 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,362 developers. It's quick & easy.

How to query mysql for data that has "#" in the string?

When I do an select statement in PHP for mysql that contains fields that end in a # sign, I receive an error that indicates that it can't find the table and I receive no results. If I remove the offending field, everything works perfectly:

Expand|Select|Wrap|Line Numbers
  1. $selstring = "SELECT VNDTBL.VNDCOD, VNDTBL.VSS# FROM OHPRODSQL.VNDTBL";  
  2. $result = mysql_query($selstring);
  3. $row = mysql_fetch_array($result);
  4.  
Has anyone run into this, and if so, how do I fix it?
Feb 4 '11 #1
14 8700
code green
1,726 Expert 1GB
The hash symbol denotes a comment in php and also MySQL.
Feb 4 '11 #2
Markus
6,050 Expert 4TB
Can you post the complete error?
Feb 4 '11 #3
The error I receive is: ErrorUnknown table 'VNDTBL' in field list

This error makes sense if # is terminating the select clause and commenting out the rest of the clause. There would be no table name specified. Several of my tables have fields with #'s in them. Is there any way to accommodate this problem, short of creating a view and renaming the field?
Feb 4 '11 #4
Markus
6,050 Expert 4TB
Are you quite sure that table exists?

No - the hash character in a string is of no significance to the PHP interpreter.
Feb 4 '11 #5
code green
1,726 Expert 1GB
I don't know how MySQL will handle this, but you could try wrapping the column names, hashes and all, in backticks
Expand|Select|Wrap|Line Numbers
  1. SELECT `VNDTBL`.`VNDCOD`, `VNDTBL`.`VSS#` FROM `OHPRODSQL`.`VNDTBL`
I know this helps with spaces.
However, I if the PHP code still breaks, maybe single quotes will work
Expand|Select|Wrap|Line Numbers
  1. $selstring = 'SELECT `VNDTBL`.`VNDCOD`, `VNDTBL`.`VSS#` FROM `OHPRODSQL`.`VNDTBL`';
If not you will need to read the query in from a text file
Expand|Select|Wrap|Line Numbers
  1. $selstring = file_get_contents(myquery.txt)
where myquery.txt contains
Expand|Select|Wrap|Line Numbers
  1. SELECT `VNDTBL`.`VNDCOD`, `VNDTBL`.`VSS#` FROM `OHPRODSQL`.`VNDTBL`
On one line. I recommend this as good practice as queries are then seperated from PHP code
Feb 4 '11 #6
Markus
6,050 Expert 4TB
Naughty, code green! I was just about to reply to you, but you edited your post. You removed the part I was going to quote, so nevermind.
Feb 4 '11 #7
Thanks for your responses. It looks like I have two options, use a text file to contain the select string, or use a view to rename the field. If I use a view, will this affect mysql optimization capabilities regarding selecting the most appropriate keyed path to retrieve the data?
Feb 4 '11 #8
By the way, I had already tried using backticks, and it didn't help.
Feb 4 '11 #9
JKing
1,206 Expert 1GB
What version of MySQL are you using?

I just tested this out by adding a # to the end of a field in a test database I have on my local machine.

I wrote a small script to run a select using that field name. All results were returned with no errors.

So are you positive that your field and table names are correct?

Another possible option if performance isn't an issue, is to use the wildcard to select all fields in the table and then only use the ones you want in php.
Feb 4 '11 #10
What I have observed is that using a mysql query editor, I can use the ` mark to surround the field with the # sign, and the query works. PHP is another matter. It seems to honor the # for commenting no matter where it falls. What I ended up doing is to create a view and rename the fields containing a # sign. This works for PHP. I didn't want to get into referencing an external file for the select clause. Carefully crafted "replace all" commands took care of the text changes in the PHP code.
Feb 4 '11 #11
Markus
6,050 Expert 4TB
No. It absolutely does not. As I said before, the hash character serves absolutely no purpose other than its literal representation when in a string. The problem lies elsewhere. I fail to understand how loading this string via an 'external file' would alleviate the issue, either.
Feb 5 '11 #12
Perhaps it is the version of PHP I am running. It is 5.3.2 All I can say is that when I have fields in the select clause in my php code that have a # sign, the rest of the string is treated as a comment. When I take out the offending fields, everything works as it should. I created a view with the offending fields renamed and used it in the php code and added back the renamed fields. Everything works as it should. By the way, if I use * in the select clause instead of specific fields, it also works. Of course, for an insert, you can't use *, and again, it fails with the # fields in the clause. It must be that, at least in my version of php, the interpreter sees the # as the start of a comment, even inside a select clause string. If someone is using a version of PHP that doesn't act this way, I'd love to know which it is.
Feb 5 '11 #13
Markus
6,050 Expert 4TB
I don't know what's causing your issue, but I'm near-enough certain that it's not being picked up as a comment. The following works for me on 5.3.2:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. mysql_connect( '...', '...', '...' ) or die( mysql_error() );
  4. mysql_select_db( '...' );
  5. $q_str = "SELECT `a`, `b#` FROM `test`";
  6. $q_res = mysql_query( $q_str ) or die( mysql_error() );
  7.  
  8. var_dump( $q_str, $q_res );
  9.  
  10. var_dump(
  11.     mysql_result( $q_res, 0, 0 ),
  12.     mysql_result( $q_res, 0, 1 ) 
  13. );
  14.  
Feb 5 '11 #14
Thanks much for your response. I copied your code exactly, inserting my database and file info, and you're right, it does run. If you don't use the backticks, it fails as usual. My code has each field name conditioned with the file name, and when I tried using the backticks, I made the mistake of enclosing the whole thing, tablename.fieldname. This failed. I see that you backticked all the fields and table name. Nearly all of the code I've worked with does not do this. I did notice that PHP doesn't seem to care if some fields are backticked, and some are not. Again, thanks for your patience, and I will use backticks (just around the field name) for fields that have # signs in them.
Feb 7 '11 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Michael Flanagan | last post: by
1 post views Thread by Simon Wigzell | last post: by
10 posts views Thread by Andres Eduardo Hernando | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.