Solved

Problem in recordcount with group

Posted on 2008-10-28
12
282 Views
Last Modified: 2013-12-24
Hi experts.
Look at the code below.The problem is that i have wrong recordcount values.
if one art_ID has f.e two values in ArtExtras table like art_ID 1 than if i want to show all items in my resultpage  (They are 2 now) i have recordcount 3 and not 2.
The select query does not work good too.
If i have selected two values for S_Extras in URL(S_Extras=10&S_Extras=20) i have in my result page both art_id 1   and  art_id 2 as result.(I should have only the item with art_Id 1 that has in the ArtExtras Table the values 10 and 20 as Extras)
The hole function with recorcount and Maxrows... does not work good.
Any help?
(i have CF8 and MSSQL)
DB:

Artikel:

Art_ID   Km          Price 

1        10000        5000

2        12000        6000

ArtExtras:

Artikel_ID    Extras

1             10

1             20

2             10

 

(Artikel.Art_ID=ArtExtras.Artikel_ID)

Result.cfm:

<cffunction name="cleanOrderBy" returntype="string">

  <cfargument name="theValue" type="string">

  <cfargument name="defaultSort" type="string">

  <cfif REFindNoCase("[\w,]{1,50}\s+(asc|desc)\s*",theValue,1)>

    <cfset matches = REFindNoCase("[\w,]{1,50}\s+(asc|desc)\s*",theValue,1,"true")>

    <cfreturn Mid(theValue,matches.pos[1],matches.len[1])/>

  </cfif>

  <cfreturn defaultSort/>

</cffunction>

<cfparam name="PageNum_dboartikel" default="1">

<cfparam name="url.tfm_orderby" default="Price">

<cfparam name="url.tfm_order" default="ASC">

<cfset sql_orderby = cleanOrderBy("#tfm_orderby# #tfm_order#","Price")>

<cfif true eq true>

  <!---TOMLR--->

  <cfquery name="dboartikel" datasource="carfree24">

SELECT a.Art_ID,a.Km,a.Price,ArtExtras.Extras

FROM Artikel a

LEFT JOIN ArtExtras ON (a.Art_ID = ArtExtras.Artikel_ID)

           

WHERE (0=0

          

<cfif isDefined ("Url.S_Preisbis")>

 <cfif Url.S_Preisbis NEQ "">

 AND  a.Price <= <cfqueryparam value="#URL.S_Preisbis#" cfsqltype="cf_sql_numeric">

       </cfif>

     </cfif>

<cfif isDefined ("Url.S_Kilometerbis")>

 <cfif Url.S_Kilometerbis NEQ "">

 AND   Km <= #URL.S_Kilometerbis#

      </cfif>

   </cfif>

<cfif isdefined("Url.S_Extras")>

 <cfif Url.S_Extras NEQ "">     

 AND   EXTRAS IN (<cfqueryparam value="#URL.S_Extras#" cfsqltype="cf_sql_numeric" list="yes">)

       </cfif> 

     </cfif> )

       

ORDER BY #tfm_orderby# #tfm_order#

  </cfquery>

  <cfset QueryString_dboartikel=Iif(CGI.QUERY_STRING NEQ "",DE("&"&CGI.QUERY_STRING),DE(""))>

</cfif>

<cfset MaxRows_dboartikel=10>

<cfset StartRow_dboartikel=Min((PageNum_dboartikel-1)*MaxRows_dboartikel+1,Max(dboartikel.RecordCount,1))>

<cfset EndRow_dboartikel=Min(StartRow_dboartikel+MaxRows_dboartikel-1,dboartikel.RecordCount)>

<cfset TotalPages_dboartikel=Ceiling(dboartikel.RecordCount/MaxRows_dboartikel)>

<cfscript>

//sort column headers for dboartikel

tfm_saveParams = "";

tfm_keepParams = "";

if(tfm_order EQ "ASC") {

        tfm_order = "DESC";

}else{

        tfm_order = "ASC";

}

