472,352 Members | 1,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,352 software developers and data experts.

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
  2. $result = mysql_query($selstring);
  3. $row = mysql_fetch_array($result);
Has anyone run into this, and if so, how do I fix it?
Feb 4 '11 #1
14 9356
code green
1,726 Expert 1GB
The hash symbol denotes a comment in php and also MySQL.
Feb 4 '11 #2
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
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
I know this helps with spaces.
However, I if the PHP code still breaks, maybe single quotes will work
Expand|Select|Wrap|Line Numbers
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
On one line. I recommend this as good practice as queries are then seperated from PHP code
Feb 4 '11 #6
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
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
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
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
  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() );
  8. var_dump( $q_str, $q_res );
  10. var_dump(
  11.     mysql_result( $q_res, 0, 0 ),
  12.     mysql_result( $q_res, 0, 1 ) 
  13. );
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

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

Similar topics

by: Michael Flanagan | last post by:
(Bottom line: I think what I'm looking for is an easy way of changing the case of key values in an array.) I've got code that I'm trying to make...
by: Simon Wigzell | last post by:
Does anyone have a definitive list of keywords to avoid or prohibit as field names for the tables for an ASP driven program that communicates with a...
by: Andres Eduardo Hernando | last post by:
Hi, I'm not entirely sure this is the right group to ask this question, but I saw a similar one above, and the group's charter is not clear enough...
by: HD | last post by:
Hello. For the following, I would appreciate if anyone could tell me: if it can be done, how it might done, and/or what search terms I could use...
by: Ralph | last post by:
I'm trying to import a range of cells from an Excel spreadsheet into a table in access. The first row contains column labels, but I cannot use...
by: kathyburke40 | last post by:
Odd problem. I have a table in the following format: DocID Question1 Question2 Question3...
by: Oliver Gabriel | last post by:
Hi, i want to export a table for later import, using vba. That´s my code: export: filename = "C:\HVOtabelle.txt"
by: Billy | last post by:
I do a SELECT * from table command in an ASP page to build a text file out on our server, but the export is not to allow a field name rows of...
by: | last post by:
Given an XML file (dataset.writexml), here is my output (simplified for this posting): <?xml version="1.0" standalone="yes"?> <NewDataSet>...
by: Wayne | last post by:
I have been given the task of rewriting a database that seems as though it has been written by someone with a very basic understanding of Access....
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.