Solved

Store Query in Array and Search in the Array

Posted on 2004-04-05
15
452 Views
Last Modified: 2013-12-24
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
Comment
Question by:Netwing
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 10758249
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
 
LVL 12

Accepted Solution

by:
jyokum earned 50 total points
ID: 10759294
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10761330


...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
Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

 
LVL 35

Expert Comment

by:mrichmon
ID: 10761624
My first post suggests using a query of queries and applying the where clauses there to get the resutls.
0
 
LVL 2

Author Comment

by:Netwing
ID: 10763731
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
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 50 total points
ID: 10766590
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
 
LVL 5

Assisted Solution

by:Seth_Bienek
Seth_Bienek earned 50 total points
ID: 10766630
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10766637
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
 
LVL 2

Author Comment

by:Netwing
ID: 10766678
@Seth_Bienek

hmmm StructFind sounds interesting.... i'll try
0
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10766771
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
 
LVL 2

Author Comment

by:Netwing
ID: 10772972
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
 
LVL 2

Author Comment

by:Netwing
ID: 10773022
if it helps to understand my problem here is the code: http://www.tomgruber.com/downloads/problem.txt
0
 
LVL 2

Author Comment

by:Netwing
ID: 10773115
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
 

Expert Comment

by:robinwood
ID: 10773140
Ill split the Points ;-)
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Forbidden errors 5 156
JVM encoding. How to change encoding. 27 120
apply ddos protection on all network interface 2 127
Best practices for sharing large amounts of data 9 107
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question