CODEWITHSUNDEEP
phpmysqlsearchselectcount
nemuroito
nemuroito

Reputation: 179

how to read current value in sql

i created a search function :

SELECT
    tbl_place.id AS id, 
    tbl_place.metalink AS metalink, 
    tbl_place.title AS title, 
    tbl_place.img_thumbnail AS img_thumbnail, 
    tbl_place.category1_id AS category1_id, 
    tbl_place.category2_id AS category2_id, 
    tbl_place.category3_id AS category3_id, 
    tbl_place.region_id AS region_id, 
    tbl_place.subregion_id AS subregion_id, 
    tbl_place.summary AS summary, 
    (SELECT COUNT(tbl_place.title) FROM tbl_place) AS total_show_up
FROM tbl_place 
WHERE 
    tbl_place.title LIKE '%$keywords[0]%' AND
    tbl_place.title LIKE '%$keywords[1]%'

if i search with keywords: "Pizza Store", it will display something like this:

* Pizza Store
* Pizza Store Manhattan
* Pizza Store California
* Pizza Store California
* Pizza Store Texas
* Pizza Store California

And what i want to do is, also count how many times each tbl_place.title show up while the query is still searching with the keywords. I want the result to become like this:

* Pizza Store - showed 1 time
* Pizza Store Manhattan - showed 1 time
* Pizza Store California - showed 3 times
* Pizza Store California - showed 3 times
* Pizza Store Texas - showed 1 time
* Pizza Store California - showed 3 times

Is there any syntax to read current value of tbl_place while SELECT-ing?

I believe i change something in this part of the query : 

(SELECT COUNT(tbl_place.title) FROM tbl_place) AS total_show_up

Upvotes: 0

Views: 86

Answers (1)

jordeu
jordeu

Reputation: 6821

Try an approach like this:

SELECT
   p1.title, pj.total
FROM 
   pizza p1
JOIN 
   (
    SELECT 
       p2.title title, COUNT(p2.id) total
    FROM pizza p2 
    WHERE p2.title LIKE '%Pizza%' AND p2.title LIKE '%Store%'
    GROUP BY p2.title
   ) AS pj
ON p1.title=pj.title

Upvotes: 2

Related Questions