call from same DB, different fields

[sastjohn]

The subject may seem confusing, but it is very simple really. I am new to CF and I am trying to utilize the same DB, but call different values based on where the content is on the page. I have found a way for it to work, but it seems clumsy and extraneous at best. This is the way I have found to do it:

<cfquery name="getGame25" datasource="dbtest">

SELECT  gameID, gameName, gameDev, gameYear, gameConsole

FROM tblGames

WHERE gameID = 25

</cfquery>

<cfquery name="getGame24" datasource="dbtest">

SELECT  gameID, gameName, gameDev, gameYear, gameConsole

FROM tblGames

WHERE gameID = 24

</cfquery>

<cfquery name="getGame23" datasource="dbtest">

SELECT  gameID, gameName, gameDev, gameYear, gameConsole

FROM tblGames

WHERE gameID = 23

</cfquery>

......

<cfoutput query="getGame25">

<div class="showcaseText"><p><h1>#gameID#. #gameName#</h1></p></div>

<table>

<tr>

<td>Developer:</td><td>#gameDev#</td>

</tr>

<tr>

<td>Dev Year:</td><td>#gameYear#</td>

</tr>

<tr>

<td>Console:</td><td>#gameConsole#</td>

</tr>

</table>

</cfoutput>

	<cfoutput query="getGame24">
	<div class="showcaseText"><p><h1>#gameID#. #gameName#</h1></p></div>
	<table>
	<tr>
	<td>Developer:</td><td>#gameDev#</td>
	</tr>
	<tr>
	<td>Dev Year:</td><td>#gameYear#</td>
	</tr>
	<tr>
	<td>Console:</td><td>#gameConsole#</td>
	</tr>
	</table>

...you get the idea..

I am wondering if there is a better way ( a cfloop perhaps) that would cut down on the amount of code and the multiple queries to the DB based on wanting to pull just that specific ID for a specific table. Thanks in advance!

[mack_]

select *

from table

where in in (10, 11, 12)

would get you a query with 3 or less rows. After that you can loop over

this query and output the entry only if it matches your specific ID (one

way to do it).

[sastjohn]

Thanks for the reply. I need to pull 25 different records based on a specific ID number for each. I am building a list of 25 video games (best of 1-25) and they are listed in the DB with a numbered ID (1-25). As the page is scrolled, starting at 25 and going to 1, each listing showcases the number, the title, the dev year, the developer, and the console. This is all listed in the DB row for each gameID. I kind of see what you are saying with the three or less rows, but I actually need all 25 rows in the query for the info to polpulate.

[mack_]

If the sorting order cannot be determined from the other fields in the

table you can use:

select * from table where id = 1

union

select * from table where id = 2

....

union

select * from table where id = 25

and just output the query

[sastjohn]

Thanks! I think this is what I'm looking for! Wasn't aware of the "union" aspect

[sastjohn]

I tried the UNION call out. It is still rendering all 25 fields in DB where I need just the one for each <td>. I think I'm stuck with a DB query for each individual spot on the page, otherwise, it grabs all of the table listings and displays them all. I did try this:

<cfscript>

function GetQueryRow(query, rowNumber) {

var          i = 0;

var          rowData = StructNew();

var          cols          = ListToArray(query.columnList);

for (i = 1; i lte ArrayLen(cols); i = i + 1) {

rowData[cols] = query[cols][rowNumber];

}

return rowData;

}

</cfscript>

...it worked, but, again, it rendered all the IDs in the DB and as it calls, it returns a struct, which I don't need.