How to fetch data using loop PHP statement from mysql to ios?

Question

My system is composed of 3 components.

ios - php - mysql

If ios users select multiple 'id's,
then, ios app posts these selected 'id' request to server,
and, the server finds the data based on selected 'id's in MySQL.
and finally, the server passes these data to ios app.

These are the PHP statements I tried.

<?php


    $id_0 = $_POST['0'];
    $id_1 = $_POST['1'];
    $id_2 = $_POST['2'];
    $id_3 = $_POST['3'];
    ...
    $id_n = $_POST['n'];

    $conn = mysqli_connect('address', 'user', 'password', 'database');
    $sql = "SELECT * FROM firstname WHERE id = '$id_0'";

    if ($result = mysqli_query($conn, $sql)) {

    $resultArray = array();
    $tempArray = array();

    while($row = $result->fetch_object()) {
       $tempArray = $row;
       array_push($resultArray, $tempArray);
     }

     } else {

     }

     mysqli_close($conn);


?>

To get multiple 'id' data, I found that I need to use loop statement.

But the problem is, the number of selected id are variable.

I think that in this code, I need two loop statement.

1. To get multiple data based on multiple id(the number of id are variable)
2. To append these data into array

I don't know how to use loop statement when the range is not defined.

How can I use loop statement in PHP, and how can I rearrange these codes?

Answer 1

I'm sure this question is a duplicate but I can't find an exact source.

Your current method means you need to run a whole MySQL query for each iteration.

As the query results will never change what the iteration contains, therefore; you can simply rework it to use MySQL IN to load all of your variables at once:

Step 1) Take all the values and place them in a single array.

$new = []; //new array
foreach ($_POST as $key=>$id){
        // Check it is a numeric key
        // Check id value is valid to avoid importing other POST values 
        if((int)$key == $key && (int)$id == $id){
             $new[] = $id;
        }
}

The above code block is nessecary to stop using values from $_POST['button'] or other posted data that should not be included. This step can be removed if you can clarify your posted data, such as saving all ids to a $_POST['id'] array itself.

Step 2) Empty the array of null/void or repeated values.

$new = array_unique($new); 

Step 3) Turn the array into a string, inside the SQL

$arrayString = implode(',',$new);

Step 4) Plug the string into the SQL IN clause:

$sql = "SELECT * FROM firstname WHERE id IN (" . $arrayString . ") ORDER BY id";

---

Simplified and reduced:

$new = []; //new array
foreach ($_POST as $key=>$id){
        if((int)$key == $key && (int)$id == $id){
             $new[] = $id;
        }
}
$new = array_unique($new);
$sql = "SELECT * FROM firstname WHERE id IN (" .
         implode(',',$new) . ") ORDER BY id";

The SQL query above will give you an array of arrays, each one a different row. You can the order them, sort them and output them in PHP as you wish.

See also: This Q&A.

BUT

As expressed by others, you really, REALLY should be using Prepared Statements with MySQLi.

See also here, here and here to see further how to do it and WHY you should do it.

---

Top Tips:

• Numerical columns (typical of id columns) in MySQL do not need the ' quotes.
• Until you're using Prepared Statements you can typecast the variables to integers ((int)$var) to limit risk.
• It is better to specify the columns you need rather than to use the * catch-all.
• Your $_POST data should be an array $_POST['ids'][...].
• Eat five different pieces of fruit or veg' a day.

• Never trust user input!