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

[leroesler]

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:

$selstring = "SELECT VNDTBL.VNDCOD, VNDTBL.VSS# FROM OHPRODSQL.VNDTBL";  
$result = mysql_query($selstring);
$row = mysql_fetch_array($result);
 

Has anyone run into this, and if so, how do I fix it?

[code green]

The hash symbol denotes a comment in php and also MySQL.

[Markus]

Can you post the complete error?

[leroesler]

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?

[Markus]

Are you quite sure that table exists?

No - the hash character in a string is of no significance to the PHP interpreter.

[code green]

I don't know how MySQL will handle this, but you could try wrapping the column names, hashes and all, in backticks

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

$selstring = 'SELECT `VNDTBL`.`VNDCOD`, `VNDTBL`.`VSS#` FROM `OHPRODSQL`.`VNDTBL`';

If not you will need to read the query in from a text file

$selstring = file_get_contents(myquery.txt)

where myquery.txt contains

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

[Markus]

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.

[leroesler]

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?

[leroesler]

By the way, I had already tried using backticks, and it didn't help.

[JKing]

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.

[leroesler]

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.

[Markus]

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.

[leroesler]

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.

[Markus]

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:

<?php
 
mysql_connect( '...', '...', '...' ) or die( mysql_error() );
mysql_select_db( '...' );
$q_str = "SELECT `a`, `b#` FROM `test`";
$q_res = mysql_query( $q_str ) or die( mysql_error() );
 
var_dump( $q_str, $q_res );
 
var_dump(
    mysql_result( $q_res, 0, 0 ),
    mysql_result( $q_res, 0, 1 ) 
);
 

[leroesler]

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.