I am converting my MySQL code over to PDO to take advantage of prepared statements and parameters but problems have arisen when trying to add parameters.
The code I am trying to get working is:
include ("foo/bar.php");
try {
$DBH = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
}
catch(PDOException $e) {
echo $e->getMessage();
}
$mydate=date("Y-m-d",strtotime("-3 months"));
$foo_query=$DBH->prepare("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate = :postdate AND postdate > '$mydate' ORDER BY postdate DESC");
$foo_query->execute( array('postdate' => $_REQUEST['postdate']) );
$DBH=null;
In English, this is meant to read "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".
I am then displaying the results in the following (note this code is abridged):
$foo_query->setFetchMode(PDO::FETCH_ASSOC);
while($r=$foo_query->fetch()) {
echo $r["id"];
echo $r["title"];
echo date("d-M-Y",strtotime($r["postdate"]));
echo nl2br ($r["preview_text"]); }
Now, while the SELECT query is written as:
("SELECT id, postdate, title, SUBSTRING_INDEX(body,' ',20) as preview_text, body FROM BarTable WHERE postdate > '$mydate' ORDER BY postdate DESC")
....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.
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. I have also been advise that AND is useless in my initial query, but it was not explained why.
I have tried to print my error and it brings up no error. Manually sending the query displays correctly when typing the following, but that does not utilise $mydate:
("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")
The query does not display at all when typing any of the following:
("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 > '$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 > '2012-07-01 00:01' ORDER BY postdate DESC")
I thought that I might need to bind the parameter in some way, but the tutorials I've read do not specifically say that I should always bind them. Either way, I attempted 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:
$foo_query->bindParam (
":postdate", strtotime ( $_REQUEST['postdate']), PDO::PARAM_INT);
$foo_query->execute();
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?
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.
Thanks in advance
Tom
|
Calendar
Search
Help
Members
Register
Login
Home
]