Databases
StackOverflow2013
Postsdbo
POWhy does my query with named parameters return a blank result?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does my query with named parameters return a blank result?
    primarykey
    data
    text
    <p>I am converting my MySQL code over to PDO to take advantage of prepared statements. I was originally getting a fatal error as described in <a href="https://stackoverflow.com/questions/12663656/why-is-my-prepared-statement-giving-me-a-fatal-error/">this question</a>. I had solved that issue, but it brought up more problems when trying to add parameters.</p> <p>The code I am trying to get working is:</p> <pre><code>include ("foo/bar.php"); try { $DBH = new PDO("mysql:host=$hostname;dbname=$database", $username, $password); } catch(PDOException $e) { echo $e-&gt;getMessage(); } $mydate=date("Y-m-d",strtotime("-3 months")); $foo_query=$DBH-&gt;prepare("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate &gt; '$mydate' ORDER BY postdate DESC"); $foo_query-&gt;execute( array('postdate' =&gt; $_REQUEST['postdate']) ); $DBH=null; </code></pre> <p>In English, this is meant to read "<em>take the current date and set it back 3 months calling it $mydate, then select all of those fields (also taking the first 20 words of the body and calling it 'preview_text') from my table where the postdate is equal to the parameter postdate and where postdate is greater than $mydate</em>".</p> <p>I am then displaying the results in the following (note this is now abridged):</p> <pre><code>$foo_query-&gt;setFetchMode(PDO::FETCH_ASSOC); while($r=$foo_query-&gt;fetch()) { echo $r["id"]; echo $r["title"]; echo date("d-M-Y",strtotime($r["postdate"])); echo nl2br ($r["preview_text"]); } </code></pre> <p>Now, while the SELECT query is written as:</p> <pre><code>("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate &gt; '$mydate' ORDER BY postdate DESC") </code></pre> <p>...it displays exactly what I need it to, but of course while this is prepared, it contains no parameters so only achieves 50% of the goal.</p> <p>I was under the impression the way of preparing the statement that I outlined initially would be fine as per the tutorials and advice I have already been given.</p> <p>I have tried to print my error and it brings up no error. I manually sent the query as follows and get no returns. Note I have tried the date strings in all manner of permutations:</p> <pre><code>("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = 20121001 AND postdate &gt; 20120701 ORDER BY postdate DESC") </code></pre> <p><strong>EDIT: The above was pointed out as incorrect but I will keep it here for reference as this section is explained in the comments below.</strong></p> <p><strong>EDIT: Manually sending the query now displays correctly when typing:</strong></p> <pre><code>("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = '2012-09-30 08:38:23' ORDER BY postdate DESC") </code></pre> <p>The query does not display at all when typing any of the following:</p> <pre><code>("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate ORDER BY postdate DESC") ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate &gt; '$mydate' ORDER BY postdate DESC") ("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = '2012-09-30 08:38:23' AND postdate &gt; '2012-07-01 00:01' ORDER BY postdate DESC") </code></pre> <p>I am thinking that I should bind the parameter in some way, but the tutorials I've read do not specifically say that I should always bind them - I'm unsure of how to proceed.</p> <p><strong>EDIT : I have looked to bind the statement as follows with no success. The query seemingly ignores the bind so the state of the query acts as all of the above permutations without binding:</strong></p> <pre><code>$foo_query-&gt;bindParam ( ":postdate", strtotime ( $_REQUEST['postdate']), PDO::PARAM_INT); $foo_query-&gt;execute(); </code></pre> <p>What am I doing wrong, that isn't happening when I don't add parameters? Should I be declaring the :postdate parameter somewhere other than in the SELECT query?</p> <p>For the purposes of preventing SQL injection I would like to use named parameters as well as preparing the statement, but if I can't figure it out then I will just have to not used named parameters.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. UStomdot
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy does my query with named parameters return a blank result?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COpostdate = 20121001 AND postdate > 20120701 ?
      singulars
      1. POWhy does my query with named parameters return a blank result?
      1. USJvdBerg
    2. COyeah - in English it should read "The postdate is equal to 01-Oct-2012, and the postdate should be greater than 01-July-2012". This complies with the way I was shown to write that statement - postdate = :postdate AND postdate > '$mydate'
      singulars
      1. POWhy does my query with named parameters return a blank result?
      1. UStomdot
    3. COYour missing the point, 20121001 is per definition greater then 20120701. The `AND` in your query has no meaning! Only records with the date 20121001 will be returned. Are there any?
      singulars
      1. POWhy does my query with named parameters return a blank result?
      1. USJvdBerg
 

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