Page 1 of 1

using an array in a query string

Posted: Wed Jul 30, 2003 5:45 pm
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

Posted: Thu Jul 31, 2003 12:03 am
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.

Posted: Thu Jul 31, 2003 8:22 am
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?