asked on

MySQL query needed to match posted array.

Hi,
I have a form that has multiple select options for keywords, which produces an array as below.

$keyword_tags = $_REQUEST['keyword_tags'];

print_r($keyword_tags) --> Array ( [0] => 2087 [1] => 2089 [2] => 2099)

What SQL statement should I use to find records that match any posted values? - user id's: 10,11 & 13
Also, what SQL statement should I use if I wanted only users that have all posted values? - user id: 13

Thanks in advance for your feedback.

CREATE TABLE IF NOT EXISTS `keywords_tags` (
`user_id` int(11) default NULL,
`updated` int(1) default '0',
`keyword_tags` text,
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `keywords_tags`
--

INSERT INTO `keywords_tags` (`user_id`, `updated`, keyword_tags`) VALUES
(10, 0, '2087'),
(11, 1, '2003 2006 2018 2099'),
(12, 1, '2102 2030'),
(13, 1, '2087 2085 2089 2099 2100 2444'),
(14, 0, NULL);

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

nevermind ;-) sat too long on the page.

boon86

based on your question you asking for query:

SELECT user_id, LENGTH( keyword_tags ) - LENGTH( REPLACE( keyword_tags,  ' ',  '' ) ) +1 AS keyword_counter
FROM keywords_tags
HAVING keyword_counter =6

Open in new window

this query is to count keyword that exactly as userid 13 as 6 word.

Hope that help

boon86

What SQL statement should I use to find records that match any posted values? - user id's: 10,11 & 13

try this:
(10, 0, '2087'),
(11, 1, '2003 2006 2018 2099'),
(12, 1, '2102 2030'),
(13, 1, '2087 2085 2089 2099 2100 2444'),

SELECT * FROM keywords_tags WHERE keyword_tags LIKE '%2087%' OR keyword_tags LIKE '%2003%' 
 OR keyword_tags LIKE '%2006%'  OR keyword_tags LIKE '%2018%'  OR keyword_tags LIKE '%2099%'
 OR keyword_tags LIKE '%2085%' OR keyword_tags LIKE '%2089%' 
 OR keyword_tags LIKE '%2100%'  OR keyword_tags LIKE '%2444%'

Open in new window

hope that help

boon86

sorry its:

SELECT * FROM keywords_tags WHERE keyword_tags LIKE '%2087%' OR keyword_tags LIKE '%2003%' 
 OR keyword_tags LIKE '%2006%'  OR keyword_tags LIKE '%2018%'  OR keyword_tags LIKE '%2099%'
 OR keyword_tags LIKE '%2085%' OR keyword_tags LIKE '%2089%' 
 OR keyword_tags LIKE '%2100%'  OR keyword_tags LIKE '%2444%'

Open in new window

sabecs

ASKER

Thanks for your help, I will take your advice and normalize table, I will ask a seperate question on how to alter current table.

hielo

Your table definitions looks fine. The problem is that you are inserting multiple keyword_tags values into a SINGLE field at once. Instead insert the values like I showed you above.

_agx_

The overall structure is okay. But if the "tag" values are all numeric as they appear, you should also change the data type from varchar to something numeric.

You might also modify the column name to indicate it contains a singular value only, like "keyword_id", "tag_id", etc... But that's up to you.