Check data in database one row at a time

[cherphas]

Hi all,

I just did the switched from ASP to Coldfusion and although they are very similar, there are things I don't quite understand.

First, in ASP, when you create a RecordSet and want to check or display the info from a database, it gets the data one row at a time with the help of a LOOP and Move.Next until there is no more data. But in Coldfusion, I can only get all the info at once. For example, On my web page I have to check for Login name and Passwork, so I have an Access table with a login field and a Passwork field. When a user submits his Login and Pass, I want to check if the info is in the database. if the Login and Pass match, he has access.
How do I do this in coldfusion?

Joël

 

[csteinhilber]

I'm confused... you say you want to check the resultset one row at a time... but then in the example you give, there should theoretically only be one row... so you're essentially checking the whole resultset anyway.

You should be able to do something like:

<CFQUERY NAME=&quot;userLogin&quot; ...>
  SELECT password
  FROM userTable 
  WHERE username = &quot;#FORM.username#&quot;
</CFQUERY>

<CFIF userLogin.RecordCount GT 0 AND userLogin.password EQ FORM.password>
   Congratulations!<br />
   You have been logged in.
<CFELSE>
   You entered an incorrect username or password.<br />
   You could not be logged in.
</CFIF>

Or, if you're really worried about the query returning more than one row (ie - user is entered several times), there are several methods to stepping through a CFQUERY resultset.

One is a CFLOOP, another is CFOUTPUT. Which you use depends on whether you want to manipulate the data, or simply display it. Sounds like you want to manipulate it (or at least validate it)... so you'd probably want the former.

Something like:

<CFQUERY NAME=&quot;userLogin&quot; ...>
  SELECT password
  FROM userTable 
  WHERE username = &quot;#FORM.username#&quot;
</CFQUERY>

<CFSET validPassword = false>
<CFLOOP QUERY=&quot;userLogin&quot;>
    <CFIF password EQ FORM.password>
        <CFSET validPassword = true>
    </CFIF>
</CFLOOP>

<CFIF validPassword>
   Congratulations!<br />
   You have been logged in.
<CFELSE>
   You entered an incorrect username or password.<br />
   You could not be logged in.
</CFIF>

If you simply wanted to output the recordset, you could use the query parameter for the CFOUTPUT tag, like:

<CFQUERY NAME=&quot;userLogin&quot; ...>
  SELECT password
  FROM userTable 
  WHERE username = &quot;#FORM.username#&quot;
</CFQUERY>

<table cellpadding=&quot;5&quot;>
<tr><td>User Name</td><td>Password</td></tr>
<CFOUTPUT QUERY=&quot;userLogin&quot;>
    <tr><td>#username#</td><td>#password#</td></tr>
</CFOUTPUT>
</table>

Hope it helps,
-Carl

 

[CFHub]

There is a method to loop through each row as well.

<cfquery datasource=&quot;yours&quot; name=&quot;MyQuery&quot;>
SELECT stuff, morestuff FROM MyTable
</cfquery>

Some useful variables attached to your returned query

<cfoutput>
Records Returned #MyQuery.RecordCount#
Columns Returned #MyQuery.ColumnList#
This Record &quot;number&quot; #MyQuery.CurrentRow#
</cfoutput>

An interesting way to display the &quot;third&quot; row

<cfoutput>
#MyQuery['stuff'][3]
</cfoutput>

A method of looping through the recordset

<cfloop From=&quot;1&quot; to=&quot;#MyQuery.RecordCount#&quot; index=&quot;ThisRow&quot;>
<cfloop list=&quot;#MyQuery.ColumnList#&quot; index=&quot;ThisColumn&quot;>
<cfoutput>
#MyQuery[ThisColumn][ThisRow]#
</cfoutput>
</cfloop>
</cfloop>

 

[cherphas]

Well, one of the reasons I want to display the info one row at a time is because I want to place a bullet(that I created) beside each row of info. another example:

I have two columbs in my table database, ID and Title. I want to display each Title as a link with a bullet beside it. This will be used as a side menu, like this:

-Title one
-Title two
-Title three
etc.

So I thought by having this...

<cfquery name=&quot;get_pass&quot; datasource=&quot;login_pass&quot;>
SELECT * FROM Sheet_Spec_Titles ORDER BY ID
</cfquery>

<cfloop QUERY=&quot;get_pass&quot;>
<img src=&quot;bullet-arrow.jpg&quot; width=&quot;8&quot; height=&quot;10&quot;>
<cfoutput query=&quot;get_pass&quot;>
#get_pass.Title#<br>
</cfoutput>
</cfloop>

...it would enter the loop, display one row with bullet beside it, re-enter loop, display row 2, etc.

if I don't loop, it will just display the whole column, with one bullet beside it. Any ideas?

 

[cherphas]

Well, one of the reasons I want to display the info one row at a time is because I want to place a bullet(that I created) beside each row of info. another example:

I have two columbs in my table database, ID and Title. I want to display each Title as a link with a bullet beside it. This will be used as a side menu, like this:

-Title one
-Title two
-Title three
etc.

So I thought by having this...

<cfquery name=&quot;get_pass&quot; datasource=&quot;login_pass&quot;>
SELECT * FROM Sheet_Spec_Titles ORDER BY ID
</cfquery>

<cfloop QUERY=&quot;get_pass&quot;>
<img src=&quot;bullet-arrow.jpg&quot; width=&quot;8&quot; height=&quot;10&quot;>
<cfoutput query=&quot;get_pass&quot;>
#get_pass.Title#<br>
</cfoutput>
</cfloop>

...it would enter the loop, display one row with bullet beside it, re-enter loop, display row 2, etc.

if I don't loop, it will just display the whole column, with one bullet beside it. Any ideas?

 

[r937]

<cfloop QUERY=&quot;get_pass&quot;>
<img src=&quot;bullet-arrow.jpg&quot; width=&quot;8&quot; height=&quot;10&quot;>
<cfoutput query=&quot;get_pass&quot;>
#get_pass.Title#<br>
</cfoutput>
</cfloop>

that's not going to work the way you think

when you use CFOUTPUT on query results, the contents of the CFOUTPUT are processed for each row -- in other words, looping is &quot;built in&quot; or implied

therefore the CFLOOP you have around the CFOUTPUT is unnecessary (not sure, but i suspect you'll get n-squared output lines)

here's how i would do it --

<p>
<CFOUTPUT QUERY=&quot;get_pass&quot;>
<img src=&quot;bullet-arrow.jpg&quot; width=&quot;8&quot; height=&quot;10&quot;>
#get_pass.Title#<br />
</CFOUTPUT>
</p>

(notice how the <p>...</p> enclosed the multiple lines of output... yes, i am a stickler for valid html... and yes, there will be a <br /> immediately before the closing </p>)

actually i would not do it that way at all, i would do the following --

<ul>
<CFOUTPUT QUERY=&quot;get_pass&quot;>
<li>#get_pass.Title#</li>
</CFOUTPUT>
</ul>

and then use css to define the list bullet as the image

rudy