PHP query mysql database in an efficient manner

Question

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!

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
– user9189147, Commented Jan 30, 2019 at 21:35
WARNING: When using mysqli you should be using parameterized queries and bind_param 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. 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
– tadman, Commented Jan 30, 2019 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
– tadman, Commented Jan 30, 2019 at 21:37
Are these 50-100 elements fixed? Or from another table/query? — danblack
– danblack, Commented Jan 30, 2019 at 21:42

Crutches · Accepted Answer · 2019-01-30 21:28:33Z

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');

answered Jan 30, 2019 at 21:28

Crutches

1791 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S. Imp · Accepted Answer · 2019-01-30 21:28:42Z

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"];
   }

answered Jan 30, 2019 at 21:28

S. Imp

2,87714 silver badges30 bronze badges

6 Comments

Nikita U. Over a year ago

also select only COINFO field will be a little bit better

tadman Over a year ago

implode is extremely risky here if that's arbitrary data. That must be escaped.

S. Imp Over a year ago

@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.

tadman Over a year ago

Hard-wired is usually just the start. Then it becomes user data in production and bad things happen.

ChartProblems Over a year ago

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?

|

Collectives™ on Stack Overflow

PHP query mysql database in an efficient manner

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related