Argthtjtr

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."'";

}