if(ListLen(tfm_saveParams)GT 0) {

        tfm_params=ListToArray(tfm_saveParams,",");

        For (i=1; i LTE ArrayLen(tfm_params); i=i+1) {

                if(isDefined(tfm_params[i])) {

                        tfm_temp = tfm_params[i];

                        if(isDefined("form." & tfm_params[i])) tfm_temp = "form." & tfm_params[i];

                        tfm_keepParams = tfm_keepParams &  LCase(tfm_params[i]) & "=" & URLEncodedFormat(Evaluate(tfm_temp)) & "&";

                }

        }

}

tfm_orderbyURL = CGI.SCRIPT_NAME & "?" & tfm_keepParams & "tfm_order=" & tfm_order & "&tfm_orderby=";

</cfscript>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

</head>

 

<body>

 

<cfoutput>#dboartikel.RecordCount#</cfoutput>

<cfoutput query="dboartikel" group="#tfm_orderby#" startRow="#StartRow_dboartikel#" maxRows="#MaxRows_dboartikel#">

  <tr><td colspan="3">

  <div>

  <table width="300px" border="2" cellpadding="0" cellspacing="0" bordercolor="##00FF00">

    <tr>

      <td colspan="2"> </td>

      <td><a href="#tfm_orderbyURL#Km">Kilo</a></td>

      <td><a href="#tfm_orderbyURL#Price">Preis</a></td>

 </tr>

<tr>

      <td height="142" colspan="2">

  <cfoutput>#dboartikel.Art_ID#</cfoutput></br>

  <cfif #Extras# NEQ "">

    #extras#,

  </cfif>

   </td>

   <td>#dboartikel.Km#</td>

   <td>#LSCurrencyFormat(dboartikel.Price)#</td>

    </tr>

  </table>

  </td>

  </tr>  

</cfoutput>

</body>

</html>

Open in new window

0
Comment
Question by:Panos
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22824955
i recon both - your "wrong" recordcount and "extra" results - are because of this line:
AND   EXTRAS IN (<cfqueryparam value="#URL.S_Extras#" cfsqltype="cf_sql_numeric" list="yes">)

the IN operator will select ALL records that match ANY of the values in the list.

you need to loop through #URL.S_Extras# list and create a series of "AND Extras = xxx" statements instead of using the IN operator:

<cfif isdefined("Url.S_Extras") AND listlen(url.s_extras)>
  <cfloop list="#url.s_extras#" index="extra">  
   AND EXTRAS = <cfqueryparam value="#extra#" cfsqltype="cf_sql_numeric">
  </cfloop>
</cfif>


btw, you can easily combine your paired <cfif> statements into one:
instead of
<cfif isDefined ("Url.S_Preisbis")>
 <cfif Url.S_Preisbis NEQ "">
(and other similar code lines)

just use
<cfif isDefined ("Url.S_Preisbis") AND Url.S_Preisbis NEQ "">
CF uses short-cut evaluation of compound if statements. in the above case it means that if the first statement (isDefined) is false the second statement will not even be looked at by CF.
0
 
LVL 2

Author Comment

by:Panos
ID: 22825246
Hi azadisaryev.
In tried to change my question but you did already make the post and so i did not.
I followed the tutorial on http://tutorial150.easycfm.com/ and so i changed the group to:
group="art_ID". and the output:
<cfif #Extras# NEQ "">
     <cfoutput>
 #Extras#,
 </cfoutput>
</cfif>
Also i copied your code in my SELECT query.
Now i have the same problem with recordcount and when i select:
S_Extras=10&S_Extras=20 i have no results.

0
 
LVL 8

Expert Comment

by:eszaq
ID: 22842664
Of course, there won't ber any records matching your WHERE clause if you follow suggestion to loop through list #url.s_extras# like this <cfloop>AND EXTRAS =..</cfloop>. This code will generate WHERE clause as:
AND EXTRAS=1 AND EXTRAS=2

No record can can have EXTRAS match two values at the same time.  If you want your query to return any result at all then, you have to generate either
AND EXTRAS IN (1,2)
either
AND (EXTRAS=1 OR EXTRAS=2)
Please note proper use of parenthises (makes whole lot of difference in SQL). If you get too many records add more specific conditions to your WHERE clause.

In any case, before you start banging your head on the wall with coldfusion code, make sure you have written database query that returns results you need. Only after that you start working on the code that would generate query dynamically. Always start you work at the data level.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22846960
I don't see anywhere in your query where you try to limit it to just art_Id=1

