SQL-How to retrieve the correct data using php
- by Programatt
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.
