CODEWITHSUNDEEP
phpmysqlpdo
user1434156
user1434156

Reputation:

MySQL Query using UNION - PHP PDO issues

I have built a basic search engine which is almost functional. I am using UNION in the query in order to search for an specific value in two tables blog and pages. I am also using PDO style. There are a total of 13 rows. When I perform the query with keyword blah which is found in every column of both tables. I only get 5 results back from SELECT COUNT(*) FROM blog and nothing back from SELECT COUNT(*) FROM pages . Also I am using a prepare statement for :searchquery => $serachquery that stores the keyword value but when echoing the value I get :searchquery instead of blah. How can I display the correct results? DEMO

PHP

<?php
include("db_con/db_con.php");
$search_output = "";
if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){
  $searchquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['searchquery']);
  if($_POST['filter1'] == "All Tables"){
   $sqlCommand = "(SELECT COUNT(*) FROM blog WHERE blog_title LIKE :searchquery OR blog_body LIKE :searchquery) UNION (SELECT COUNT(*) FROM pages WHERE page_title LIKE :searchquery OR page_body LIKE :searchquery) ";
  }
  $sql_prepare = $db_con->prepare($sqlCommand);
  if($sql_prepare->execute( array("searchquery"=>'%'.$searchquery.'%') )){
    $count = $sql_prepare->fetchColumn();
    if($count > 1){
      $search_output .= "<hr />$count results for <strong>$searchquery</strong><hr />$sqlCommand";
    } else {
      $search_output = "<hr />0 results for <strong>$searchquery</strong>$sqlCommand<hr />";
    }
  }
}
?>

HTML

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Search For:
  <input name="searchquery" type="text" size="44" maxlength="88">
Within:
<select name="filter1">
<option value="All Tables">All Tables</option>
</select>
<input name="myBtn" type="submit">

Upvotes: 1

Views: 1899

Answers (3)

peterm
peterm

Reputation: 92815

First of all UNION happened* (see a note below) to give you in your case two rows in the resultset.

| COUNT(*) |
|----------|
|        5 | -- with fetchColumn() you read a value of only the first row
|        8 |

but you fetch only a value of the first row. That's why you always get 5 instead of 13

Note: the usage of UNION in your particular case is invalid because it returns only distinct values. This means if both selects happened to have the same value (e.g. 5) then your result set would've looked like this 

| COUNT(*) |
|----------|
|        5 |

instead of 

| COUNT(*) |
|----------|
|        5 | 
|        5 |

You might've used UNION ALL instead of UNION and either iterated over the resultset and got the sum in php, or used an outer SELECT with an aggregate function SUM().

Now to get the total count you can simply do this

SELECT 
(
  SELECT COUNT(*) 
          FROM blog 
         WHERE blog_title LIKE :searchquery 
            OR blog_body  LIKE :searchquery
) + 
(
  SELECT COUNT(*) 
    FROM pages 
   WHERE page_title LIKE :searchquery
      OR page_body LIKE :searchquery
) total

Output:

| TOTAL |
|-------|
|    13 |

Here is SQLFiddle demo

Now as far as php code goes

1) this check

if($count > 1)...

is incorrect and actually useless since you can legitimately get only one hit by your search criteria and COUNT() always return a value be it 0 or some other positive integer. Therefore you can simply leave the only line

$search_output .= "<hr />$count results for <strong>$searchquery</strong><hr />$sqlCommand";
                                                                               ^^^^^^^^^^^

2) You see placeholders :search query when you output the query because you output the raw sql query from $sqlCommand variable.

Upvotes: 0

Mostafa -T
Mostafa -T

Reputation: 740

now replace this line : 

$count = $sql_prepare->fetchColumn();

with this : 

$count = 0;
foreach($sql_prepare->fetchall() as $records)
{
    $count += $records[0];
}

it returns count of all rows;

Upvotes: 1

niyou
niyou

Reputation: 873

I made a sqlfiddle: http://sqlfiddle.com/#!2/82482/3

As you see your query returns two rows - this might be your problem. You can enhance your query to sum the both results:

SELECT SUM(results)
FROM 
      (SELECT COUNT(*) as results
      FROM blog 
      WHERE blog_title LIKE '%blah%' OR blog_body LIKE '%blah%' 
      UNION
      SELECT COUNT(*) as results
      FROM pages 
      WHERE page_title LIKE '%blah%' OR page_body LIKE '%blah%') a

Upvotes: 0

Related Questions