SQL-How to retrieve the correct data using php
Posted
by
Programatt
on Stack Overflow
See other posts from Stack Overflow
or by Programatt
Published on 2013-11-07T15:29:47Z
Indexed on
2013/11/08
3:54 UTC
Read the original article
Hit count: 197
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.
© Stack Overflow or respective owner