Solved

Populate array from query

Posted on 2001-09-16
41
497 Views
Last Modified: 2013-12-16
I was wondering if someone could help me with populating an array from a query, I am new to javascript, and am a little lost. I am designing a website in coldfusion and need to populate a array with this query.

<CFQUERY NAME="getCompParts" DAASOURCE="carnivore">
SELECT partNum,typeID,salePrice,description
FROM components
</CFQUERY>


Thanks
Doug

P.S God bless the USA
0
Comment
Question by:DBrown
  • 16
  • 14
  • 4
  • +4
41 Comments
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6487802
I know no ColdFusion, but basically you'll want to create the array and fill it. The array (once sent to the browser), should look like this:

<!--
var yourarray = new Array()
//-->

and you'll want to fill it by doing this:

yourarray[0] = "record1"
yourarray[1] = "record2"

So, to fill the array, just loop through your recordset, and write the above code to the page...
0
 
LVL 5

Expert Comment

by:kcm76
ID: 6489870
If U use ADO Recordset then u can use GetRows Method to store the records in an array.
0
 
LVL 5

Expert Comment

by:kcm76
ID: 6489876
<Script language="JavaScript">
     function addArray()
     {
          var strConnectionString
          var arrRecSet = new Array
          var ado  =  new ActiveXObject("ADODB.CONNECTION")
          var rs = new ActiveXObject("ADODB.RECORDSET")
          ado.Open strConnectionString;
          rs.Open(strQry,ado);
          arrRecSet = rs.GetRows();
     }
</Script>
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6489902
best to let the ColdFusion server to do all the database connection stuff. Otherwise this page will be sooooo slow.
0
 
LVL 19

Accepted Solution

by:
cheekycj earned 100 total points
ID: 6493874
try this:
<CFQUERY NAME="getCompParts" DAASOURCE="carnivore">
SELECT partNum,typeID,salePrice,description
FROM components
</CFQUERY>
<SCRIPT>
var partNumArray = new Array();
<CFOUTPUT QUERY="getCompParts">
 partNumArray[#currentRow#] = #getCompParts.partNum#;
</CFOUTPUT>
</SCRIPT>

CJ
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6493901
That's a dead easy scripting language! Looks good.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6493913
CF is (hands down) the easiest server side scripting language there is.. Allaire (now Macromedia) tried to make a server side language that is like coding in HTML.

CJ
0
 
LVL 2

Author Comment

by:DBrown
ID: 6493973
cj thanks for responding!! I am confused as to how I will get the index. like below. How do I populate the index of the array by increment.

<script language="javascript">
var arrParts = new Array();
var arrPrice = new Array()
arrParts[0][0] = ('#whatever#');
arrParts[0][1] = ('#whatever#');
arrParts[0][2] = ('#whatever#');

arrPrice[0][0] = ('#whateverPrice#');
arrPrice[0][1] = ('#whateverPrice#');
arrPrice[0][2] = ('#whateverPrice#');
</script>



Doug
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6493993
Hi Doug,
 Do you want a 2D array that represents the entire Query or 4 arrays that represent each of the values returned by the query?

CJ
0
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 6493997
good luck CJ. I'm bailing out now...
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6494004
Thanx :-)
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494013
4 seperate arrays will be needed for what I am doing C.J


Thanks for your help.


Doug
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6494086
try this out:
<SCRIPT>
<CFOUTPUT>
  <CFLOOP LIST="#getCompParts.ColumnList#" INDEX="columnName">
    var #columnName#Array = new Array();
    <CFLOOP QUERY="getCompParts">
      #columnName#Array[#currentRow#] = #Evaluate("getCompParts.#columnName#")#;
    </CFLOOP>
  </CFLOOP>
</CFOUTPUT>
</SCRIPT>

Its dynamic enough that if you change your query it will adjust the code.

CJ
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494175

C.J what I need is similar to the array below. I will need to update prices of a computer system based upon which item is chosen in the configuration. I will also need to track those changes. The only way I know of doing this is to make an array out of each component. What do you think.


<script Language="JavaScript">
var arrPrices = new Array();
var arrCat = new Array();
var arrItems0 = new Array();
  var arrItems0_0 = new Array();
    arrItems0_0[0] = new Array('Space Black', 165);
    arrItems0_0[1] = new Array('Conspiracy Blue', 165);
    arrItems0_0[2] = new Array('Charcoal Black', 103);
    arrItems0_0[3] = new Array('Cyborg Green', 165);
    arrItems0_0[4] = new Array('Plasma Purple', 165);
    arrItems0_0[5] = new Array('Martian Red', 165);
    arrItems0_0[6] = new Array('Saucer Silver', 165);
    arrItems0_0[7] = new Array('Nova Yellow', 165);
    arrItems0_0[8] = new Array('Lunar White', 103);
