asked on

Problem in recordcount with group

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

azadisaryev

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.

Panos

ASKER

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.

eszaq

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.

gdemaria

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">

Panos

ASKER

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.

ASKER CERTIFIED SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

azadisaryev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eszaq

Try to select DISTINCT rows:

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

and so on.

Panos

ASKER

Thank you all for help.
regards
Panos

eszaq

but keep
AND EXTRAS IN ()

if you want to get any records in your resultset

eszaq

i see the a earlier answer is accepted - thanks, but still try to use DISTINCT

gdemaria

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