MySQL / PHP

If this is the wrong forum to ask this question, I apologize, but man am I frustrated at the moment.

I am using PHP 4.4.

I am trying to execute the following query:

SELECT * FROM victim_assistance WHERE defendant_last_name LIKE "%hicks%" AND (closed <> "Y" OR closed IS NULL) ORDER BY victim_last_name, victim_first_name

and when I run the script I get an "Unknown column ’defendant_last_name’ in where clause" error.

Things to know:

(1) there are several columns that if queried, produce the same error;
(2) the columns do, indeed, exist and spelled correctly;
(3) if the query is pasted directly into a mysql terminal session, it executes with no errors.

So, can anyone offer some insight as to why PHP doesn’t like the query ... hopefully before I pull out ALL my hair?

Thanks in advance for all your help.

Are these tables in the same database as your MODx tables? If not, you’ll need to establish a connection to the second database before querying it. Are you doing that already?

Yes, same directory.

Directories (physical filesystem locations) and tables are not the same thing ... if that’s possibly part of the confusion.

no, that would not be the issue in this case.

SOME columns work fine with the query and others do not. I have googled this issue and the problem seems to usually occur with LEFT JOINs and PHP 5. Other forums I have read say the problem exists, but have never found a ’cure’.

If you look in the backup tool in the MODx Manager do you see these tables? If not, then MODx is not recognizing them as part of the same database (which would be a MySQL/database setup issue, not a MODx issue). There are lots of reasons why a table wouldn’t be selectable, so I don’t think you can Google something that broad and expect useful results.

I apologize for any confusion here ... I’m not talking about TABLE scope ... I can query certain columns from the table ... but there are other columns within the table which turn up an Unknown Column error. The table itself is being found and certain columns within that table are being found where other columns do not (although they really are there)

The only thing that occurs to me is that perhaps your character encodings are different and some characters (perhaps the underscores) are being altered when passing the query from PHP to MySQL...?

Thanks ZAP,

yeah, I thought about that, but one of the columns that does not work is a one word column ’judge’

what I am doing is I have a main screen for data entry ... for my search screen i am just display the main screen and then building my query off of whatever fields contain data from that screen, but when I go to save the data, if a user as placed data in certain fields, I get an Unknown Column error ... although, when the user uses the same form to post a new record, no problem is encountered.

Weird problem.

Do you have PHPMyAdmin or something similar installed, and does it work properly?