PHP - Use array in mysqli query

Question

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.

Madhur Bhaiya · Accepted Answer · 2018-11-20T02:04:34.000

1

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

edited Nov 20 '18 at 02:04

answered Nov 18 '18 at 12:21

Madhur Bhaiya

28,155
10
49
57

Thanks very much for this and the note on SQL injections. I had a go with your code and got it running although I needed to change `implode(",'", $arr)` to `implode("','", $arr)` (extra single quote before the comma) to make the `$match_string`. – Muon Nov 19 '18 at 21:54
With `json_encode($results);` I'm getting back `{"0":"A","classA":"A","1":"B","classB":"B","2":"5","corr":"5"}` where as what I'm after is `[{"classA":"A","classB":"A","value":1},{"classA":"A","classB":"B","value":5},{"classA":"B","classB":"A","value":1},{"classA":"B","classB":"B","value":5}]` Why does it produce such a messy array? – Muon Nov 19 '18 at 22:06
1

@Lachlan.00 you will need to do some error operations. MySQL will fetch results in tabular manner only – Madhur Bhaiya Nov 20 '18 at 02:03
Great, thanks @Madhur, would you kindly be able to suggest a link that details these kind of processes? – Muon Nov 20 '18 at 04:27
Just a note that I solved this with something like: `while($row = mysqli_fetch_array($results, MYSQLI_NUM)){ $array[] = $row; }` – Muon Nov 20 '18 at 04:38

score 1 · Answer 2 · answered Nov 18 '18 at 12:29

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

PHP - Use array in mysqli query

2 Answers2

Linked