3

I am trying to understand when is the SQL query actually taking place? For example, lets say I have a code like this:

$stmt = $db->query("SELECT * FROM my_Table"); //Line #1 

foreach ($stmt as $row)  //Line #2
{
    $abc[] = $row['abc'];
}

In the above example, when does the script communicate with the mysql database to get the data? Does it query in Line #1 itself and stores the data to the $stmt variable (or) does it only connects to the database in Line #2 in the foreach() statement?

I know this may sound like a very basic question but I am trying to understand this...

Neel
  • 9,352
  • 23
  • 87
  • 128
  • 4
    The query is executed when the $db-query() is called, but the data is __buffered__ by the database via the resource $stmt variable, and only transferred to PHP one row at a time – Mark Baker Jan 11 '14 at 11:45
  • 2
    @MarkBaker It actually depends on the second parameter of [mysqli::query()](http://www.php.net/manual/mysqli.query.php). See also [this question](http://stackoverflow.com/a/9876758/603003). – ComFreek Jan 11 '14 at 11:50
  • @Mark Baker: I think your answer makes sense. When I was testing the code, I added `print_r($stmt);` after Line #1 and before the foreach statement and the result that was echoed is `PDOStatement Object ( [queryString] => SELECT * FROM my_Table)` instead of printing the arrays of fetched results from the query. So I am assuming its due to the data being buffered until it is transferred to php like you said, right? It was this way I got confused if the query was executed in line#1 or not and thats why I asked here.. – Neel Jan 11 '14 at 12:00
  • 1
    Yes, the resultset is buffered so that it's only transferred to PHP when requested, so a large query that returns a lot of rows will not soak up your PHP memory until data rows are actually requested from the resultset. The PDOStatement object doesn't buffer the data itself, but serves as the request mechanism – Mark Baker Jan 11 '14 at 12:04
  • 1
    @eggyal [`mysqli_result`](http://php.net/manual/class.mysqli-result.php) (the return type of `mysqli::query`) implements Traversable and is therefore usable in a foreach loop (as of 5.4.0). [`PDOStatement`](http://php.net/manual/class.pdostatement.php) also implements Traversable. – ComFreek Jan 11 '14 at 12:04
  • @ComFreek: Gosh. I stand thoroughly corrected. – eggyal Jan 11 '14 at 12:14
  • @Mark Baker: Thank you so much for that explanation. If you can perhaps add these as answer, I can accept it since you have pointed out how it actually works with my example code. :) – Neel Jan 11 '14 at 12:49

1 Answers1

3

It will be executed when you call the query() function. Here the manual reference.

StarsSky
  • 6,721
  • 6
  • 38
  • 63
  • 3
    Data isn't actually stored inside $stmt, otherwise a large query would still take a lot of PHP memory: but the $stmt variable acts as a _gatekeeper_ to the buffered datastore, controlling access to the data – Mark Baker Jan 11 '14 at 12:06