0

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!

ChartProblems
  • 415
  • 4
  • 16
  • https://stackoverflow.com/questions/17870999/bind-multiple-parameters-into-mysqli-query – AbraCadaver Jan 30 '19 at 21:30
  • Make sure you have an index on ID field too. This can speed up queries on large tables. You can run an explain statement to make sure that your query is using an index. – user9189147 Jan 30 '19 at 21:35
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Jan 30 '19 at 21:37
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and ideally should not be used in new code. – tadman Jan 30 '19 at 21:37
  • Are these 50-100 elements fixed? Or from another table/query? – danblack Jan 30 '19 at 21:42
  • Hello danblack. The elements are actually fixed (hardwired) and not generated in other queries. I have tried the suggested IN approach but the query still seems to be running very slow on my server whereas on localhost there are no issues at all. @tadman: I appreciate that there are now better ways to execute this code but can I trouble you to show me how you would accomplish this? I am self-taught programmer and it is much easier for me to read code than to write it myself (if that makes sense). I much appreciate the help from everyone! – ChartProblems Jan 30 '19 at 21:46
  • There's a number of patterns here, most of which involve introducing *N* placeholders `?` and then calling `bind_param`, but honestly `mysqli` is really, really terrible at doing this. PDO is a lot more flexible, `execute()` can take an arbitrary array. Even better is using an ORM, most of those can do a whole lot of work for you. [Propel](http://propelorm.org/), [RedBeanPHP](https://redbeanphp.com/) and [Eloquent](https://laravel.com/docs/master/eloquent) are all good examples. – tadman Jan 30 '19 at 22:31

2 Answers2

1

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');
Crutches
  • 179
  • 1
  • 5
0

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"];
   }
S. Imp
  • 2,833
  • 11
  • 24
  • 1
    also select only COINFO field will be a little bit better – Nikita U. Jan 30 '19 at 21:32
  • 1
    `implode` is *extremely* risky here if that's arbitrary data. That *must* be escaped. – tadman Jan 30 '19 at 21:37
  • @tadman that depends on whether `$idArray` is hard-wired in code (as it is on the OP) or whether this array comes from user input or external data. But yes these values should be escaped. – S. Imp Jan 30 '19 at 21:38
  • 2
    Hard-wired is usually just the start. Then it becomes user data in production and bad things happen. – tadman Jan 30 '19 at 21:38
  • The data actually will be hardwired but even when I apply the IN keyword the query still seems to be running very slowly on my server whereas on localhost there are no issues at all. I don't know it this would lead you to believe that the query is to blame or if it is something entirely different that is causing trouble? – ChartProblems Jan 30 '19 at 21:47
  • @ChartProblems that sounds like you may have a different problem. You should update your question with this additional information. – S. Imp Jan 30 '19 at 21:49