Sort out similar strings from a query

[tomtomtom]

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

[Dan_Bracuk]

A better plan would be to do something about the duplicate records that are causing the problem.

[tomtomtom]

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

[Dan_Bracuk]

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.

[tomtomtom]

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

[tomtomtom]

Notice that's it's NOT duplicate records!!!!! There is just similar entries in the table!

[-__cfSearching__-]

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.

[Newsgroup_User]

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

[Newsgroup_User]

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/

[tomtomtom]

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

[Newsgroup_User]

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/

[tomtomtom]

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

[Newsgroup_User]

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/

[tomtomtom]

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

[tomtomtom · Accepted solution]

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>

[Newsgroup_User]

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/

[Newsgroup_User]

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/

[tomtomtom]

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

[Newsgroup_User]

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).