checking if variables in one array exist in another

[jardane]

i am making two mySQL query's pulling information from two different tables:

$q = "SELECT zip FROM allowed_zip WHERE user_id='$id'";

and

$z = "SELECT zip_code FROM companies WHERE ". $search;

$search is just a selection sting i build before hand ignore that part.

 

What i need to do is to have an if statement is to check if the values pulled in to $z exist in $q. They are both lists of zip codes, the first one is a list of zip code the user has access too and the second one is what the user is searching for. This is meant to deny them access to select records.

[teamatomic]

$zips = array_intersect($q, $z);

 

 

HTH

Teamatomic

[jardane]

How do i apply that to my problem?

[jcbones]

Is "zip" in "allowed_zip" contain 1 zip code per row, or does each row contain more than 1 zip code?

 

ex:

user_id | zip

1 | 28542

1 | 28555

 

or,

 

1 | 23542, 28555

 

Hope that makes sense.

 

If it is the first, you could (UN-TESTED)

$sql = "SELECT `companies`.`zip_code` FROM `companies`,`allowed_zip` WHERE (`companies`.`zip_code` = `allowed_zip`.`zip` AND `allowed_zip`.`user_id` = '$id') AND $search";

 

If it is the second, (UN-TESTED)

$sql = "SELECT `companies`.`zip_code` FROM `companies`,`allowed_zip` WHERE (`companies`.`zip_code` IN(`allowed_zip`.`zip`) AND `allowed_zip`.`user_id` = '$id') AND $search";

[dirkers]

How about letting the DB do the work by using a sub-select:

 

$q = "SELECT zip_code FROM companies WHERE ". $search." AND zip_code IN (SELECT zip FROM allowed_zip WHERE user_id='$id')";

 

Alternatively, you can rewrite the above query as an INNER JOIN or INTERSECT.

[roopurt18]

Just chiming in to let the OP know dirkers is presenting the best solution.  Keep the heavy lifting in compiled programs.