Sql Query with PHP, need Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
DougieC
Forum Newbie
Posts: 18
Joined: Fri Jul 25, 2003 1:49 pm

Sql Query with PHP, need Help

Post 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
tylerdurden
Forum Commoner
Posts: 66
Joined: Mon Jul 28, 2003 11:52 am
Location: Austria

Post by tylerdurden »

MySQL doesn't support nested queries.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

tylerdurden wrote:MySQL doesn't support nested queries.
Unless you're running MySQL 4.1.

Mac
DougieC
Forum Newbie
Posts: 18
Joined: Fri Jul 25, 2003 1:49 pm

Post by DougieC »

I am running the latest version
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

DougieC wrote:I am running the latest version
4.1?

Mac
DougieC
Forum Newbie
Posts: 18
Joined: Fri Jul 25, 2003 1:49 pm

Post 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?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

By nested queries he means sub-selects - these just aren't supported by MySQL yet.

Mac
Whil
Forum Newbie
Posts: 8
Joined: Wed Jul 30, 2003 8:50 am
Location: USA

brackets

Post 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 :).
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Re: brackets

Post 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
Post Reply