Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP - Query single value per iteration or fetch all at start and retrieve from array?

I have a function that looks something like this:

//iteration over scales
foreach ($surveyScales as $scale)
{
    $surveyItems = $scale->findDependentRowset('SurveyItems');

    //nested iteration over items in scale
    foreach ($surveyItems as $item)
    {
        //retrieve a single value from a result table and do some stuff
        //depending on certain params from $item / $scale
    }
}

QUESTION: is it better to do a db query for every single value within the inner foreach or is it better to fetch all result values into an array and get the value from there?

like image 601
markus Avatar asked Dec 10 '08 17:12

markus


People also ask

How fetch data from database in array in PHP?

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL. The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both.

How can fetch data without while loop in PHP?

You can just call mysql_fetch_assoc() (or any similar function) one. Like this: $sql = "SELECT * FROM table"; $row = mysql_fetch_assoc( mysql_query($sql) ); echo $row['title']; This is the easiest and most readable way to do this with the MySQL functions.

What does fetchAll return PHP?

Return Values ¶ PDOStatement::fetchAll() returns an array containing all of the remaining rows in the result set. The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch.


1 Answers

One query that returns a dozen pieces of data is almost 12x faster than 12 queries that return 1 piece of data.

Oh, and NEVER EVER NEVER put a SQL inside a loop, it will always lead in a disaster.

Depending on how your app works, a new connection might be opened for each query, this is especially bad as every DB server has a limit on the number of connections. Then also realize this will happen for each user, so 50 queries with 5 users and you already have 250 queries at any given moment. But even if all the queries do share just 1 connection, you're taxing the DB server X times more, slowing it down for everything else, every page, because users are hogging the DB server on this page, and everybody has to share.

I've seen an entire application fail in the past because of this 1 design flaw, just don't do it.

like image 122
TravisO Avatar answered Nov 08 '22 11:11

TravisO