Is it possible to make a query with data from an array in PHP on multiple columns in a database?
Example:
SELECT * FROM rooms WHERE Facility1,Facility2,Facility3,Facility4,Facility5,Facility6,Facility7,Facility8,Facility9 = ' { Array Values Here } '
I have list of checkbox values stored in an array and I only want to select these. If it is not possible, how else can I run a query to select these?
Sure it is possible to query multiple fields:
WHERE Facility1 = 'value1' AND Facility2 = 'value2' ...
That's standard SQL, you find it documented in the mysql manual as well:
For your specific problem (checkbox array values) it heavily depends. You need to map the checkbox array values to the fields in the mysql database to create an SQL statement that makes sense.
As you have not given any information next to that it's an array, there is not much to give as an example but that you map these array values to database columns.
$columns = array('Facility1' => 'checkboxname1', 'Facility2' => 'checkboxname2');
$query = 'SELECT * FROM rooms WHERE ';
$queryWhereAnd = array();
foreach($columns as $column => $name)
{
$queryWhereAnd[] = sprintf('%s = \'%s\'', $column, mysql_real_escape_string($_POST[$name]));
}
$query .= implode(' AND ', $queryWhereAnd ) . ';';
foreach part as needed.SELECT * FROM rooms WHERE Facility1 OR Facility2 OR Facility3 OR Facility4 IN ('".implode("','",$option).'\') - this is what I'm doing right now but the query does not check all the columns. So if something is found in Facility2 for one entry and Facility4 for another then it does not return Facility2 as a result but will return Facility4.WHERE Facility1 IN (...) OR Facility2 IN (...) OR ... and so on. Otherwise it just would be a boolean sort of OR, like WHERE TRUE OR TRUE OR FALSE ... - You first need to learn some of the basics, than it will turn out working for you.Yes, here's the format:
SELECT *
FROM rooms
WHERE (field1, field2, field3, field4)
IN
(('value1', 'value2', 'value3', 'value4'))
But it's likely to scan all rows (not utilize indexes). It's best split out the values as hakre suggests:
WHERE Facility1 = 'value1' AND Facility2 = 'value2' ...
"SELECT * FROM rooms WHERE (Facility1, Facility2, Facility3, Facility4) IN(('".implode("','",$option).'\'))'; - does it mean it will check for matching values in Facility1 OR Facility2 OR Facility3?
The colsest thing to what you're looking for, I think is awful :)
Give this a try:
select * from rooms
where (facility1, facility2, facility3) in (select 'Val1', 'Val2', 'Val3')
Make sure the resulting String from PHP results in a similar query.
PS: I insist on using ANDs
