Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Store Query in Array and Search in the Array

I've 4 coloums in the database.

Number | Position | TYPE | VALUE

I'd like to store the output of the database query into an array. And later on output only one value from the array.

e.g the value where number is 100 and position is 100 and type is 1

for this i've to search thrue the array. Ist this possible? And if... how?

Thx 4 help!
0
Netwing
Asked:
Netwing
  • 5
  • 4
  • 3
  • +2
3 Solutions
 
mrichmonCommented:
Well you could just use WHERE clauses so that you only pull the record that you are going to output.  This would be a lot more efficeint.

Or if you need all of the data for some reason, then when you want to do the output you could just do a query of queries and put the where clauses on that query so that you grab the exact record you need.

These would both be better than searching through an array of values.
0
 
jyokumCommented:
you can reference a query recordset as an array

#queryName[row].columnName#

to get the 2nd record you could do something like

#myQuery[2].Number# #myQuery[2].Position#
0
 
Seth_BienekCommented:


...Or do a query-of-a-query to get the data out of the original query results.

<cfquery name="getRow" dbtype="query">
SELECT
  Number,Position,TYPE,VALUE
FROM
  query1
WHERE
  Number = 100
  AND Position = 100
  AND Type = 1
</cfquery>

Regards,

Seth
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mrichmonCommented:
My first post suggests using a query of queries and applying the where clauses there to get the resutls.
0
 
NetwingAuthor Commented:
Thx 4 your suggestions but i cant use them for various reasons. i know that it would be a lot more efficient if i could use select .... where ... and  or use subqueries. but i cant use them trust me (in no newby). if i can't store the query into an array and later on search in the array  as i described the only solution for me is to write 4 each output a select query and this would end up in ~1000 selectqueries with output and thats not ideal. so please help me because i havend found anything to search in an array. i know its possible in php..... is it in cf?
0
 
mrichmonCommented:
well you could write your own  method to search an array.

But, tell us what it is you are trying to do that makes you need to search through a query and maybe we can come up with an alternate solution that will help.
0
 
Seth_BienekCommented:
You can use the ArrayFind function by Nathan Distenfass:
http://www.cflib.org/udf.cfm?ID=715

You might pay special attention to the performance hit this route takes, because searching through large arrays (using any approach, in any language), is not going to be nearly as efficient as the suggestions that were given above.

But hey, if they won't work, they won't work. :)

Good luck,

Seth
0
 
Seth_BienekCommented:
PS: Even using StructFind would be quite abit faster, if it's possible to keep your data in a Structure as opposed to an Array.
0
 
NetwingAuthor Commented:
@Seth_Bienek

hmmm StructFind sounds interesting.... i'll try
0
 
Seth_BienekCommented:
If you have a choice, I would definitely go with StructFind().

Structures are designed to be searched, whereas Arrays are designed strictly to be iterated through or accessed by position, which is why you give up so much performance when you search an Array.

Good luck,

Seth
0
 
NetwingAuthor Commented:
Maybe its possible with structfind or one of the struct functions. Ill try.
But if someone is faster than me it will help ;-)

....so here is my problem: Ive a form with ~1000 input fieds which should be prefilled with data from a database if he open the form a second time. I know the users number. so my query is:

<cfquery name="PreFill" datasource="OPL1">
      SELECT  Position, TYPE, VALUE
      FROM AAERF.LE_PROZ_ERG
      WHERE NUMBER = '11111'
</cfquery>

so now ive to prefill the values of the input fields

From each field (they are not ordered) i know the Position Nr (e.g 100) and the Type (e.g A)
....so how can i prefill the inputfield with the value?

Can i solve my problem with one of the Struct Functions and when - How? A code excample would be fine. I've never worked with the Struct Functions and the Info in the Reference isnt very good in this point
0
 
NetwingAuthor Commented:
if it helps to understand my problem here is the code: http://www.tomgruber.com/downloads/problem.txt
0
 
NetwingAuthor Commented:
uuuuuuhhh.... im soo stupid ;-) ive found a solution for my problem:

<cfquery name="PreFill" datasource="OPL1">
      SELECT  *
      FROM AAERF.LE_PROZ_ERG
      WHERE KST = '11111'
</cfquery>


<cfloop query="PreFill">
<cfif PreFill.PROZ_NUM IS 107 AND PreFill.TYP IS "D"><cfoutput>#PreFill.WERT#</cfoutput></cfif>
</cfloop>


why do it simple if you can make it complex ;-)
0
 
robinwoodCommented:
Ill split the Points ;-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now