php and mysql help with multiple tables

[ericjw316]

Below you will find the mysql code. I can view the data when i look at it via mysql but when i go to php i am unable to view the data.

Here is the error i am getting when i try to view the code in php:

MySQL said: Error #1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM clients, users, agents_action, clients_agents_rel WHERE clients.id = client' at line 1


Can you help me out with this.

SELECT users.full_name, users.user_email, clients.bus_name, clients.contact_fname, clients.contact_lname, clients.bus_phone, agents_action.setup_date, agents_action.merchant, agents_action.price_mo, agents_action.setup_fee, agents_action.source FROM clients, users, agents_action, clients_agents_rel WHERE clients.id = clients_agents_rel.client_id AND users.id = clients_agents_rel.agent_id

Thank you,

ericjw316

[penders]

Your SQL looks syntactically OK to me.

Are you able to execute other queries OK?

May be you could post the PHP where you are building and executing this statement. Is the SQL posted above the exact string you are passing to MySQL via PHP?

[rocknbil]

It looks syntactically correct but I don't see any join on agents_action.

[ericjw316]

I am new to the join part of sql i have only had to check for data in one database. when i run the query in mysql it runs fine and displays my data.

It is when i take it to php that i get blank data but the correct number of lines display.

[rocknbil]

Well, you said it errored?

Right number of rows? . . . then it's how you're pulling it from the rows in PHP. When you join you get

users.full_name|users.user_email
John Doe|[e-mail]

And you're likely doing

$fname = $row['full_name'];

where (I think, I use as) you would need to do is

$fname = $row['users.full_name'];

Easier to use the as keyword:

SELECT users.full_name as full_name, users.user_email as email, clients.bus_name as bus_name, clients.contact_fname, as cfname, clients.contact_lname as clname ....

Then you can use the single reference in the array.

$fname = $row['full_name'];
$email = $row['email'];
(etc.)

[Globetrotter]

You could also try to var_dump() the result you are getting. This way you can see if you get any results at all and as stated above are parsing the results the wrong way.