Solved

MySQL query needed to match posted array.

Posted on 2011-09-10
9
365 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
designing in object programming 12 77
Wordpress Taxonomy 2 28
php simple error message 4 23
Complex SQL statement in VB.NET 7 15
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

773 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