how about adding the clause...

 AND   ART_ID = <cfqueryparam value="#ART_ID#" cfsqltype="cf_sql_numeric">
0
 
LVL 2

Author Comment

by:Panos
ID: 22848081
Hi gdemaria.
Why Art_ID?
This is the values i'm trying to find out.Which art_ID in table artikel have the specified in Url (S_Extras)values in table ArtExtras(relationship one to many) .

Hi eszaq.
If you mean i must change the code to:
<cfif isdefined("Url.S_Extras") AND Url.Extras NEQ "">  
 AND   EXTRAS IN (<cfqueryparam value="#URL.S_Extras#" cfsqltype="cf_sql_numeric" list="yes">)
    </cfif>
i have the problem i discribe in my question.
0
 
LVL 8

Accepted Solution

by:
eszaq earned 250 total points
ID: 22848112
I understand that you have a problem with that, but what I am saying is: there is NO WAY your query will return any results if generated query will have something like  
WHERE EXTRAS=1 AND EXTRAS=2
It is impossible, because following this logic you will be trying to find rows where 1=2 (if a=b and a=c then b=c).

This is problem with your SQL, not Coldfusion. You need to post your tables structure and what you need to pull from database in SQL Experts area (skip all the coldfision code there to avoid confusion). Once you get your query straighten up and working in the database move to CFML scripts.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 27

Assisted Solution

by:azadisaryev
azadisaryev earned 250 total points
ID: 22848133
what you want to do is extremely hard, if not impossible, to achieve in one query...
consider instead:
a) run a db query to select records with the EXTRAS IN (<cfqueryparam value="#url.S_Extras#" list="yes">) - it will select both art_id=1 and art_id=2
b) run a QoQ on the above query to select only records which have first url.S_Extras
c) run a QoQ on the db query to select only records that have the second url.S_Extras
d) run a QoQ on the above 2 QoQs , INNER JOINing them on art_id column - this shoudl return you the art_ids that have only both S-Extras

mind you, the above is untested, but should work.

if you are using MySQL db, it has a GROUP_CONCAT() function, which concatenates ROWS into a delimited list - you may play with that and various string functions...
0
 
LVL 8

Expert Comment

by:eszaq
ID: 22848175
Try to select DISTINCT rows:

SELECT DISTINCT a.Art_ID,a.Km,a.Price,ArtExtras.Extras
FROM Artikel a .....

and so on.


0
 
LVL 2

Author Closing Comment

by:Panos
ID: 31510874
Thank you all for help.
regards
Panos
0
 
LVL 8

Expert Comment

by:eszaq
ID: 22848179
but keep
AND   EXTRAS IN ()

if you want to get any records in your resultset
0
 
LVL 8

Expert Comment

by:eszaq
ID: 22848185
i see the a earlier answer is accepted - thanks, but still try to use DISTINCT
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22849949
sorry, i had to reread your question several times, now I think I know what you're going for.  You want the records where BOTH the extras exist.

So that would be this...
<cfquery name="dboartikel" datasource="carfree24">

 SELECT a.Art_ID,a.Km,a.Price

  FROM Artikel a

 WHERE 1=1

<cfif isDefined ("Url.S_Preisbis") and len(Url.S_Preisbis)>

 AND  a.Price <= <cfqueryparam value="#URL.S_Preisbis#" cfsqltype="cf_sql_numeric">

</cfif>

<cfif isDefined ("Url.S_Kilometerbis") and len(Url.S_Kilometerbis)>

 AND   Km <= #URL.S_Kilometerbis#

</cfif>

<cfif isdefined("Url.S_Extras") and len(Url.S_Extras)>

  <cfloop index="aValue" list="#Url.S_Extras#">

   and exists (select top 1 ae.extras from ArtExtras ae where ae.Artikel_ID = a.Art_ID and ae.EXTRAS = <cfqueryparam value="#aValue#" cfsqltype="cf_sql_numeric">)

  </cfloop>

</cfif>

 ORDER BY #tfm_orderby# #tfm_order#

</cfquery>

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
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…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

13 Experts available now in Live!

Get 1:1 Help Now