CODEWITHSUNDEEP
phpmysql
ChartProblems
ChartProblems

Reputation: 437

PHP query mysql database in an efficient manner

I am retrieving a lot of single data items from a mysql database and have constructed the code below whereby the variables for each data item is set to be identical to the ID that is queried. My problem is that the array contains some 50-100 elements and the query seems to be very slow as a result. I would very much appreciate if anyone can review the code and inform me if I can somehow optimize this such that it will execute faster. Thank you very much in advance.

        $idArray = [ // contains some 50-100 elements in total
          "BANANA",
          "APPLE",                 
          "COCONUT",       
          "PEAR",       
        ];

        foreach($idArray as $val) {
          $query = "SELECT * FROM {$tableName} WHERE ID = '{$val}'";
          $result = mysqli_query($conn, $query) or die('error');
          while($data = mysqli_fetch_array($result)) {
              $$val = $data["COINFO"];
          }
        }

Update: I have now tried to construct the query with the IN keyword instead but the query still seems to be executing very slowly on my server whereas there are no issues at all on my localhost. As such, I am wondering if there might be some other issue.

Based on the comment below, I understand that the most optimal approach is to apply parameterized queries and bind_param and I would very much appreciate if someone can translate the above code to the new updated approach!

Upvotes: 0

Views: 58

Answers (2)

S. Imp
S. Imp

Reputation: 2877

You can alter your code so that instead of running a query with each loop iteration it instead constructs a query using the IN keyword. The query will run only once:

   $idArray = [ // contains some 50-100 elements in total
      "BANANA",
      "APPLE",
      "COCONUT",       
      "PEAR",       
    ];

   $query = "SELECT * FROM {$tableName} WHERE ID IN ('" . implode("','", $idArray) . "')";
   $result = mysqli_query($conn, $query) or die('error');
   while($data = mysqli_fetch_array($result)) {
       $$val = $data["COINFO"];
   }

Upvotes: 0

Crutches
Crutches

Reputation: 179

Try to construct an IN statement. This way you can make one query, rather than looping through your array.

So your query would be something like this after construction:

SELECT * FROM tableName WHERE ID IN ('BANANA','APPLE','COCONUT','PEAR');

Upvotes: 1

Related Questions