select count with union table sql query

Question

I am making a search paging, i have tried to select count from three table using union but am getting error like this Fatal error: Cannot pass parameter 2 by reference in /home/plooks/public_html/user/test.php on line 106here is my table structure

table 1 name >> blogpost

| bid   | title     | body          | author    |
|-----  |-------    |-------------  |--------   |
| 1     | new       | hello new     | you       |
| 2     | cast      | broadcast     | me        |
| 3     | hack      | who hack us   | you       |

table2 name >> forumnew

| fid   | ftitle    | fbody         | user  |

|-----  |-------    |-------------  |--------   |
| 1     | new forum | hello new     | you       |
| 2     | cast me   | broadcast     | me        |
| 3     | hack you  | who hack him  | us        |

table3 name >> download

| did   | file      | disc          | type      |

|-----  |-------    |-------------  |--------   |
| 1     | whoweare  | hello new     | php       |
| 2     | cast      | broadcast     | html      |
| 3     | hack      | who hack us   | c++       |

SQL QUERY

SELECT COUNT(id) FROM (
    SELECT 'post' AS type, BID AS id FROM blogpost
    UNION
    SELECT 'jail' AS type, jid AS id FROM forumnew
    UNION
    SELECT 'article' AS type, TID AS id FROM download
)csl WHERE title LIKE :search OR title LIKE :search")

What does your code look like? Similar issue: http://stackoverflow.com/questions/8287581/how-to-resolve-cannot-pass-parameter-by-reference-error-in-php#autocomment44785441 — Jacob Mulquin, Jul 06 '16 at 23:50
@mulquin that didn't solve my problem i think my query is wrong — Frank, Jul 07 '16 at 00:00
You now have two `title like search`. But I think you need to put that where under your inner select from blogpost, not the outer query. — artm, Jul 07 '16 at 00:02
@mulquin now i changed to `bindvalue()` and i ger this error ` Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM blog_post) AS search_posts UNION (SELECT 'jail' AS type, jid AS id,' at line 2' in /home/prokn/public_html/user/test.php:106 Stack trace: #0 /home/prokn/public_html/user/test.php(106): PDOStatement->execute() #1 {main} thrown in /home/prokn/public_html/user/test.php on line 106` — Frank, Jul 07 '16 at 00:04
Try running the query using phpMyAdmin or similar with some known values to see if you get the expected result. — Jacob Mulquin, Jul 07 '16 at 00:08

score 1 · Accepted Answer · answered Jul 07 '16 at 00:08

There is a mistake in the query, you don't have the title in the temporary table csl, so it cannot be used in the WHERE clause. Also the second OR title LIKE :search is superfluous. If you don't want to do a distinct count of the id, then you don't need the other columnns, only the title column to filter by, the result will be the same.

SELECT COUNT(*) FROM (
    SELECT title FROM blogpost
    UNION
    SELECT ftitle AS title FROM forumnew
    UNION
    SELECT disc AS title FROM download
) csl WHERE title LIKE :search

or

SELECT
  (SELECT COUNT(*) FROM blogpost WHERE title LIKE :search) +
  (SELECT COUNT(*) FROM forumnew WHERE ftitle LIKE :search) +
  (SELECT COUNT(*) FROM download WHERE disc LIKE :search)

select count with union table sql query

1 Answers1