Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

Unexpected Result in MySQL PDO

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

Unexpected Result in MySQL PDO

Postby Yusufmalikul » Tue Apr 08, 2014 8:39 pm

When visit index.php/?s=programmer
The result is
Code: Select all
Array
(
    [COUNT(*)] => 1
)

That's is right.
Code that show above result
Code: Select all
if ($search) {

   // If visitor is searching
   $sql = $DBH->prepare("SELECT COUNT(*) FROM blog_posts
      WHERE post_title LIKE '%$search%' OR post_content LIKE '%$search%'");
   //$sql->bindParam(':search', $search);
   print_r($sql);
   // Useful for indexing
   $searh_param = "&s=".$search;

} else {
   // If visitor not searching then query all post
   $sql = $DBH->prepare("SELECT COUNT(*) FROM blog_posts");   
}

$sql->execute();

$sql->setFetchMode(PDO::FETCH_ASSOC);
$total_post = $sql->fetch();
print_r($total_post);


But when i change $search to :search because i want to use PDOStatement::bindParam(), when visit this url the result is unexpected
Code: Select all
Array
(
    [COUNT(*)] => 0
)

The code is
Code: Select all
if ($search) {

   // If visitor is searching
   $sql = $DBH->prepare("SELECT COUNT(*) FROM blog_posts
      WHERE post_title LIKE '%:search%' OR post_content LIKE '%:search%'");
   $sql->bindParam(':search', $search);
   print_r($sql);
   // Useful for indexing
   $searh_param = "&s=".$search;

} else {
   // If visitor not searching then query all post
   $sql = $DBH->prepare("SELECT COUNT(*) FROM blog_posts");   
}

$sql->execute();

$sql->setFetchMode(PDO::FETCH_ASSOC);
$total_post = $sql->fetch();
print_r($total_post);


What is wrong with my code ?
Yusufmalikul
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Apr 08, 2014 7:28 am

Re: Unexpected Result in MySQL PDO

Postby seandisanti » Wed Apr 09, 2014 10:03 pm

a few things...

1) a prepared statement is for use with INSERT, UPDATE, DELETE statements.
2) if you're performing a query once, preparing the query first adds overhead on your mysql server and makes the query take longer
3) * is a lazy way to ask for every column, and COUNT() is an aggregate function, you do not actually want to count each column, you only actually care about the number of rows, or the count of a specific (unique) column. Ambiguity is the enemy of security and efficiency, and should be avoided.

Those 3 things alone are reason enough to start from scratch, so on to the constructive part.

Assuming you want the code to do what your comments say, rather than what your code says, you would want to do something like this:

Code: Select all


$where 
= (!empty($search))?" WHERE CONCAT(post_title,post_content) LIKE " . $DBH->quote("%$search%"):''; //where statement populated if $search is
$sql = "SELECT post_id, post_content,author,create_date FROM blog_posts " . $where

$result 
= $DBH->query($sql,PDO::FETCH_ASSOC);
if (!$result || $result->rowCount()==0){
     die('nothing to show here');
}
while ($row = $result->fetch()){
// code to handle each row goes here
}

***edit*** fixed typo
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm


Return to PHP coding => General

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron