Solved

MySQL query needed to match posted array.

Posted on 2011-09-10
9
362 Views
Last Modified: 2012-05-12
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
Comment
Question by:sabecs
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 82

Accepted Solution

by:
hielo earned 300 total points
ID: 36517891
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 200 total points
ID: 36517910
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
 
LVL 52

Expert Comment

by:_agx_
ID: 36517913
nevermind ;-) sat too long on the page.
0
 
LVL 7

Expert Comment

by:boon86
ID: 36517927
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Expert Comment

by:boon86
ID: 36517947
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
 
LVL 7

Expert Comment

by:boon86
ID: 36517949
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
 

Author Closing Comment

by:sabecs
ID: 36518023
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
 
LVL 82

Expert Comment

by:hielo
ID: 36518030
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
 
LVL 52

Expert Comment

by:_agx_
ID: 36520214
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now