Looping through a query 1

ma701sd · Feb 7, 2001

Hi,
Im trying to loop through a query in a database by performing a search for a keyword in all records in a database. This works fine i.e. given a keyword, N number of records are found. However, I also want to display the username responsible for each row. What happens is that two records are found but it only displays one username for two distinct records when it should display two usernames (one for each row). Can any1 help?

Thanx

Sam...

GunJack · Feb 7, 2001

Hey Sam,

Do you mean that the query outputs two rows with different values for one field but the username field shows the same value for both rows?

If you can post the output code as well as your initial query, that would help spot the problem.

GJ

tleish · Feb 7, 2001

You have to set the "QUERY" attribute in the <CFOUTPUT> tag so that it knows it's looping through a query, otherwise it returns the first result of a query.

<CFQUERY NAME="qMyQuery" DATASOURCE="myDataSource">
SELECT UserName FROM myTable WHERE myCell = 'Test'
</CFQUERY>


<CFOUTPUT>#qMyQuery.RecordCount#</CFOUTPUT><BR><BR>


<CFOUTPUT QUERY="qMyQuery">
#qMyQuery.UserName#<BR>
</CFOUTPUT>

ma701sd · Feb 8, 2001

Hi Gunjack,

Yep, Thats exactly what I mean.
Below is the code :
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<CFQUERY NAME ="WebPageResults" DATASOURCE="Online">
SELECT title,heading,section1heading1,section1text1,section2heading2,section2text2
FROM UserSpecific
WHERE title LIKE '%#Form.Criteria#%'
</cfquery>

<html>
<head>
<title>Webpage collection results</title>
</head>

<body>


<CFIF #form.criteria# is "">
Im sorry, no results match the criteria you entered
<CFELSE>
<CFOUTPUT>#WebPageResults.RecordCount# user websites Found for "#Form.Criteria#".<BR></cfoutput>
<CFLOOP QUERY="WebPageResults">
<CFOUTPUT>
#title# 1<BR> 
#username# 2<BR>
</cfoutput>
</CFLOOP>
</cfif>

</body>
</html>

You might notice, ive put in a number 1 and 2 after each of the variables #title# and #UserName# respectively.
It displays two different title but it displays the same username for both titles when there are two usernames associated to the two titles.

Many thanx for your help GJ...Much appreciated

Sam....

SteelDragon · Feb 8, 2001

Kathryn,

Here is the code from my online knowledge base search results. It displays all of the query results for a given search, my other code is at home, I'm on a laptop now, I'll post the other later if this is of no assistance.

<CFQUERY name="SearchResult" dataSource="support" username="######">
SELECT DISTINCT ABCard.Card_ID AS ResultField1, ABCard.Summary AS ResultField2, ABCard.Card_ID AS ID_Field, ABCard.ChangeDate AS DetailField4
FROM ABCard
WHERE #PreserveSingleQuotes(Criteria)#
Order by ABCard.ChangeDate Desc
</CFQUERY>
<CFOUTPUT query="SearchResult">
<TR bgcolor="#IIf(CurrentRow Mod 2, DE('ffffff'), DE('cccccc'))#">
<TD><font face='Tahoma'>#ResultField1#</font></TD>
<TD></TD>
<TD><A href="support_Detail.cfm?ID=#URLEncodedFormat(ID_Field)#"><font face='Tahoma'>#ResultField2#</font></A></TD>
<TD><Font face='Tahoma'>#DetailField4#</FONT></TD>

</TR>
</CFOUTPUT>

SteelDragon

GunJack · Feb 8, 2001

Hey Sam,

Try changing your variables inside the cfoutput tags to #WebPageResults.username#. Although I can't reproduce it now, I seem to remember having problems in the past with <cfloop> not scoping the query variables correctly.

If this doesn't fix it, modify your criteria to exclude the row that matches the username being displayed and see what happens. That should force it to display the other username. If that works, put the criteria back and reverse the sort order.

Let me know what happens as I don't see anything wrong that would cause that problem.

GJ

ma701sd · Feb 9, 2001

Hey GJ,
It works like a dream i.e. it loops through all records and displays each username and one of its columns that ive decided to output, now the next problem...
What I then do within the loop is to create a link to an action page which will display the record to extract other info, however, the variable passed to the next page is the first username variable...Should it not pass the username variable that I select i.e. if I click A then dispays A's record, else if I click B then it should display B's record?
Currently, regardless of me clicking A's link or B's Link, it still extracts A's record.

I did use a CFOUTPUT tag on the action page to display what varaible is being passed and its displaying A's. On the action page, I am running a query to

select *
From UserSpecific
Where uername=#UserName#
But if username is always A, then it will extract A's record...

Any ideas,

Cheers
Sam...

GunJack · Feb 9, 2001

Hey Sam,

It sounds like the problem is in your code that creates the link. I would check the same thing and make sure when you create your dynamic link, you're using #queryname.username# and not just #username#. If that's not it, post the code where you're creating the link and I'll take a look.

