Solved

MySQL query needed to match posted array.

Posted on 2011-09-10
9
368 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
Independent Software Vendors: 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!

 
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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
Php logic to add to date card 9 39
scan php uploads for viruses 5 26
How do I remove "" from json_encode 5 22
MySQL stored procedure returning null values 4 25
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

749 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