18 Replies Latest reply on Sep 26, 2008 8:27 AM by tomtomtom

    Sort out similar strings from a query

    tomtomtom Level 1
      Hello

      I'm stuck. Below you see the content of my table 'hersteller'. As you see the names 'Bertschi' and 'Flückiger Hemmi' appear more than one time.



      In the end I would like to create a pull-down (in html) which sould look like:

      <option value="157,164">Bertschi</option>
      <option value="158">Bonsma</option>
      <option value="159">Bürli</option>
      <option value="160,162,163">Flückiger Hemmi</option>
      <option value="161">Kornoski</option>

      So far I can SELECT from 'hersteller' but every row is listed (that's quite normal). But how do I go on from here. I have no idea how to parse in a query result for similar strings. And no idea about collecting then hersteller_IDs I need.

      I'm very thankfull for any hints

      Regards

        • 1. Re: Sort out similar strings from a query
          Dan Bracuk Level 5
          A better plan would be to do something about the duplicate records that are causing the problem.
          • 2. Sort out similar strings from a query
            tomtomtom Level 1
            Hi Dan

            I actually showed only a selection of columns in this table. There is more columns - so I need to have several similar entries for names and firstnames...

            But I feel that there isn't an easy solution, right? Is there some way to do it anyway?

            Thanks
            • 3. Re: Sort out similar strings from a query
              Dan Bracuk Level 5
              That being the case, your major problem is with your database design. If you have never heard the term "normalized database" before, I have heard good things about the book Database Design for Mere Mortals.
              • 4. Sort out similar strings from a query
                tomtomtom Level 1
                Hi Dan

                I actually normalized the database. The table I'm talking about might be a little strange but I need it that way for many other request in my project. For these it works really fine. This topic here treats only a 'nice to have' issue. And my expectation is to realize this 'nice to have'.

                So I'm not willing yet to close the topic. I hope you understand.

                Thanks
                • 5. Re: Sort out similar strings from a query
                  tomtomtom Level 1
                  Notice that's it's NOT duplicate records!!!!! There is just similar entries in the table!
                  • 6. Re: Sort out similar strings from a query
                    -==cfSearching==- Level 4
                    tomtomtom wrote:
                    > Notice that's it's NOT duplicate records!!!!! There is just similar entries in the table!
                    > As you see the names 'Bertschi' and 'Flückiger Hemmi' appear more than one time.

                    If they both represent the same person (member, etcetera) then it is duplicate information.
                    • 7. Re: Sort out similar strings from a query
                      Level 7
                      tomtomtom wrote:
                      > I'm stuck. Below you see the content of my table 'hersteller'. As you see the
                      > names 'Bertschi' and 'Fl�ckiger Hemmi' appear more than one time.

                      it appears the "hersteller_ID" doesn't matter so just use the "hersteller_name"
                      or whatever to process the form submission.

                      as far as the select (pull-down) goes, just do a GROUP BY either in your
                      original query or query of query.

                      SELECT hersteller_name
                      FROM hersteller
                      GROUP BY hersteller_name
                      • 8. Re: Sort out similar strings from a query
                        Level 7
                        hersteller_ID is used as option values if you look at the OP.

                        if your db is mysql, you can use GROUP_CONCAT() function to concatenate
                        rows of data.

                        if not, you can do:

                        <select ...>
                        <cfoutput query="..." GROUP="hersteler_Name">
                        <cfset ids_list = "">
                        <cfoutput><cfset ids_list = listappend(ids_list, hersteller_ID)></cfoutput>
                        <option value="#ids_list#">#hersteler_Name#</option>
                        </cfoutput>
                        </select>

                        hth

                        Azadi Saryev
                        Sabai-dee.com
                        http://www.sabai-dee.com/
                        • 9. Re: Sort out similar strings from a query
                          tomtomtom Level 1
                          Hello all

                          thank you for your inputs. I think AZADI got my problem. I list now a full dump of the table. May be everything will be cleared a little up (supposingly it was a bad idea to not show the full table from scratch?).

                          My DB is MS Access. I still didn't understand everything...

                          <select ...>
                          <cfoutput query="..." GROUP="hersteler_Name">
                          <cfset ids_list = "">
                          <cfoutput><cfset ids_list = listappend(ids_list, hersteller_ID)></cfoutput>
                          <option value="#ids_list#">#hersteler_Name#</option>
                          </cfoutput>
                          </select>


                          Can you please write out a little bit more on the first line saying: query="..." GROUP="hersteler_Name"
                          Is the query:
                          <cfquery name="get_hersteller">
                          SELECT hersteller.*
                          FROM hersteller
                          </cfquery> ?????

                          afterwards:
                          <select ...>
                          <cfoutput query="get_hersteller" GROUP="hersteller_name">
                          <cfset ids_list = "">
                          <cfoutput><cfset ids_list = listappend(ids_list, hersteller_ID)></cfoutput>
                          <option value="#ids_list#">#hersteller_name#</option>
                          </cfoutput>
                          </select>

                          Then I have to put a cfloop somewhere to get all names listed? But where and waht query?

                          As you see I'm not really familiar with that stuff yet, but I feel being really close and would appreciate if somebody answered this more or less rookie questions.

                          Regards
                          • 10. Re: Sort out similar strings from a query
                            Level 7
                            tomtomtom wrote:

                            > Is the query:
                            > <cfquery name="get_hersteller">
                            > SELECT hersteller.*
                            > FROM hersteller
                            > </cfquery> ?????


                            yes, you query would be something like

                            <cfquery name="get_hersteller" datasource="your_dsn_here">
                            SELECT whichever, fields, you, need
                            FROM hersteller
                            </cfquery>

                            >
                            > afterwards:
                            > <select ...>
                            > <cfoutput query="get_hersteller" GROUP="hersteller_name">
                            > <cfset ids_list = "">
                            > <cfoutput><cfset ids_list = listappend(ids_list, hersteller_ID)></cfoutput>
                            > <option value="#ids_list#">#hersteller_name#</option>
                            > </cfoutput>
                            > </select>
                            >
                            > Then I have to put a cfloop somewhere to get all names listed? But where and
                            > waht query?

                            afterwards, just use this verbatim to create your <select> list. you do
                            not need to add any loops or anything. if you are not familiar with
                            GROUP attribute of <cfoutput> tag - check the cfml reference manual
                            (download from adobe.com if you do not have one):

                            <select name="myselect" size="1">
                            <cfoutput query="get_hersteller" GROUP="hersteler_name">
                            <cfset ids_list = "">
                            <cfoutput><cfset ids_list = listappend(ids_list, hersteller_id)></cfoutput>
                            <option value="#ids_list#">#hersteler_name# #hersteller_fname#</option>
                            </cfoutput>
                            </select>

                            just change the name="myselect" to whatever name you want to give this
                            form field.

                            hth

                            Azadi Saryev
                            Sabai-dee.com
                            http://www.sabai-dee.com/
                            • 11. Re: Sort out similar strings from a query
                              Level 7
                              Azadi wrote:
                              > hersteller_ID is used as option values if you look at the OP.

                              yes i saw that but all they'll get back is a list of IDs supposedly matched to
                              that name, so in the end just that name should do (assuming that this will end
                              up doing something like querying/updating a db).

                              • 12. Re: Sort out similar strings from a query
                                tomtomtom Level 1
                                Dear AZADI

                                Thank you again. I tested it and unfortunately didn't get the result I want. May be its a drag but now I list again everything I have so far.

                                1. Below the table as it is (you see there is several times identical names - I'm supposed to GROUP everything by the hersteller_shortname

                                2. The queries and everything you told me so far...

                                <cfquery name="get_hersteller">
                                SELECT hkb_hersteller.*
                                FROM hkb_hersteller
                                </cfquery>

                                <cfoutput query="get_hersteller" GROUP="hersteller_shortname">
                                <cfset ids_list = "">
                                <cfoutput>
                                <cfset ids_list = listappend(ids_list, hersteller_ID)></cfoutput>
                                #hersteller_name# #hersteller_fname# #hersteller_shortname# (#ids_list#)<br>
                                </cfoutput>

                                3. My result list is the following:


                                Bertschi Andreas BERTA12 (157)
                                Bonsma Noah Adrian BONSN1 (158)
                                Bürli Manuel BURLM1 (159)
                                Flückiger Hemmi Michael FLUCM12 (160)
                                Kornoski Nathalie KORNN1 (161)
                                Kunz Nicolas KUNZN13 (162)
                                Noti Andrea NOTIA1 (163)
                                Rampichova Eva RAMPE1 (164)
                                Sasaki-Kuroiwa Mayuko SASAM1 (165)
                                Schmid Natalie SCHMN13 (166)
                                Steffen Nadine STEFN1 (167)
                                Stettler Niklas STETN12 (168)
                                Tang Mansing TANGM1 (169)
                                Tiller Sabrina TILLS1 (170)
                                Weisskopf Manuela WEISM12 (171)
                                Wyss Fabian WYSSF12 (172)
                                Bertschi Andreas BERTA12 (173)
                                Bonsma Noah Adrian BONSN1 (174,189,190)
                                Wyss Fabian WYSSF12 (191)

                                You see p.e. 'Bonsma Noah Adrian BONSN1' appears on the top with one ID and below once again with three IDs. And other names weren't grouped.

                                Can you tell me what's worng?

                                Thank you & kind regards
                                • 13. Re: Sort out similar strings from a query
                                  Level 7
                                  that means only one thing: the names are not the same.
                                  you maybe have a trailing space in some of them, i.e.:
                                  one hersteller_shortname is "BONSN1"
                                  while another one is "BONSN1 "

                                  it could be any non-printing character, not just space - and you won't
                                  se it in html output because those characters are suppressed in html.

                                  as you can see, the second BONSN1 has 3 IDs in a list, and that makes me
                                  believe the other names are different.

                                  to remedy this, use your db's appropriate string function to trim the
                                  field value, i.e.

                                  SELECT ..., TRIM(hkb_hersteller.short_name) AS short_name
                                  FROM hkb_hersteller

                                  check your db's manual for correct function to use.

                                  to quickly check if different names is the case, change this:

                                  #hersteller_name# #hersteller_fname# #hersteller_shortname# (#ids_list#)

                                  to:
                                  <pre>#hersteller_name# #hersteller_fname# #hersteller_shortname#
                                  (#ids_list#)</pre>

                                  so that on-screen html output preserves the whitespace.
                                  you may be able to see extra spaces or whatever then. otherwise, lok
                                  into your db and check the names.


                                  Azadi Saryev
                                  Sabai-dee.com
                                  http://www.sabai-dee.com/
                                  • 14. Re: Sort out similar strings from a query
                                    tomtomtom Level 1
                                    Dear AZANI

                                    I just checked the hersteller_shortnames. The ones supposed to be grouped are identical. But another thing I saw and which is most probably the reason is that only records listed 'together' are grouped.

                                    p.e. 'Bonsma' with ID 158 is listed apart from the 'Bonsma' with ID 174,189,190 while these three records are standing one under the other. Can you see this two? Might it be that GROUP isn't understood by MS Access or ColdFusion or the syntax has to be different?

                                    Thanks again
                                    • 15. Re: Sort out similar strings from a query
                                      tomtomtom Level 1
                                      Hello everybody

                                      Meranwhile I found it. Now, I'm happy and thanks to everybody for patience and help!!!

                                      <cfquery name="get_hersteller" datasource="#request.db_datasource#" dbtype="odbc" username="#request.db_username#" password="#request.db_passwort#">
                                      SELECT trim(hkb_hersteller.hersteller_shortname) AS hersteller_shortname, hkb_hersteller.hersteller_name, hkb_hersteller.hersteller_fname, hkb_hersteller.hersteller_ID
                                      FROM hkb_hersteller
                                      GROUP BY hkb_hersteller.hersteller_shortname, hkb_hersteller.hersteller_name, hkb_hersteller.hersteller_fname, hkb_hersteller.hersteller_ID
                                      ORDER BY hkb_hersteller.hersteller_name ASC
                                      </cfquery>


                                      <cfoutput query="get_hersteller" group="hersteller_shortname" groupcasesensitive="No">
                                      <cfset ids_list = "">
                                      <cfoutput>
                                      <cfset ids_list = listappend(ids_list, hersteller_ID)></cfoutput>
                                      #hersteller_name# #hersteller_fname# #hersteller_shortname# (#ids_list#)<br>
                                      </cfoutput>
                                      • 16. Re: Sort out similar strings from a query
                                        Level 7
                                        so it was your short names at fault after all? were they in different case?

                                        Azadi Saryev
                                        Sabai-dee.com
                                        http://www.sabai-dee.com/
                                        • 17. Re: Sort out similar strings from a query
                                          Level 7
                                          btw, what do you need that GROUP BY clause in your query for???

                                          it is not needed for GROUP attribute of <cfoutput> tag... only proper
                                          ORDER BY clause is important for that... which pops an idea in my head
                                          that maybe that was the cause for it not working before?..

                                          Azadi Saryev
                                          Sabai-dee.com
                                          http://www.sabai-dee.com/
                                          • 18. Re: Sort out similar strings from a query
                                            tomtomtom Level 1
                                            No, the hersteller_shortname's were indead identical!

                                            May be its a MS Access issue (makes not really sense). I have to group the data within the first query from the hersteller table so that same hersteller_shortname entries are listed one after the other. And then hand the query over to your code, means <cfoutput query=".....

                                            That is the only way it worked, everything else failed.

                                            Regards