GJ

ma701sd · Feb 9, 2001

Hey GJ

I think whats happening is that the link is generated for the last record that is found, but I though the loop would take care of that i.e. generate the correct username bacause that is what the loop does...loop through all records and display all users...So I guess it mut be the link. I did put in the #queryname.username# but it still does not work..Ive posted the code below:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<CFQUERY NAME ="WebPageResults" DATASOURCE="Online">
SELECT username, title,heading,section1heading1,section1text1,section2heading2,section2text2
FROM UserSpecific
WHERE title LIKE '%#Form.Criteria#%'
</cfquery>

<html>
<head>
<title>Webpage collection results</title>
</head>

<body>


<CFIF #form.criteria# is "">
Im sorry, no results match the criteria you entered
<CFELSE>


<CFLOOP QUERY="WebPageResults">
<CFOUTPUT>
A website created by #WebPageResults.username# was Found for "#Form.Criteria#".<BR>
#title# 1<BR>
#username# 2<BR>
This website was created by <a href="WebSearchSiteView.cfm?#username#=#username#">#webpageresults.username#</a><BR><BR>
</cfoutput>
</CFLOOP>
</cfif>

</body>
</html>

ANy ideas??

Cheers

Sam...

GunJack · Feb 9, 2001

Hehe, I think I see the problem. You put the queryname on the visible part of the link but not the part the browser actually uses

Try this:
<a ref= "WebSearchSiteView.cfm?username=#webpageresults.username#">
#webpageresults.username#</a>

GJ

ma701sd · Feb 9, 2001

Hey GJ,
Thanx for the speedy response, It works but something weird is happening...

The variable being passed to the action page is weird.

If I perfrom a dynamic query provided by the #queryname.username# on the previous it still does not display the correct record, however, If I hard code the username I want into the query then it works yet it still displays the username that I dont want ( i checked this by displaying the variable within CFOUTPUT tags), I'm not even passing it!...or am I?

Let me give you an example,
Theres two users in the DB, A and B, A is the first record in the DB and B is the second. I Perform a search which displays one record A. I pass this username (A) onto the third action page by providing the query with the Variable value A but it still displays B, Yet if I hardcode the Username A into the query on the third page then it displays A's record but somehow it still displays B's username as the vaiable (I did this as a test to make sure that A's user name was being passed and not B's)???

Ive posted the code:

Page one is the search page.

Page two is what you have been helping me with but I'll post it again:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<CFQUERY NAME ="WebPageResults" DATASOURCE="Online">
SELECT username, title,heading,section1heading1,section1text1,section2heading2,section2text2
FROM UserSpecific
WHERE title LIKE '%#Form.Criteria#%'
</cfquery>

<html>
<head>
<title>Webpage collection results</title>
</head>

<body>


<CFIF #form.criteria# is "">
Im sorry, no results match the criteria you entered
<CFELSE>


<CFLOOP QUERY="WebPageResults">
<CFOUTPUT>
A website created by #WebPageResults.username# was Found for the criteria "#Form.Criteria#".<BR>

#title# 1<BR>
This website was created by <a href="WebSearchSiteView.cfm?#username#=#webpageresults.username#">#webpageresults.username#</a><BR><BR>

</cfoutput>
</CFLOOP>
</cfif>
</body>
</html>

Page three:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<CFOUTPUT>
<H1>#Username#</h1>
</cfoutput>

<CFQUERY DATASOURCE= "ONLINE" NAME="RecreateUserPage">
SELECT *
FROM UserSpecific
WHERE Username= 'A'
</CFQUERY>

<CFOUTPUT>
<H1>#Username#</h1><BR>
</cfoutput>

As you can see, on this page, I am hardcoding the username A yet as a check I am seeing what variable is being displayed it its displaying B so in essence, If I pass a variable, It will pick up B as the username to seacrh on...

I hope this makes sense??

Cheers

Sam

GunJack · Feb 9, 2001

Hey Sam,

I think you have another variable defined somewhere else called "username" that has "b" as it's value. I think that if you again scope the output variable with the query name, you'll see the correct value. Do you by chance have a session, client, application, or possibly another query with a username field that might be taking precendence?

You could try something like <cfoutput>#variables.username# - #form.username# - #cookie.username# - #session.username# - #client.username# - #application.username# ....</cfoutput> and see what happens. You will almost assuredly get an error but just delete out the variables it can't find untill you either find another username variable or eliminate every possible scope. I think you will find though that username is defined somewhere else.

Hope this helps,
GJ

ma701sd · Feb 9, 2001

Hey GJ,

U are right, I used a process of elimination and found that I got errors for all scoped variables apart from client.username.

So what I'm doing is using CFPARAM to ceate a default value for username and use that as a criteria for the query,
But it still somehow manages to use the client.username variable. This si what Ive done to try an fix this problem but it doesnt work, do you have any more ideas?

<CFIF ISDEFINED ('Client.UserName')>
<CFPARAM NAME= "usernametemp" DEFAULT="">
</CFIF>


