ColdFusion: Access Query Result Set for Specific ID

Question

I have a query;

    <cfquery datasource="forms" name="departments">
        select * from quizes.tb_depts
    </cfquery>

This will return a result set. I want to access a specific result set where the ID (Primary Key) for this row is What I input.

Example departments[13] should get the result in the result set where ID=13.

Is there a builtin way to do this? I saw departments.id[rownumber] which is not what I want.

Thank you

Do query of query, I don't think, there is some built in way to get that — JS Mitrah, Dec 23 '15 at 16:20
Why do you not want to do the way using row number? Because you want the entire row? That's the best way to do it without adding unnecessary overhead. — Matt Busche, Dec 23 '15 at 16:49
Using the rownumber is a bad idea because it is not guaranteed to return the row you really want. — Dan Bracuk, Dec 23 '15 at 17:22
Here the situation. I have loop that goes over the department ids. within that loop, I want to do departments[id] and get the row for the particular id. I am thinking of just constructing an array for it myself that will have the index as ID and value as the entire row. Unless there is a better solution. — Saad A, Dec 23 '15 at 17:27

score 5 · Answer 1 · edited May 23 '17 at 12:31

There's a couple of things you need:

A WHERE clause in your query, to filter the results;
A <cfqueryparam> element to protect you from SQL injection (More on SQL injection: How does the SQL injection from the "Bobby Tables" XKCD comic work?).

First, let's assign your ID to a variable, since it probably won't be the same every time you execute this query:

<cfset departmentID = 13 >

Now adjust your <cfquery> block as follows:

<cfquery datasource="forms" name="departments">
    select *
    from quizes.tb_depts
    where id = <cfqueryparam value="#departmentID#" cfsqltype="cf_sql_numeric">
</cfquery>

Note: As a matter of best practice, you'll want to scope your variables in the <cfqueryparam>. For a simple variable assignment as I've done here, it would be #VARIABLES.departmentID#. If this query were in a function in which the ID were passed as an argument, it would be #ARGUMENTS.departmentID#.

score 3 · Accepted Answer · answered Dec 23 '15 at 20:06

Here is What I ended up doing. I apologize my wording wasn't clear.

<cfoutput>
    <cfquery datasource="cehssmforms" name="departments">
        select id, dept 
        from cehssmquizes.tb_depts 
        Order by id
    </cfquery>

    <cfset d = structNew()/>
    <cfloop query="departments">
        #departments.id# - #departments.dept# <br>
        <cfset d[departments.id] = departments.dept > 
    </cfloop>

    <cfdump var="#d#" >
    #d[2]#
</cfoutput>

I have 1 query department that gets all the department information. I wanted all this packaged in a associative array/struct so I can access the information by id column.

So for example

ID Name

12 ABC

22 EFC

33 GTF

when I do a read query I want to get all the records one time only and use them as I need them in different places of my project.

d[12] = ABC

d[33] = GTF

I hope its clear. I apologize for the confusion

score 2 · Answer 3 · answered Dec 23 '15 at 19:08

This can be another workaround:

<!--- Query Object --->
<cfset departments = queryNew(
    "dept_ID, dept_name",
    "INTEGER, VARCHAR,",
    [
        { dept_ID: 9, dept_name: "Department 9" },
        { dept_ID: 13, dept_name: "Department 13" },
        { dept_ID: 22, dept_name: "Department 22" },
        { dept_ID: 55, dept_name: "Department 55" }
    ]
)>

<!--- Get a comma delimited list of ID's --->
<cfset listOfID = valueList(departments.dept_ID, ",")>

<!--- Your required ID --->
<cfset requiredID = 13>

<!--- Get its index in list. ListFind will return 0 if it is not found. --->
<cfset indexOfID = listFind(listOfID, requiredID)>

<!--- Found --->
<cfif indexOfID>
    <cfdump var="#departments.dept_name[indexOfID]#">
</cfif>

