Unexpected Result in MySQL PDO

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

Yusufmalikul
New php-forum User
New php-forum User
Posts: 2
Joined: Tue Apr 08, 2014 7:28 am

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 ?

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

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


Return to “PHP coding => General”

Who is online

Users browsing this forum: No registered users and 1 guest