Solved

Populate array from query

Posted on 2001-09-16
41
503 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Report: Liquid Web beats Amazon, Rackspace & More

A study by performance analyst firm Cloud Spectator finds that Liquid Web beats rivals Amazon, Rackspace and DigitalOcean when it comes to website and cloud application performance.

Question has a verified solution.

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

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…

752 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