<CFQUERY DATASOURCE= "ONLINE" NAME="Visitorsiteview">
SELECT *
FROM UserSpecific
WHERE Username= '#usernametemp#'
</CFQUERY>

<CFOUTPUT>
#Usernametemp#<BR>
#Visitorsiteview.columnlist#<BR>
#Visitorsiteview.currentrow#<BR>
#Visitorsiteview.recordcount#<BR>
</cfoutput>

GunJack · Feb 9, 2001

Hey Sam,

I'm a little confused as that last code is different from the earlier code. What is the problem you're trying to solve at this point? In regards to the code above, the cfparam will always create usernameTemp and set the value to "". If you use this in your query, I wouldn't think you would match any records since they would unlikely have a NULL username value.

GJ

rmz8 · Feb 9, 2001

Sam,

GunJack has been very helpful. Let him know by voting for him as TipMaster.

Ryan ;-]

ma701sd · Feb 10, 2001

Hi Ryan, Definately, GJ has been very help and patient with me whenever I have posted a question and has always pretty much got me out of all my stick situations!, theres no hesitation on my part to vote for GJ...Umm....How do I do it??

GJ, You have been most helpful and I appreciate it. Thanx.
WIth regards to th CF Param thing, What I tried to do was to delete the client.username variable that was being picked up somehow (It was late and I was doing silly programming !). I think Ive deleted it now so Im not having that problem.
WHat I've done now is loop through the query and create a link to each record to a second page to view the particular username record, however when I pass the Var to the nxt page, the username variable is not being displayed therefore the query to search for a username variable (that should be passed is not being performed.

Below is the revised code:
Page 1:
<CFLOOP INDEX = "client.username" LIST="#GetClientVariablesList()#">
<CFSET deleted=deleteClientVariable("client.username&quot

>
</cfloop>

<CFOUTPUT>#client.username#....If I cant see anything then Ive deleted the client variable<BR><BR></cfoutput>

<CFQUERY NAME ="WebPageResults" DATASOURCE="Online">
SELECT username, title,heading,section1heading1,section1text1,section2heading2,section2text2
FROM UserSpecific
WHERE title LIKE '%#Form.Criteria#%'
</cfquery>

<html>
<head>
<title>Webpage collection results</title>
</head>

<body>


<CFIF #form.criteria# is "">
Im sorry, no results match the criteria you entered
<CFELSE>


<CFLOOP QUERY="WebPageResults">
<CFOUTPUT>
<BR>A website created by #WebPageResults.username# was Found for the criteria "#Form.Criteria#".<BR>

<FORM ACTION="WebSearchSiteView1.cfm?#username#=#webpageresults.username#" METHOD="POST">
<INPUT TYPE="Hidden" NAME="#username#" Value="#username#">
<INPUT TYPE="submit" VALUE="View">
</form>

</cfoutput>
</CFLOOP>
</cfif>

Im using hidden variables and within the input tags, the it displays both usernames (I have two username in the DB and I am verifying this by viewing "Source" in IE5.

But the action pae, I use a CFOUT to view #username# but it does not display anything. Heres the action page code snippet:
<cfoutput>"#form.username#"</CFOUTPUT>

<CFQUERY DATASOURCE= "ONLINE" NAME="Visitorsiteview">
SELECT *
FROM UserSpecific
WHERE Username= '#username#'
</CFQUERY>

<CFOUTPUT>
#Username#<BR>
#Visitorsiteview.columnlist#<BR>
#Visitorsiteview.currentrow#<BR>
#Visitorsiteview.recordcount#<BR>
</cfoutput>


<CFOUTPUT QUERY= "Visitorsiteview">
<HTML>
<HEAD>
<TITLE>#title#</TITLE>
</HEAD>

GunJack · Feb 11, 2001

Hey Sam,

I don't think you're really deleting the client variable so much as setting it to blank. If you've really deleted it, the line below

<CFOUTPUT>#client.username#....If I cant see anything then Ive deleted the client variable<BR><BR></cfoutput>

should generate an error because a truly deleted variable would not be defined. If it outputs the text "... If I cant' see ..." then it would indicate to me that client.username still exists but is set to NULL. To loop through and delete all the client variables, I think this is what you want.

<CFLOOP INDEX = "x" LIST="#GetClientVariablesList()#">
<CFSET deleted=deleteClientVariable(#x#)>
</cfloop>

I think your problem at this point is still that client.username is interferring with your other code but this time, it's simply blank.

Let me know if I'm missing something.
GJ

GunJack · Feb 11, 2001

Oh, I almost forgot, thanks to both of you for the kind words!

GJ

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Looping through a query 1

IS-IT--Management

ISP

Programmer

IS-IT--Management

Programmer

ISP

IS-IT--Management

ISP

IS-IT--Management

ISP

IS-IT--Management

ISP

IS-IT--Management

ISP

Programmer

IS-IT--Management

ISP

ISP

Similar threads

Log in

Part and Inventory Search

Sponsor