MySQL Query using UNION - PHP PDO issues

Question

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">

score 1 · Answer 1 · answered Jan 03 '14 at 08:36

1

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;

answered Jan 03 '14 at 08:36

Mostafa -T

730
8
19

niyou · Answer 2 · 2014-01-03T08:34:32.780

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

peterm · Accepted Answer · 2014-01-03T08:43:16.480

0

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.

edited Jan 03 '14 at 08:43

answered Jan 03 '14 at 08:22

peterm

91,357
15
148
157

Now it makes more sense! to why i was getting only one result set – Jan 03 '14 at 08:34
*how could i display the values the query is showing?* Here is a good generic solution http://stackoverflow.com/questions/7716785/get-last-executed-query-in-php-pdo – peterm Jan 03 '14 at 08:58
In this particular case (for this specific query) it can be just a strait forward `str_replace`. Take a look at this [codepad](http://codepad.viper-7.com/L3tnIF) example – peterm Jan 03 '14 at 09:06
Great but I meant how to display the query results. The data from the tables? – Jan 03 '14 at 09:13
Got it. That is where `UNION ALL` comes to play. See [sqlfiddle](http://sqlfiddle.com/#!2/65eed/8) example. Then you iterate over the resultset and render your search results. – peterm Jan 03 '14 at 09:21
alright great, so would i need to run two sets of queries. 1st set to count the number of values and 2nd set to get the results? Also in the php would i need to do foreach loop to echo the results right? – Jan 03 '14 at 09:28
There is no need two issue two queries. You can for example use `fetchAll()` and use `count()` to get the number of rows in the resultset that will be returned as an array. Yes `foreach` would be the most convenient way to iterate over the resultset. – peterm Jan 03 '14 at 09:41

MySQL Query using UNION - PHP PDO issues

3 Answers3