• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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);
0
sabecs
Asked:
sabecs
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
hieloCommented:
Instead of having multi-values in a single column (referring to keyword_tags column):
(11, 1, '2003 2006 2018 2099'),
(12, 1, '2102 2030'),
(13, 1, '2087 2085 2089 2099 2100 2444'),

You should really be inserting ONE value per column:
(11, 1, '2003 2006 2018 2099'),
(11, 1, '2006'),
(11, 1, '2018'),
(11, 1, '2099'),
(12, 1, '2102'),
(12, 1, '2030'),
(13, 1, '2087'),
(13, 1, '2085'),
(13, 1, '2089'),
(13, 1, '2099'),
(13, 1, '2100'),
(13, 1, '2444'),

(I suggest you search for articles on db normalization if you are not familiar with it).


Then in your php:
<?php
//connect to db
mysql_connect('localhost','username','password') or die('Unable to connect' . mysql_error() );

//select db
mysql_select_db('databaseNameHere') or die('Unable to select db: ' . mysql_error());

//sanitize the input
foreach($_REQUEST['keyword_tags'] as $k=>$v)
{
    $_REQUEST['keyword_tags'][$k]=mysql_real_escape_string($v);
}

$sql=sprintf('SELECT * FROM `keywords_tags` WHERE `keyword_tags` IN (%s)', implode(',', $_REQUEST['keyword_tags']) );

$result=mysql_query($sql) or die('Unable to execute <br />' . htmlentities($sql, ENT_QUOTES) . mysql_error() );

if(0==mysql_num_rows($result)){
  echo 'No records found';
}
else{
  while($row=mysql_fetch_assoc($result)){
    echo htmlentities(print_r($row,true), ENT_QUOTES) . '<br />';
  }
}
0
 
_agx_Commented:
If at all possible, don't store multiple values in the same column. As you've discovered, it makes querying very difficult.  Not to mention it's error prone and performs poorly.

You should normalize the data. Store each user_id  and tag combination in a separate record. Then your 2 queries will be a very simple


#, user_id, tag_id, ...
1, 10, 2087, ...
2, 11, 2003
3, 11, 2006
4, 11, 2018
5, 11, 2099
....

      find records that match any posted values? - user id's: 10,11 & 13


        SELECT  user_id
        FROM     keywords_tags
        WHERE  tag_id IN (2087, 2089, 2099)


      only users that have all posted values? - user id: 13


        SELECT  user_id, COUNT(DISTINCT tag_id) TotalTags
        FROM     keywords_tags
        WHERE  tag_id IN (2087, 2089, 2099)
        GROUP BY user_id
        HAVING TotalTags  = 3
     
** Where 3  is the total number of elements in the array / IN clause

0
 
_agx_Commented:
nevermind ;-) sat too long on the page.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
boon86Commented:
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
0
 
boon86Commented:
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
0
 
boon86Commented:
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

0
 
sabecsAuthor Commented:
Thanks for your help, I will take your advice and normalize table, I will ask a seperate question on how to alter current table.
0
 
hieloCommented:
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.
0
 
_agx_Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now