I use this simple query to determine the most popular searches on my website:
select keyword, count(*) as total from searches group by keyword order by total desc limit 500;
I want to add a column that indicates how many times a particular keyword is found in testimonials. The testimonials are in a separate table called testimonies. I’m thinking that I need to implement the following, but I don’t know exactly where:
count( case when (t.title like ‘%s.keyword%’ or t.testimonyText like ‘%s.keyword%’) then 1 else null end ) as countInTestimonials
So how do I modify the searches query to include a count in the testimonies table?
Oh, you’re right. I guess they don’t have to be combined into one query. So you are suggesting that in my PHP code, in the Do loop, for every keyword that is returned from the first query to then run the second query? To answer your question, the two tables are related in that they are both in the same database.
SELECT keyword
, count(*) as total
, ( SELECT COUNT(*)
FROM testimonies
WHERE title LIKE CONCAT('%',s.keyword,'%')
OR testimonyText LIKE CONCAT('%',s.keyword,'%') ) AS countInTestimonials
FROM searches AS s
GROUP
BY keyword
ORDER
BY total desc limit 500;
This query never finishes. It timed out in the two times I tried to run it. Maybe the 240,000 rows in the searches table and 7,000 rows in the testimonies table is too much?