PHP newbie here. Here's an example of my table in a MySQL database I'm pulling data from.
id classA classB value
------------------------------
1 A A 1
2 A B 5
3 A C 2
4 B A 1
5 B B 5
6 B C 1
7 C A 8
8 C B 5
9 C C 7
The user in puts a list of Class categories (A, B, C etc.) and my code will return the values from every combination of these pairs (e.g. [A,A], [A,B], [A,C]... etc.). I can achieve this quite easily using the following script where $array is the input list (e.g. [A, B, C]):
<?php
// Mehtod 1 - slow
for($i = 0; $i < count($arr); $i++){
for ($j = 0; $j < count($arr); $j++){
$sql = "SELECT value FROM data_table WHERE '$arr[$i]'=classA AND '$arr[$j]'=classB LIMIT 1";
$value = mysqli_query($con,$sql);
$value = mysqli_fetch_array($corr)[0];
$results[] = array('classA' => $arr[$i], 'classB' => $arr[$j], 'value' => $value);
}
}
?>
However, this is quite slow because the mysqli_query() is inside the for loop. Instead I'd prefer to do the query in a single call. I've tried the following with no luck..
<?php
// Mehtod 2 - fast
for($i = 0; $i < count($arr); $i++){
for ($j = 0; $j < count($arr); $j++){
// make array of class combinations
$query_array[] = array('classA' => $arr[$i], 'classB' => $arr[$j]);
}
}
// get arrays of pairs to request
$match1 = array_column($query_array, 'classA');
$match2 = array_column($query_array, 'classB');
$sql = "SELECT classA, classB, value FROM data_table WHERE classA IN '$match1' AND classB IN '$match2'";
$results = mysqli_query($con,$sql);
$results = mysqli_fetch_array($results);
?>
Can I make a query like this with a single request? I'm a bit stuck. Cheers.
Since you want all the possible combinations, you do not need to build combinations inside PHP and then use them in query.
I would rather do the following:
SELECT classA, classB, value
FROM data_table
WHERE classA IN ('A', 'B', 'C') AND
classB IN ('A', 'B', 'C')
This would consider all the combinations. It will be equivalent to:
SELECT classA, classB, value
FROM data_table
WHERE (classA = 'A' AND classB = 'A') OR
(classA = 'A' AND classB = 'B') OR
(classA = 'A' AND classB = 'C') OR
(classA = 'B' AND classB = 'A') OR
(classA = 'B' AND classB = 'B') OR
(classA = 'B' AND classB = 'C') OR
(classA = 'C' AND classB = 'A') OR
(classA = 'C' AND classB = 'B') OR
(classA = 'C' AND classB = 'C')
In this case, the PHP code would look as follows:
<?php
// Method 3 - possibly fastest and neater code
// get comma separated values to match against
$match_string = "('" . implode("','", $arr) . "')";
$sql = "SELECT classA, classB, value
FROM data_table
WHERE classA IN " . $match_string . " AND
classB IN " . $match_string;
$results = mysqli_query($con,$sql);
$results = mysqli_fetch_array($results);
?>
Most Importantly, to avoid against SQL injection related attacks, you should rather use Prepared Statements
The user in puts a list of Class categories (A, B, C etc.
- So, you are on the right track in using the IN operator, however, you can't attach
$match1 and $match2 directly in your SQL query since they both are still arrays.
- You will have to convert them into comma separated strings and add single quote to each string, since your
classA and classB are string columns in your DB table.
Code:
<?php
$match1 = array('A','B');
$match2 = array('A','B');
$match1_values = implode(",",array_map("addQuotes",$match1));
$match2_values = implode(",",array_map("addQuotes",$match2));
$sql = "SELECT classA, classB, value FROM data_table WHERE classA IN ($match1_values) AND classB IN ($match2_values)";
echo $sql;
function addQuotes($each_class_value){
return "'".$each_class_value."'";
}
Update:
You can replace
for($i = 0; $i < count($arr); $i++){
for ($j = 0; $j < count($arr); $j++){
// make array of class combinations
$query_array[] = array('classA' => $arr[$i], 'classB' => $arr[$j]);
}
}
// get arrays of pairs to request
$match1 = array_column($query_array, 'classA');
$match2 = array_column($query_array, 'classB');
with
<?php
$match_values = implode(",",array_map("addQuotes",$arr));
$sql = "SELECT classA, classB, value FROM data_table WHERE classA IN ($match_values) AND classB IN ($match_values)";
echo $sql;
function addQuotes($each_class_value){
return "'".$each_class_value."'";
}
