Solved

Store Query in Array and Search in the Array

Posted on 2004-04-05
15
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
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

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

690 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