arrItems0[0] = new Array('Dragon Full-Tower Case (340-Watt PS) - COLOR', -1, 10001);
  var arrItems0_1 = new Array();
    arrItems0_1[0] = new Array('Cyborg Green', 152);
    arrItems0_1[1] = new Array('Plasma Purple', 152);
    arrItems0_1[2] = new Array('Martian Red', 152);
    arrItems0_1[3] = new Array('Nova Yellow', 152);
arrItems0[1] = new Array('ATX Mid-Tower Case (300-Watt PS) - COLOR', -1, 10002);
arrCat[0] = new Array (0, 'case', 0);
var arrItems1 = new Array();
arrItems1[0] = new Array('Intel? Pentium? 4 Processor 2.0GHz 400MHz FSB', 873, 10389, 0);
arrItems1[1] = new Array('Intel? Pentium? 4 Processor 1.8GHz 400MHz FSB', 505, 10275, 0);
arrItems1[2] = new Array('Intel? Pentium? 4 Processor 1.7GHz 400MHz FSB', 430, 10003, 0);
arrItems1[3] = new Array('Intel? Pentium? 4 Processor 1.6GHz 400MHz FSB', 393, 10276, 0);
arrItems1[4] = new Array('Intel? Pentium? 4 Processor 1.5GHz 400MHz FSB', 375, 10004, 0);
arrItems1[5] = new Array('Intel? Pentium? 4 Processor 1.4GHz 400MHz FSB', 355, 10005, 0);
arrItems1[6] = new Array('Intel? Pentium? III Processor 1.0GHz 133MHz FSB SSE ', 369, 10120, 0);
arrItems1[7] = new Array('Intel? Pentium? III Processor 933MHz 133MHz FSB SSE ', 359, 10121, 0);
arrItems1[8] = new Array('Intel? Pentium? III Processor 866MHz 133MHz FSB SSE ', 336, 10108, 0);
arrCat[1] = new Array (1, 'processor', 1);
var arrMBoards = new Array();
  arrMBoards[0] = new Array('Intel 815e Based Motherboard w/1AGP/6PCI SDRAM', '10268');
  arrMBoards[1] = new Array('Intel? 850 Based Motherboard w/1AGP/5PCI/1CNR RDRAM', '10263');
var arrItems3 = new Array();
arrItems3[0] = new Array('128MB SDRAM (PC-133)', 20, 10110);
arrItems3[1] = new Array('256MB SDRAM (PC-133)', 39, 10111);
arrItems3[2] = new Array('384MB SDRAM (PC-133)', 59, 10112);
arrItems3[3] = new Array('128MB RDRAM (PC-800) P4 Only', 52, 10125);
arrItems3[4] = new Array('256MB RDRAM (PC-800) P4 Only', 101, 10126);
arrItems3[5] = new Array('384MB RDRAM (PC-800) P4 Only', 152, 10127);
arrCat[2] = new Array (3, 'memory', 3);
var arrItems4 = new Array();
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494186
P.S sorry for making this so confusing, but that is what I am....confused


Doug
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6494189
that is doable too.

The last code I posted creates a simple 1-D array.
I can modify that to create a 2/3 Dimension array but where are you retrieving the values from .. like 152,10127, etc)

CJ
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494229
Well in the piece of code below which is not something I wrote. But the idea would be in each arrayItem you would have the description, price, invNumber etc. so 873 would be the price and 10839 would be the invNumber.

arrItems1[0] = new Array('Intel? Pentium? 4 Processor 2.0GHz 400MHz FSB', 873, 10389);
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494251
Im my DB it would be like so.


<CFQUERY datasource="carnivore" name="getCompParts">
SELECT typeID,partNum,model,salePrice,invNum
FROM components
</CFQUERY>
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6494255
so model, salePrice and invNum are going to be inserted in to the array.

Where does typeID and partNum go or come into the picture?
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494259
That will actually end up being a seperate array. Dont worry about that.




Doug
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

by:cheekycj
ID: 6494263
OK.. one last thing.. the arrayIndex and arrayName how are they to be determined?  
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494288
as far as arrayName I would like to define that myself. The index of the array I am not sure about. I imagine it will need to be based upon the amount of parts in that particular category, and loop through it to count the items, which would end up being the indexes of the next array.

IE:

arrCat[1] = new Array ('processor');
var arrItems1 = new Array();
arrItems1[0] = new Array('Intel? Pentium? 4 Processor 2.0GHz 400MHz FSB', 873, 10389, 0);
arrItems1[1] = new Array('Intel? Pentium? 4 Processor 1.8GHz 400MHz FSB', 505, 10275, 0);
arrItems1[2] = new Array('Intel? Pentium? 4 Processor 1.7GHz 400MHz FSB', 430, 10003, 0);
arrItems1[3] = new Array('Intel? Pentium? 4 Processor 1.6GHz 400MHz FSB', 393, 10276, 0);
arrItems1[4] = new Array('Intel? Pentium? 4 Processor 1.5GHz 400MHz FSB', 375, 10004, 0);
arrItems1[5] = new Array('Intel? Pentium? 4 Processor 1.4GHz 400MHz FSB', 355, 10005, 0);
arrItems1[6] = new Array('Intel? Pentium? III Processor 1.0GHz 133MHz FSB SSE ', 369, 10120, 0);
arrItems1[7] = new Array('Intel? Pentium? III Processor 933MHz 133MHz FSB SSE ', 359, 10121, 0);
arrItems1[8] = new Array('Intel? Pentium? III Processor 866MHz 133MHz FSB SSE ', 336, 10108, 0);
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6494364
OK how do I know that the data belongs to particular category when I am looping through it.

