Inspiring

Answered

Sort out similar strings from a query

Forum|Forum|17 years ago
September 25, 2008
6 replies
1781 views

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

Advanced techniques

This topic has been closed for replies.

Correct answer tomtomtom7147938

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

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>

N

Newsgroup_User

Inspiring

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

N

Newsgroup_User

Inspiring

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/

T

tomtomtom7147938Author

Inspiring

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

N

Newsgroup_User

Inspiring

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

T

tomtomtom7147938Author

Inspiring

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

-

-__cfSearching__-

Inspiring

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.

D

Dan_Bracuk

Inspiring

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.

T

tomtomtom7147938Author

Inspiring

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

D

Dan_Bracuk

Inspiring

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

T

tomtomtom7147938Author

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded