You would be better off with a better E-R design where you have a suburb
table with an id. Then you can have foreign key suburb_id
in both the feed
table and the recipients
table. This would create a many-to-many relationship between feed
and recipient
that you are looking for here.
The solution would then be to use joins to match recipients to feeds where the suburb is the same.
Here is what I would propose:
CREATE TABLE IF NOT EXISTS `feed` (
`feed_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) DEFAULT NULL,
`suburb` BIGINT( 20 ) UNSIGNED NULL,
`pubDate` date DEFAULT NULL,
PRIMARY KEY (`feed_id`)
UNIQUE KEY `feed_id` (`feed_id`)
);
CREATE TABLE IF NOT EXISTS `recipients` (
`recipient_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`suburb` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`recipient_id`),
UNIQUE KEY `recipient_id` (`recipient_id`)
);
CREATE TABLE IF NOT EXISTS `suburb` (
`suburb_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`suburb_id`),
UNIQUE KEY `suburb_id` (`suburb_id`)
);
And use this SELECT statement:
SELECT feed.title, recipients.name, suburb.name
FROM feed, recipients, suburb
WHERE feed.suburb = suburb.suburb_id
AND suburb.suburb_id = recipients.suburb
AND feed.title LIKE CONCAT( '%', suburb.name, '%' )
ORDER BY pubDate DESC