Zikula Community

• Lobos
  
  

  • Rank: Expert
  • Registered: Dec 02, 2002
  • Last visit: Apr 30, 2010
  • Posts: 1474

  Posted: Feb 22, 2005 - 04:58 AM

  This has me pulling my hair out!!!
  
  uid-------id--------keyword
  
  1---------72---------fo
  2---------72---------bar
  3---------72---------is
  3---------72---------cool
  4---------73---------fo
  5---------74---------bar
  6---------75---------is
  7---------76---------cool
  
  any one know a query to do something like this:
  
  so I have a table with id and keyword as columns now I want to return the id that has attached all the keywords that I have specified (each id can have multiple keywords with each 1d / keyword pair on a new row - ie only 1 keyword per row
  
  I tried this statement:
  
  $SQL = "Select * from keyword where keyword IN ( 'foo', 'bar', 'is', 'cool' )";
  
  but it returns all the ids that have ANY of the the 4 keywords - I want only the ids returned that have attached ALL the 4 keywords... eg going by the table example above I would want returned only id number 72 and none of the others...
  
  Thanks if you can help
  
  --
  -Lobos
  Professional PHP Framework Services: Concept, Development and Deployment

  • 
  •  
• **unknown user**
  
  

  • Rank: Helper
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 877

  Posted: Feb 22, 2005 - 06:36 AM

  I don't use mysql for that feature, as I use a simple text file, but I use the following code:
  
  if ((($type == $platform) or ($type == "all")) && (($type1 == $platform1) or ($type1 == "all")) && (($type2 == $platform2) or ($type2 == "all")) && (($type4 == $platform4) or ($type4 == "all")) ) {
  
  echo ......
  
  This checks 4 keywords and returns the results if they match or if the default "all" variable has been set.

  • 
  •  
• Lobos
  
  

  • Rank: Expert
  • Registered: Dec 02, 2002
  • Last visit: Apr 30, 2010
  • Posts: 1474

  Posted: Feb 22, 2005 - 07:29 AM

  Ah thanks for your advice, but I cracked it with this SQL:
  
  $query = "
  select tID from keyword
  where keyword in ( '$cat1', '$cat2', '$cat3', '$cat4' )
  group by tID having count(*) = $num
  ORDER by tID DESC
  ";
  
  $num is the amount of "keywords" an item needs to be elligable so if a user only wants to match two keywords they can, if they want to match more they also can.
  
  -Lobos
  
  --
  -Lobos
  Professional PHP Framework Services: Concept, Development and Deployment

  • 
  •  
•  
•  

Users on-line

• bronto

This list is based on users active over the last 60 minutes.