cfloop

[drmaves]

The goal is to insert a record into table 'atd', which contains a document id
and an associate id, for each document (38) and associate (150). So the end
result should be 5700 records. The following code inserts a record for each
associate and document but the document id doesn't change, it's always the
first document id in the table. I end up with 5700 records but the document id
is the same for all of them.

What am doing wrong?

<cfquery name="SelectDocuments" datasource="#application.dsn#">
SELECT *
FROM document
WHERE associate_id = 1
</cfquery>

<cfquery name="GetAssociates" datasource="#application.dsn#">
SELECT id
FROM associate
WHERE type = 'Associate'
</cfquery>


<cfloop query="SelectDocuments">
<cfoutput query="GetAssociates">
<cfquery name="AddDocumentRelationships" datasource="#application.dsn#">
INSERT INTO atd
(associate_id,
document_id,
display
)
VALUES
(#GetAssociates.id#,
#SelectDocuments.id#,
'Y'
)
</cfquery>
</cfoutput>
</cfloop>

[akola]

I have one query that gets all records where the login is not defined. I then
want to reset the order for all the records to one, and then set new ordering
for the records starting with 1 again. the problem I am encountering is that
It is looping through all the records and setting the new order to 6 which is
the total number of records. Can anyone help?
<cfquery name="GetOrdering" datasource="BusOff" Username="BusOff"
Password="asproi">
SELECT Ordering
FROM logins
WHERE salesconsultant = '1'
and loginID <> #LOGINID#
order by Ordering
</cfquery>

<cfset Neworder = 1>
<cfloop query="GetOrdering">
<cfquery name="UpdateOrdering" datasource="BusOff" Username="BusOff"
Password="asproi">
UPDATE logins
SET Ordering = #Neworder#
where loginID <> #LOGINID#
</cfquery>
<cfset Neworder = Neworder + 1>
</cfloop>

[gwgiswebmaster]

it appears youre updating every record on each increment, which would explain why they all equal the same.

[kt03]

I have to compare the ?name? from tb1 and ?name? from tb2?.
The ?tb1? table has the following fields (dn, uid, c_name, c_time, m_time )
The ?tb2? table has the following fields: (Name, Ref, Act).
Process: If record found in tb1, the Directory field value for that record
shall be YES, else NO. I have my code up and run but it takes forever to
generate the report. Attached is my code, please fell free to modify or any
advice would be much appreciated


<cfquery name="gettbl" datasource="#db#" timeout="600">
select *
from tb1
</cfquery>
<cfoutput>
<table width="735" cellpadding="0" cellspacing="0" border="1">
<tr>
<th>Directory</th>
<th>UID</th>
<th>Name</th>
<th>Reference Number</th>
<th>Activation Code</th>
<th>C Name</th>
<th>C Time</th>
<th>M Time</th>
</tr>
<cfloop query="gettbl1">
<cfset UID ="#uid#">
<cfset name ="#name#">
<cfset cn ="#c_name#">
<cfset ct ="#c_time#">
<cfset mt ="#m_time#">

<cfquery name="getname" datasource="#db#" timeout="600">
SELECT Name, Ref,Act
FROM tb2
</cfquery>

<cfloop query="getname">
<cfset e_name = "#name#">
<cfset Ref ="#Reference#">
<cfset Act ="#Activation#">

<cfif name_string EQ dn>
<tr>
<td>Y</td>
<td>#UID#</td>
<td>#Ref#</td>
<td>#Act#</td>
<td>#name#</td>
<td>#cn#</td>
<td>#ct#</td>
<td>#mt#</td>
</tr>
<cfelse>

<tr>
<td>N</td>
<td>#UID#</td>
<td>#Ref#</td>
<td>#Act#</td>
<td>#name#</td>
<td>#cn#</td>
<td>#ct#</td>
<td>#mt#</td>
</tr>
</cfif>

</cfloop>
</cfloop>
</cfoutput>

[jdeline]

One thing that might help is to move your getname query outside the loop. See
below.



<cfquery name="getname" datasource="#db#" timeout="600">
SELECT Name, Ref,Act FROM tb2
</cfquery>

<cfloop query="gettbl1">
<cfset UID ="#uid#">
<cfset name ="#name#">
<cfset cn ="#c_name#">
<cfset ct ="#c_time#">
<cfset mt ="#m_time#">

[kt03]

Tried that but it din't help, any other sugesstions? tb1 has about 55xx records, tb2 has about 26xx records. I don't think because amount of data but may be my code isn't right.

Thanks

[jdeline]

If tb 1 has 55xx records, that means the <cfloop query="gettbl1"> loops 55xx
times and your <cfquery name="getname" datasource="#db#" timeout="600">
executes 55xx times. I can't believe moving it outside the loop didn't make any
difference.

[kt03]

I don't know either but I did try your sugesstion and it didn't make any diffrent. Would be other another part of my code cause the problem?

[ayhanyildiz]

use subqueries.

[mxstu]

kt03,

If you mean your tables contain 5500 and 2600 records, then I think the total
number of loops in the code is over 14 million times ... literally. If this is
the case, it is not suprising the code takes a long time ... what it IS
suprising is that it works at all ;-)


I have to compare the ?name? from tb1 and ?name? from tb2?.
The ?tb1? table has the following fields (dn, uid, c_name, c_time, m_time )
The ?tb2? table has the following fields: (Name, Ref, Act).
Process: If record found in tb1, the Directory field value for that record
shall be YES, else NO.

If the data in the two tables is related by a common column then you need to
use a SQL JOIN to retrieve the correct information. Right now all the code
does is basically output a
[url]http://www.google.com/search?hl=en&q=define%3Acartesian+product&btnG=Goo gle+Sear[/url]
ch, which is almost certainly NOT what you want.

What is the relationship between the two tables? Are you trying to retrieve
records where the tb1.Name = tb2.Name? If you can you provide more information
.... or better yet .... an example of the values you want to output, someone can
help you design a better query.

[shastamatt]

Hello,

I am trying to allow a user to search for records, based on their criteria,
which will load a form. This form will show how many records sets fit the
criteria, which record (3 of 40) they are on and 30+ fields of information
based on the id of the record set.

I have a cached query which retieves a list of record id's based on a
information posted from a form. I cannot, for the life of me, figure out how
to navigate through the id's, how do I load the form with the "next" record or
"prior" record?



Thanks in advance,

Matt

<cfif action IS "sortrecs">
<cfset lengthquery="">
<cfset looppos="">
<!--create list--->
<cfquery datasource="#ds#" name="idlist"
cachedwithin="#CreateTimeSpan(0,0,30,0)#">
SELECT dbo.tblClientInformation.clientID
FROM dbo.tblClientInformation
WHERE tblClientInformation.ClientCaseTypeID=#form.casety pe#
</cfquery>
<!--get the length of the list--->
<cfoutput query="idlist">
<cfset recordsetnum=#idlist.recordcount#>
</cfoutput>
<!--put comma in for deliminator--->
<cfset beglist=ValueList(idlist.ClientID)>
<cfset looppos=#ListGetAt(beglist,1, ",")#>
<cfloop from="1" to="#lengthquery#" index="looppos">
<cfquery datasource="#ds#" name="sortedData">
SELECT dbo.tblClientInformation.*, dbo.tblCaseType.CaseType
FROM dbo.tblClientInformation INNER JOIN dbo.tblCaseType ON
dbo.tblClientInformation.ClientCaseTypeID = dbo.tblCaseType.CaseTypeID
</cfquery>
</cfloop>

</cfif>