Frank Frank - 5 months ago 11
PHP Question

select count with union table sql query

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

Answer

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)