MySQL LIKE wildcard for table

Question

Hi,

I have a PHP document which will execute some MySQL and echo it back.

Code:
$query1 = "SELECT * FROM feed, recipients WHERE feed.title LIKE \"%recipients.suburb%"\ ORDER BY pubDate DESC";

recipients.suburb (table.column) is in my MySQL DB. How can I perform wildcards on this?

This works, but I cant do it to tables?
$query1 = "SELECT * FROM feed, recipients WHERE feed.title LIKE '%test%' ORDER BY pubDate DESC";

Id like to check the column feed.title and see if any of this text is in recipients.suburb. It's for a script that will eventually email users, based on text from MySQL co-relating to the location of the user.

Thanks :)

Answer 1

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

Answer 2

Try:

SELECT * 
FROM feed, recipients 
WHERE feed.title LIKE concat('%', recipients.suburb, '%')
ORDER BY pubDate DESC