The CCDLookup function provides a convenient way of retrieving single field values from a database table within code. However, there sometimes arises the need to retrieve multiple fields in a row or even multiple rows in a table. While it is possible to make multiple calls to the CCDLookup function to retrieve each field individually, a more efficient way to go about it would be to execute a single SQL query which returns a recordset containing all the retrieved values.

In ASP, you can use the CCOpenRS function which returns a recordset containing the database values retrieved based on an SQL statement. The CCOpenRS function is defined as:
Function CCOpenRS(RecordSet, SQL, Connection, ShowError)

In the following example, the CCOpenRS function is used to populate a recordset and then a while loop is used to retrieve and print out all the rows in the recordset.

dim SQL, rsResults
SQL = "select task_id, task_name, priority_id, project_id from tasks"

'DBIntranet is the connection class
CCOpenRS rsResults, SQL, DBIntranet.Connection, true

'Loop through the rows in the recordset
while not rsResults.EOF
response.write "<br>Task ID: " & rsResults.fields("task_id")
response.write "<br>Task Name: " & rsResults.fields("task_name")
response.write "<br>Priority D: " & rsResults.fields("priority_id")
response.write "<br>Project ID: " & rsResults.fields("project_id")
rsResults.MoveNext
wend

The rsResults.EOF returns false when there are no more rows to traverse while rsResults.fields returns the value of a particular field in a row.

In PHP, a similar approach is used but instead of the CCOpenRS function, the query member function of the connection class is used.

$SQL = "select task_id, task_name, priority_id, project_id from tasks";

// $DBintranet is the connection class.
global $DBintranet;
$DBintranet->query($SQL);

'Loop through the rows in the recordset
while($DBintranet->next_record())
{
print "<br>Task ID: " . $DBintranet->f("task_id");
print "<br>Task Name: " . $DBintranet->f("task_name");
print "<br>Priority D: " . $DBintranet->f("priority_id");
print "<br>Project ID: " . $DBintranet->f("project_id");
}

The $DBintranet->next_record() function returns false when there are no more rows to traverse while $DBintranet->f() returns the value of a particular field in a row.