mysql row search

Posted on 2006-04-18
Last Modified: 2013-12-24
I have a table:

Table Keywords


Here is sample data


Let's say I want to get ID's where keyword is test5 and test6, but I want the ID number has to be the same.

ie if I did a search for test5 and test6 keywords it would return 1 because that is the only ID that matches the keywords

Any ideas how I would do this?


Question by:rjohnsonjr
    LVL 10

    Expert Comment

    not sure what you are trying to accomplish, but you can select the distinct id from the table for your user's input. then check the number of records returned. something like this.....

    <cfquery name="findRecs" datasource"yourDSN">
      select distinct id
      from yourTable
      where keyword like 'form.input'
    <cfif findRecs.recordCount gt 1>
        <cfset form.errorMsg = "keyword matched more than 1 result. refine your search">
    LVL 6

    Author Comment

    I want to do a search on the table and if I do a search on two keywords  test5 and test6 (from my example above)  if the ID is the same for both of the keywords found I want to return the ID.

    Test5 and test6 both have the same ID in my table data above so the ID would be the same

    Here is what I want

    Select all IDs from keywords table where a row.keyword = test5 and a row.keyword = test6 and each row has the same ID.

    LVL 16

    Accepted Solution

    One way to do this is with an Or query on the keywords and a group by having clause...
    Something like this (I'm not a mysql expert, but I believe this syntax is supported by virtually every database (access, oractle, mysql, sql server, db2):

    <cfquery name="commonids" datasource="yourdb">
    select id from
    where keyword = <cfqueryparam value="test5">
     or keyword=<cfqueryparam value="test6">
    group by id
    having count(*) > 1
    LVL 16

    Expert Comment

    You would substitute your form variables, or whatever for the test5 and test6....
    where keyword = <cfqueryparam value="#trim(form.field1)#">
     or keyword=<cfqueryparam value="#trim(form.field2)#">

    Building up the criteria in the where clause would be by whatver means is applicable.
    The important point is to get all the entries for the keyword... group by id, then filter the list where the count(*) > 1... meaning that more than one record was found for that id that matched the keywords.
    LVL 16

    Expert Comment

    having works like where, but works on the grouped records.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
    Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now