• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

Populate array from query

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
DBrown
Asked:
DBrown
  • 16
  • 14
  • 4
  • +4
1 Solution
 
AlfaNoMoreCommented:
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
 
Chandramouli kArchitectCommented:
If U use ADO Recordset then u can use GetRows Method to store the records in an array.
0
 
Chandramouli kArchitectCommented:
<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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
AlfaNoMoreCommented:
best to let the ColdFusion server to do all the database connection stuff. Otherwise this page will be sooooo slow.
0
 
cheekycjCommented:
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
 
AlfaNoMoreCommented:
That's a dead easy scripting language! Looks good.
0
 
cheekycjCommented:
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
 
DBrownAuthor Commented:
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
 
cheekycjCommented:
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
 
AlfaNoMoreCommented:
good luck CJ. I'm bailing out now...
0
 
cheekycjCommented:
Thanx :-)
0
 
DBrownAuthor Commented:
4 seperate arrays will be needed for what I am doing C.J


Thanks for your help.


Doug
0
 
cheekycjCommented:
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
 
DBrownAuthor Commented:

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
 
DBrownAuthor Commented:
P.S sorry for making this so confusing, but that is what I am....confused


Doug
0
 
cheekycjCommented:
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
 
DBrownAuthor Commented:
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
 
DBrownAuthor Commented:
Im my DB it would be like so.


<CFQUERY datasource="carnivore" name="getCompParts">
SELECT typeID,partNum,model,salePrice,invNum
FROM components
</CFQUERY>
0
 
cheekycjCommented:
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
 
DBrownAuthor Commented:
That will actually end up being a seperate array. Dont worry about that.




Doug
0
 
cheekycjCommented:
OK.. one last thing.. the arrayIndex and arrayName how are they to be determined?  
0
 
DBrownAuthor Commented:
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
 
cheekycjCommented:
OK how do I know that the data belongs to particular category when I am looping through it.

CJ
0
 
DBrownAuthor Commented:
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
 
cheekycjCommented:
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
 
DBrownAuthor Commented:
C.J

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



Doug
0
 
cheekycjCommented:
email it to me at cheekycj@yahoo.com
0
 
DBrownAuthor Commented:
I just sent it to you C.J you should have it shortly.



Doug
0
 
DBrownAuthor Commented:
C.J It came back because of size limit. It is only 2.5MB though




Doug
0
 
DBrownAuthor Commented:
I just zipped it and sent it again.



Doug
0
 
lpkuahCommented:
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
 
cheekycjCommented:
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
 
cheekycjCommented:
Any progress here?
0
 
DBrownAuthor Commented:
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
 
cheekycjCommented:
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
 
ahosangCommented:
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
 
ahosangCommented:
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
 
cheekycjCommented:
CJ_S didn't participate in this question cheekycj did :-)

CJ
0
 
ahosangCommented:
AAARGH, those darn pesky CJ twins!!!
Of course:

POINTS TO cheekycj
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
EE Admin
0
 
cheekycjCommented:
Thank you.

CJ
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 16
  • 14
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now