$db->query() not accepting this mysql query?

[twick100]

This is the query

$query =
"SELECT f.name fname, f.state, f.city, f.id fid, cl.alt_asd_ida, cl.alt_asdd_idb, c.ave_amount, c.asd, c.name, cc.first_name, cc.last_name, cc.id cid, cc.phone_work
FROM alt_facilities f
INNER JOIN alt_facchem_c cl ON (f.id = cl.alt_facit_ida)
INNER JOIN alt_chemicals c ON (cl.alt_fasad_idb = c.id)
LEFT OUTER JOIN alt_f2334_c con ON (f.id = con.alt_56_ida)
LEFT OUTER JOIN contacts cc ON (con.alt_asdds_idb = cc.id)
WHERE c.name = 'lead'
AND f.state IN ('AL','FL')
ORDER BY fname;";

$db = DBManagerFactory::getInstance();

$result = $db->query($query);

The problem is not with the query, I know this much for sure. I can run this exact query in a terminal for the database outside of PHP and it works perfect. When I run it via PHP with the DB class, it doesn't return anything.

I know the problem has to do the with "IN" statement because it stopped working when I added that.

[gauts]

Hi

did you try to echo $db->last_error;

or did you check you log file, mysql errors should be logged.

And in you query you wrote
ORDER BY fname

it should be
ORDER BY f.name

but maybe this is just when you've put this in the forum.

hope this can help

[sts]

If you are sure that the IN statement is problematic (or to test if this is the case) you could use 'or' instead (at least if your IN does not contain too much alternatives ...):
AND f.state IN ('AL','FL')
AND (f.state = 'AL' OR f.state = 'FL')

[snikwahj]

...
ORDER BY fname;";
...

If the query you posted has been copy & pasted from your code as above then the error is probably that you have the semi-colon included at the end of it (between fname and the closing double quote of the string). You do not need to include the semi colon in the query string when calling a query in the bean. You only need that when you are running a SQL query in the terminal window.

The query string in your code should look like this:

 $query =
"SELECT f.name fname, f.state, f.city, f.id fid, cl.alt_asd_ida, cl.alt_asdd_idb, c.ave_amount, c.asd, c.name, cc.first_name, cc.last_name, cc.id cid, cc.phone_work
FROM alt_facilities f
INNER JOIN alt_facchem_c cl ON (f.id = cl.alt_facit_ida)
INNER JOIN alt_chemicals c ON (cl.alt_fasad_idb = c.id)
LEFT OUTER JOIN alt_f2334_c con ON (f.id = con.alt_56_ida)
LEFT OUTER JOIN contacts cc ON (con.alt_asdds_idb = cc.id)
WHERE c.name = 'lead'
AND f.state IN ('AL','FL')
ORDER BY fname"; 
  

Regards,

JH.

[rafael.q.g@hotmail.com]

You already tried to run this query directly on databaser sever to check whether code sintax is been interpretted correctly? Maybe there is easy to debug these issues.

[twick100]

Sorry for my late response. I figured it out. I was getting the single quotes from the post data. Instead I added them in server side. I assume there is some sort of security feature that prevented the quotes from posting with proper encoding.