CJ
0
 
LVL 2

Author Comment

by:DBrown
ID: 6494515
Well the typeID would be like below. I would say that you could loop through the typeID and select all components where the typeID = 'typeID' and then create the array for that particular data from the result.

cpu
vid
mod
nic
cse
hdd
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6494742
Is typeID a string or is it an int.

Are you grouping by something otherwise how do you know as you are going through the query.. which typeID/cat you are going through.  Or do you just hard code it?

CJ
0
 
LVL 2

Author Comment

by:DBrown
ID: 6495094
C.J

Would it be easier if I emailed my database? Then you could see how the keys etc are configured.



Doug
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6497044
email it to me at cheekycj@yahoo.com
0
 
LVL 2

Author Comment

by:DBrown
ID: 6497103
I just sent it to you C.J you should have it shortly.



Doug
0
 
LVL 2

Author Comment

by:DBrown
ID: 6497119
C.J It came back because of size limit. It is only 2.5MB though




Doug
0
 
LVL 2

Author Comment

by:DBrown
ID: 6497142
I just zipped it and sent it again.



Doug
0
 
LVL 1

Expert Comment

by:lpkuah
ID: 6505182
DBrown,

How about calculating the price as the user changes the configuration? The solution I have in mind is simpler.

I think you'll have a form in where you'll populate it with selections..

<form name="frmName" method="post" action="processFile.cfm">

<select name="computerPart" onChange="changePrice()">
   <option value="0"></option>
<cfoutput query="getCompParts">
   <option value="#typeID#">#model#
</cfoutput>
</select>
<br>
<input type="textbox" name="priceBox" value="">
</form>


<script language="Javascript">
function changePrice() {
  if (document.frmName.computerPart.selectedIndex!=0) {
     var itemPrice = 0;
     <cfloop query="getCompParts">
      if (document.frmName.computerPart[document.frmName.computerPart.selectedIndex].value == '#typeID#')
         itemPrice = #salePrice#;
     </cfloop>
  }
  document.frmName.priceBox.value = itemPrice;
}

</script>


The javascript calculates the corresponding price for the selected computer part.




0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6505603
DBrown.. what type of DB is it?

It didn't have an extension and it definitely wasn't ACCESS.

Can export the data to CSV and I can examine it then.

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6524261
Any progress here?
0
 
LVL 2

Author Comment

by:DBrown
ID: 6526032
Sorry CJ been busy. I like the idea above, but will still need to load the info into an array. I do not like the idea of having seperate text boxes with the price. This will be for computer systems and not seperate components. Just a simple array would be fine. As long as I can get the price updated when they choose an upgrade or downgrade on a component.



Doug

0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6530742
OK give a me a sample of what a query resultset would look like (say 3 rows returned) and what the corresponding array would look like (in your vision) and then we can work on the behaviour of updating everything appropriately.

CJ
0
 
LVL 12

Expert Comment

by:ahosang
ID: 7968651
This question has been abandoned. I will make a recommendation to the moderators on its resolution in a week or so. I appreciate any comments that would help me to make a recommendation.
 
In the absence of responses, I may recommend DELETE unless it is clear to me that it has value as a PAQ. Silence = you don't care
 
ahosang
0
 
LVL 12

Expert Comment

by:ahosang
ID: 8029733
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

points to CJ_S
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
ahosang
EE Cleanup Volunteer
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8035164
CJ_S didn't participate in this question cheekycj did :-)

CJ
0
 
LVL 12

Expert Comment

by:ahosang
ID: 8036220
AAARGH, those darn pesky CJ twins!!!
Of course:

POINTS TO cheekycj
0
 
LVL 5

Expert Comment

by:Netminder
ID: 8092829
Per recommendation, force-accepted.

Netminder
EE Admin
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8093960
Thank you.

CJ
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article shows how to create and access 2-dimensional arrays in JavaScript.  It includes a tutorial in case you are just trying to "get your head wrapped around" the concept and we'll also look at some useful tips for more advanced programmers. …
In my daily work (mainly using ASP.net), I need to write a lot of JavaScript code. One of the most repetitive tasks I do are the jQuery Ajax calls. You know: (CODE) I don't know if for you it's the same, but for me is soooo tedious to write the …
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

746 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

13 Experts available now in Live!

Get 1:1 Help Now