Nice one. This is even faster than using a hash table. – Alex Dec 23 '15 at 20:13 — Alex, Dec 23 '15 at 20:13

score 0 · Answer 4 · answered Dec 23 '15 at 18:20

Mapping is your best friend and will be noticeably faster than Query of Query for this use case.

<!--- make up some data --->
<cfset departments = queryNew(
    "ID     , Name   , Staff",
    "INTEGER, VARCHAR, INTEGER",
    [
        { ID: 12, Name: "Department A", Staff: 20 },
        { ID: 14, Name: "Department B", Staff: 22 },
        { ID: 33, Name: "Department C", Staff: 17 }
    ]
)>

<!--- map primary key from result set --->
<cfset departmentsMap = {}>
<cfloop query="departments">
    <cfset departmentsMap[departments.ID] = departments.currentRow>
</cfloop>

<!--- let's pick some random departments --->
<cfset depToPick = [ 33, 12, 77, 14 ]>

<cfloop array="#depToPick#" index="depID">

    <!--- skip departments that are not in the result set --->
    <cfif not structKeyExists(departmentsMap, depID)>
        <cfcontinue>
    </cfif>

    <!---
        fetch fields to display using the query's [column][row] accessor,
        departmentsMap[depID] returns the row index that corresponds to the the mapped primary key
    --->
    <cfset depName  = departments["Name"][departmentsMap[depID]]>
    <cfset depStaff = departments["Staff"][departmentsMap[depID]]>

    <cfoutput>#depName# (ID: #depID#) has a total of #depStaff# staff members.<br></cfoutput>

</cfloop>

score -1 · Answer 5 · edited Dec 23 '15 at 16:45

-1

I would use a Query of a Query

<cfquery name="getDept" dbtype="query">
SELECT *
FROM departments
WHERE id = 13
</cfquery>

Just a few other notes, it is really bad to use a * in a select query for a number of reasons...

you are probably returning more data in the result set then you need, thus increasing network traffic and memory usage
ODBC has a fun bug where it caches the order of the columns when using a * and if you add a column to that table it can cause some very odd results*

and I am sure there are more reasons, so I would really think about undoing that...

edited Dec 23 '15 at 16:45

James A Mohler

11,060
15
46
72

answered Dec 23 '15 at 16:20

tigeryan

162
7

thank you I will take that into account. However the method your purposed would require me to execute a lot of query, approximately 50 queries. Is that good practice? Because I am running loop where I have the ids 1-50 and I want to get the name from the departments query where the Id match. I know I can use join, but I am using CFWheels where I stuck in a situation that requires me to use this method. – Saad A Dec 23 '15 at 16:27
Why use query of query and add overhead? The best solution is to add a `where` clause to the original query. – Scott Stroz Dec 23 '15 at 16:54
Explain exactly how cfwheels is preventing you from using a join. – Dan Bracuk Dec 23 '15 at 17:25
I thought he wanted to do the first query and then cherry pick from that recordset on the page for multiple purposes to save DB round trips... – tigeryan Dec 23 '15 at 17:26
Here the situation. I have loop that goes over the department ids. within that loop, I want to do departments[id] and get the row for the particular id. I am thinking of just constructing an array for it myself that will have the index as ID and value as the entire row. Unless there is a better solution. – Saad A Dec 23 '15 at 17:27
@Dan http://stackoverflow.com/questions/33657887/cfwheels-map-table-to-another-database-database-tablename I have another question where I ran into mapping issue for a different datasource and that problem still exist. – Saad A Dec 23 '15 at 17:28
The more you say the less clear you become. Does the first query actually return all the required information? – Dan Bracuk Dec 23 '15 at 17:37
@Dan sorry for that. Looking at the answer I think my wording wasn't very clear. I think I got the functionality that I wanted, It wasn't suppose to be something complex at all. Please see my answer – Saad A Dec 23 '15 at 19:58

ColdFusion: Access Query Result Set for Specific ID

5 Answers5