Page 1 of 1

Sql Query with PHP, need Help

Posted: Mon Jul 28, 2003 11:51 pm
by DougieC
Hey thanks for taking a look, I am trying to to access data through a form and i am incountering a couple problems. This is the error i am getting:

"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 'SELECT sa.AgencyID FROM AgencyAreas aa"

here is the code for the query:

$result = mysql_query("SELECT ag.AgencyID, ag.CompanyName, ag.Address, ag.StateOrProvince, ag.City, ag.PostalCode
FROM Agencies ag
WHERE ag.StateOrProvince ='$province'

AND ag.AgencyID IN ( SELECT sa.AgencyID
FROM AgencyAreas aa
INNER JOIN Areas ar ON (aa.AreaServedID=
ar.AreaServedID)
WHERE a.AreaServed LIKE '%$city')

AND ag.AgencyID IN ( SELECT as.AgencyID
FROM AgencyServices
WHERE ServiceID IN ($services))"
) or die("Invalid query: " . mysql_error() );
// $services is a check box array

what i am trying to do is to get the relevent information based on the area the agency served based on a text field search and services provided based on a check box query. I hope this makes sense, if not sorry my brain is mush right now. Thanks again

Posted: Tue Jul 29, 2003 12:09 am
by tylerdurden
MySQL doesn't support nested queries.

Posted: Tue Jul 29, 2003 6:51 am
by twigletmac
tylerdurden wrote:MySQL doesn't support nested queries.
Unless you're running MySQL 4.1.

Mac

Posted: Tue Jul 29, 2003 8:31 am
by DougieC
I am running the latest version

Posted: Tue Jul 29, 2003 9:12 am
by twigletmac
DougieC wrote:I am running the latest version
4.1?

Mac

Posted: Tue Jul 29, 2003 10:31 am
by DougieC
sorry i'm running 4.0 :? with nested queries do you mean for example a query on city for a agency that supplies long term care. So I would be querying for a agency in a particular city that supplied longterm care?

Posted: Wed Jul 30, 2003 3:51 am
by twigletmac
By nested queries he means sub-selects - these just aren't supported by MySQL yet.

Mac

brackets

Posted: Wed Jul 30, 2003 8:53 am
by Whil
from [ag agencies]
if you have 2 words (spaces) you normally have to have the table / field name in [] ...
hope this helps you out :).

Re: brackets

Posted: Wed Jul 30, 2003 9:56 am
by twigletmac
Whil wrote:from [ag agencies]
if you have 2 words (spaces) you normally have to have the table / field name in [] ...
hope this helps you out :).
Not if you're setting aliases -

Code: Select all

FROM Agencies ag
is the same as doing

Code: Select all

FROM Agencies AS ag
Mac