Databases
StackOverflow2013
Postsdbo
PORecursion in prepared statements
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORecursion in prepared statements
    primarykey
    data
    text
    <p>I've been using PDO and preparing all my statements primarily for security reasons. However, I have a part of my code that <em>does</em> execute the same statement many times with different parameters, and I thought this would be where the prepared statements really shine. But they actually break the code...</p> <p>The basic logic of the code is this.</p> <pre><code>function someFunction($something) { global $pdo; $array = array(); static $handle = null; if (!$handle) { $handle = $pdo-&gt;prepare("A STATEMENT WITH :a_param"); } $handle-&gt;bindValue(":a_param", $something); if ($handle-&gt;execute()) { while ($row = $handle-&gt;fetch()) { $array[] = someFunction($row['blah']); } } return $array; } </code></pre> <p>It looked fine to me, but it was missing out a lot of rows. Eventually I realised that the statement handle was being changed (executed with different param), which means the call to fetch in the while loop will only ever work once, then the function calls itself again, and the result set is changed.</p> <p>So I am wondering what's the best way of using PDO prepared statements in a recursive way.</p> <p>One way could be to use fetchAll(), but it says in the manual that has a substantial overhead. The whole point of this is to make it more efficient.</p> <p>The other thing I could do is not reuse a static handle, and instead make a new one every time. I believe that since the query string is the same, internally the MySQL driver will be using a prepared statement anyway, so there is just the small overhead of creating a new handle on each recursive call. Personally I think that defeats the point.</p> <p>Or is there some way of rewriting this?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRob
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORecursion in prepared statements
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORecursion in prepared statements
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORecursion in prepared statements
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI just did some (not super scientific) testing my very similar recursive function code and have concluded that for my purposes, prepared statements are slower overall. What's the point of preparing a statement if you have to re-prepare it before each use? I must be missing something.
      singulars
      1. PORecursion in prepared statements
      1. USJoel Mellon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload