Solved

MySQL query needed to match posted array.

Posted on 2011-09-10
9
369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

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!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

695 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