CODEWITHSUNDEEP
phpsql
Programatt
Programatt

Reputation: 836

SQL-How to retrieve the correct data using php

I am new to SQL so please excuse my question if it is simple.

I have a database with a few tables. 1 is a users table, the others are application tables that contain the users preferences for receiving notifications about that application based on the country they are interested in.

What I want to do, is retrieve the e-mail address of all users that have an interest in that country. I am struggling to think about how to do this. I currently have the following query constructed, and the code to populate the values

function check($string)
{
    if (isset($_POST[$string])) {
        $print = implode(', ', $_POST[$string]); //Converts an array into a single string
        $imanageSQLArr = Array();


        if (substr_count($print,'Benelux') > 0) {
            $imanageSQLArr[0] = "checked";

        } else {
            $imanageSQLArr[0] = "off";


        }

        if (substr_count($print, 'France') > 0) {
            $imanageSQLArr[1] = "checked";

        } else {
            $imanageSQLArr[1] = "off";

        }
        if (substr_count($print,  'Germany') > 0) {
            $imanageSQLArr[2] = "checked";
        } else {
            $imanageSQLArr[2] = "off";

        }
        if (substr_count($print,  'Italy') > 0) {
            $imanageSQLArr[3] = "checked";
        } else {
            $imanageSQLArr[3] = "off";

        }
        if (substr_count($print, 'Netherlands') > 0) {
            $imanageSQLArr[4] = "checked";
        } else {
            $imanageSQLArr[4] = "off";

        }
        if (substr_count($print,  'Portugal') > 0) {
            $imanageSQLArr[5] = "checked";

        } else {
            $imanageSQLArr[5] = "off";
        }
        if (substr_count($print,  'Spain') > 0) {
            $imanageSQLArr[6] = "checked";

        } else {
            $imanageSQLArr[6] = "off";
        }
        if (substr_count($print,  'Sweden') > 0) {
            $imanageSQLArr[7] = "checked";

        } else {
            $imanageSQLArr[7] = "off";
        }
        if (substr_count($print,  'Switzerland') > 0) {
            $imanageSQLArr[8] = "checked";

        } else {
            $imanageSQLArr[8] = "off";
        }

        if (substr_count($print, 'UK') > 0) {
            $imanageSQLArr[9] = "checked";

        } else {
            $imanageSQLArr[9] = "off";
        }

and the query

$tocheck = $db->prepare(
    "SELECT users.email
     FROM users,app
     WHERE users.id=app.userid
     AND BENELUX=:BENELUX
     AND FRANCE=:FRANCE
     AND GERMANY=:GERMANY
     AND ITALY=:ITALY
     AND NETHERLANDS=:NETHERLANDS
     AND PORTUGAL=:PORTUGAL
     AND SPAIN=:SPAIN
     AND SWEDEN=:SWEDEN
     AND SWITZERLAND=:SWITZERLAND
     AND UK=:UK");
$tocheck->execute($country);
$row = $tocheck->fetchAll();

This does retrieve data, but only people who's preferences match EXACTLY what is put (so what they haven't selected is taken into account as much as what they have). Any help would be greatly appreciated.

Upvotes: 0

Views: 71

Answers (2)

Rich Dolinsky
Rich Dolinsky

Reputation: 111

If I am understanding correctly, you just need to change your AND's to OR's

$tocheck = $db->prepare(
"SELECT users.email
 FROM users,app
 WHERE users.id=app.userid
 AND 
(   
BENELUX=:BENELUX
 OR FRANCE=:FRANCE
 OR GERMANY=:GERMANY
 OR ITALY=:ITALY
 OR NETHERLANDS=:NETHERLANDS
 OR PORTUGAL=:PORTUGAL
 OR SPAIN=:SPAIN
 OR SWEDEN=:SWEDEN
 OR SWITZERLAND=:SWITZERLAND
 OR UK=:UK
)
");
$tocheck->execute($country);
$row = $tocheck->fetchAll();

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88062

You have to go one step further. Basically, you have to see if the option is checked to see whether to actually test for that in your users.

For example:

SELECT email 
FROM users u
  INNER JOIN app a on (a.userid = u.id)
WHERE 
     (:BENELUX = 'off' OR a.BENELUX = :BENELUX)
AND (:FRANCE = 'off' OR a.FRANCE = :FRANCE)

etc..

What this does is limits the WHERE conditions down to just those that have your selected country. However, if NO country is selected then ALL of the conditions will pass and you will get everyone. So you probably want to have some checking in the PHP code to ensure that they picked at least one.

This line means allow the record if BENELUX was selected and the app table matches OR if benelux was not selected.

 (:BENELUX = 'off' OR a.BENELUX = :BENELUX)

The and here ensures that the previous condition must be true as well as this one.

AND (:FRANCE = 'off' OR a.FRANCE = :FRANCE)

Now, this is only going to pull all users which match all of the selected countries. So if they are filtering on France and Benelux then only those users which have both France and Benelux attributes will be selected.

If you are trying to match any user that has at least one of the countries selected, then the query is slightly different:

SELECT email 
FROM users u
  INNER JOIN app a on (a.userid = u.id)
WHERE 
     (:BENELUX != 'off' AND a.BENELUX = :BENELUX)
OR (:FRANCE != 'off' AND a.FRANCE = :FRANCE)

Note that you don't have to do checking in PHP to ensure that they picked at least one country for this query. If they picked nothing, then the query will return zero records.

Upvotes: 1

Related Questions