using an array in a query string

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

using an array in a query string

Post by DougieC »

I am useing a for loop to build a query string to pass to mysql(the values are checked off in check boxes in a different form)It seems to work when there is only one check box selected. Here is my code:

Code: Select all

$sql = "SELECT DISTINCT ag.AgencyID, ag.CompanyName, ag.Address, ag.StateOrProvince, ag.City, ag.PostalCode
                        FROM Agencies ag

                        INNER JOIN AgencyAreas aa ON (ag.AgencyID = aa.AgencyID)
                        INNER JOIN Areas ar ON (aa.AreaServedID = ar.AreaServedID)

                        INNER JOIN AgencyServices ags ON (ag.AgencyID = ags.AgencyID)
                        INNER JOIN Services s ON (ags.ServiceID = s.ServiceID)

                        WHERE ag.StateOrProvince ='".$province."'

                        AND ar.Area LIKE '".$city."'";


        $zug = count($serv);

        if ($zug > 0) {
             $sql = $sql. " AND s.ServiceID IN (";
            for ($i=0; $i < $zug; $i++){
               $sql.= " '$serv[$i]' ";

		    }
		    $sql.= ' )';
		}
[\PHP]

when I echo the sql string I get this:

Code: Select all

SELECT DISTINCT ag.AgencyID, ag.CompanyName, ag.Address, ag.StateOrProvince, ag.City, ag.PostalCode FROM Agencies ag INNER JOIN AgencyAreas aa ON (ag.AgencyID = aa.AgencyID) INNER JOIN Areas ar ON (aa.AreaServedID = ar.AreaServedID) INNER JOIN AgencyServices ags ON (ag.AgencyID = ags.AgencyID) INNER JOIN Services s ON (ags.ServiceID = s.ServiceID) WHERE ag.StateOrProvince ='British Columbia' AND ar.Area LIKE 'vic%' AND s.ServiceID IN ( '1' '2' '3' )
[\PHP]

The problem is I've only been using mySQL and PHP for a few days now so I am not really sure if I am doing it right. Any help would be fantastic!!! Thanks a lot
jmarcv
Forum Contributor
Posts: 131
Joined: Tue Jul 29, 2003 7:17 pm
Location: Colorado

Post by jmarcv »

Well, would be nice to know which variable is the checkbox, but....

But, basically, take an array, explode it into a comma delimited string, and go to the mysql site and look up the FIND_IN_SET command. I suspect thats what you want.
DougieC
Forum Newbie
Posts: 18
Joined: Fri Jul 25, 2003 1:49 pm

Post by DougieC »

commaSSSSS, Damn thanks dude that worked :D
But when I do a query, for example i want to find the information associated with the values 1 and 2 , i get the info associated with 1 and the info associated with 2 and the info associated with 1 and 2. Is there a way to get only get the values associated with 1 and 2?
Post Reply