Solved

Store Query in Array and Search in the Array

Posted on 2004-04-05
15
447 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now