Joining 2 tables - problem with data format

Databases
1

I have 2 tables, events and categories, which are part of this WordPress plugin:

The plugin is great, except there is no simple way to display all events and categories on a single page.

I will be able to get that out of the tables via this SQL:

SELECT 
   cats.category_name, 
   events.event_name
FROM
   wp_swcnka_events_detail events, 
   wp_swcnka_events_cat_detail_tbl cats
WHERE
   events.category_id = cats.id;

However, while the cats.id value is a simple number e.g. 1, 2, 3 etc. the format of events.category_id is this:

a:1:{i:0;s:1:"2";}

Where the “2” is the correct cat_id to link through to the cats. table.

I need to do the join on the 2 in between the speech marks, but I can’t work out how I’d do that in the SQL. Presumably via some string manipulation / substr / instr work, or maybe even a reg-ex.

Trouble is, I can’t work out how to do it!

The site I’m working on is running 5.1.39-log.

Any advice much appreciated.

Thanks!

2

yikes!!!

perhaps you could use LIKE ??

what does the data look like if there are multiple categories for the event?