MySQL查询从数据库表中的所有字符串中查找特定区域数据并列出所有相关横幅

Question

tr_regions table contains region details of USA states.

INSERT INTO tr_regions`(`regionpkid`,`regionname`,`statepkid`,`statename`) VALUES
(1, 'Alabama Gulf Coast', 13, 'Alabama'),
(2, 'Greater Birmingham', 13, 'Alabama'),
(3, 'Black Belt', 13, 'Alabama'),
(4, 'Central Alabama', 13, 'Alabama'),
(5, 'Lower Alabama', 13, 'Alabama'),
(6, 'Mobile Bay', 13, 'Alabama'),
(7, 'North Alabama', 13, 'Alabama'),
(8, 'Northeast Alabama', 13, 'Alabama'),
(9, 'Northwest Alabama', 13, 'Alabama'),
(10, 'South Alabama', 13, 'Alabama');

In t_banner table, each banner has specific regions keywords. It will help me to display banners to visitors on specific region page on website. (Ex: Banner 1 will display when #8, #9, & #10 regions pages will be accessed by visitors).

INSERT INTO `t_banner` (`bannerpkid`, `keyword`) VALUES
(1,'8|Northeast Alabama|9|Northwest Alabama|10|South Alabama|'),
(2,'2|Greater Birmingham|4|Central Alabama|6|Mobile Bay|'),
(3,'5|Lower Alabama|7|North Alabama|9|Northwest Alabama|'),
(4,'3|Black Belt|6|Mobile Bay|9|Northwest Alabama|'),
(5,'1|Alabama Gulf Coast|2|Greater Birmingham|3|Black Belt|'),
(6,'5|Lower Alabama|8|Northeast Alabama|10|South Alabama|');

Now in my control panel, I have select menu where all these regions are listed. Now I want that whenever I select any region then all banners should be displayed which have selected region in their keyword field.

$int_region_pkid=0;
if(isset($_GET["regionpkid"]))
{
    $int_region_pkid=trim($_GET["regionpkid"]);
}

$str_query_select="SELECT * FROM t_banner WHERE ... ";

How can I write above query to get desired result? I tried LOCATE and LIKE functions of MySQL but they are not working here. Someone please help me.

Answer 1

It looks like you need this query (x is your regin). See example at sqlfiddle

SELECT * FROM t_banner WHERE keyword LIKE 'x%' OR keyword LIKE '%|x|%';

First part 'x%' if keyword starts from desired region and second '%|x|%' if desired region in other part of keyword