While working on a web links directory i wanted to update categories table’s num_listings column with the count of listings under each category. The category id was stored something like this in listings table:
id | category | title
--------------------------------------------------------------------
21 | Carpenter-23-Plumbing-45 | Top carpenters and plumbers are here!
In the above example you would notice that the category(ies) stored in category column is actually a combination of two categories. So, in order to count all listings under one category i would do query using regular expression or LIKE statement and i preferred the latter.
(I knew this was not a very good idea to have a table structure like this but i had no other choice as it was a established website using ‘roughly written’ biz2 directory script. The listings table had thousands of records and i didn’t had time to make changes to script to accommodate listings_categories like association or whatsoever.)
Anyways, i was looking for some ‘one time’ sql query which could update the categories column with listings count. In a case where LIKE statement was not involved (means the category field would contain proper category id like 23 or 45, one at a time) one would have achieved it by using something like this:
update categories c set num_listing =
(select count(*) from listings
where category = c.id)
But in my case the query had to be something like where category LIKE “%-“+c.id+”-%” (just an idea). In order to find a solution to this i goggled a lot, also tried some combination of my own but could not figure it out. Finally i posted it as a question at stackoverflow website and after 2 days today i had the answer finally(although, in the meantime i had to update the records using foreach in php).
Here is the sql query to update table record with the count of another table record using LIKE state, finally:
drop table if exists temp;
create table temp as
select categories.id, count(*) as c
from categories
join listings on listings.category like concat('%-',categories.id,'-%') and listings.status='active'
group by categories.id;
update categories, temp
set categories.num_listings = temp.c
where categories.id = temp.id;
Kudos to Gilz at stackoverflow. You can view the question answered here http://tinyurl.com/66f8ghk. I hope it helps someone.
