To read the article online, visit http://www.4GuysFromRolla.com/webtech/tips/t061301-1.shtml

Inserting the Contents of a Two-Dimensional Array into a Database


By David G.

As we all know, an array is a special type of variable that can be used to represent a list or table of related values. Basically, it is a numbered series of related items. One popular application of an array is utilizing it in an e-commerce shopping cart.

Now, imagine that you are using a session-level two-dimensional array to keep track of products that are added to your web site shopping cart, where each item in the first dimension represents information about the particular product while each item in the second dimension identifies a product in the shopping cart. For example:

'This user has two items in her shopping cart
'	Silk Shirt
shopping_cart_array( 0, 0 ) = 001
shopping_cart_array( 1, 0 ) = "Silk Shirt"
shopping_cart_array( 2, 0 ) = 50.00
shopping_cart_array( 3, 0 ) = 1
'	Leather Pants
shopping_cart_array( 0, 1 ) = 002
shopping_cart_array( 1, 1 ) = "Leather Pants"
shopping_cart_array( 2, 1 ) = 100.00
shopping_cart_array( 3, 1 ) = 2

Notice that this array kind of looks like a Recordset (a Recordset would have columns describing the properties of each item, and a row for each item in the shopping cart); a two-dimensional array is just another way to conceptualize at a Recordset. In fact, the Recordset object contains a GetRows method, which transforms a Recordset into a two-dimensional array (for more information read: Using GetRows to Quickly Display a Skinny Table). However, there is no built-in method to convert an array into a Recordset.

So, let's examine how we can dump the contents of a two-dimensional array into a Recordset using our shopping cart array example. This script assumes that we already have a two-dimensional array that has been populated with some values. You could simply hard code the creation and population of the array like so:

<%
  'Create a 2d array with 4 "columns" and 10 "rows"
  dim shopping_cart_array(4,10)

  'Add some items to the array
  shopping_cart_array( 0, 0 ) = 001
  shopping_cart_array( 1, 0 ) = "Silk Shirt"
  shopping_cart_array( 2, 0 ) = 50.00
  shopping_cart_array( 3, 0 ) = 1

  shopping_cart_array( 0, 1 ) = 002
  shopping_cart_array( 1, 1 ) = "Leather Pants"
  shopping_cart_array( 2, 1 ) = 100.00
  shopping_cart_array( 3, 1 ) = 2

  shopping_cart_array( 0, 2 ) = 003
  shopping_cart_array( 1, 2 ) = "White Socks"
  shopping_cart_array( 2, 2 ) = 3.00
  shopping_cart_array( 3, 2 ) = 3
  
  ' ...

Now that we have a populated two-dimensional array let's look at how to convert this array into a Recordset. Note that if you use a connected Recordset, adding these contents to the Recordset will have the effect of adding these as rows to the database. The code, with comments, is shown below. Note that the script uses some of the ADO constants, meaning that you'll have to include adovbs.inc in your ASP pages that use this code (for more information on adovbs.inc be sure to read this FAQ).

<%
  ...

  ' open recordset object with the Orders table.  Note that we must
  ' open the Recordset with a lock type OTHER THAN the default, which
  ' is a Read-only lock (adLockReadOnly).  (Note that the code below
  ' assumes that a Connection object has been created and opened (objconn)
  set objrs = server.CreateObject("adodb.recordset")
  objrs.Open "orders", objconn, adOpenKeyset, adLockOptimistic, adCmdTable

  'Now, let's loop through the array
  ' START -- FOR loop
  for i = 0 to ubound(shopping_cart_array,2)
    'This checks to make sure that we only add to the Recordset array
    'elements that have a value
    if localCart(0,i) <> "" then  'START -- IF condition statement

      'Add a new row to the Recordset
      objrs.AddNew
      objrs("orders_id") = shopping_cart_array(0,i)
      objrs("orders_name") = shopping_cart_array(1,i)
      objrs("orders_price") = shopping_cart_array(2,i)
      objrs("orders_qty") = shopping_cart_array(3,i)
      objrs.Update

    end if  'END -- IF condition statement
  next

  ' kill recordset/connection object
  objrs.Close
  set objrs = nothing
  
  objconn.Close
  Set objconn = nothing
%>

In actuality, there is more to explain when it comes to developing a workable session-variable based shopping cart application. The above information simply gives a basic and quick introduction on how to send information from a multi-dimensional array into a database table.

And to conclude... that's all there is to it folks! Happy Programming!

Return to user tips...


Article Information
Article Title: 4GuysFromRolla.com - Inserting the Contents of a Two-Dimensional Array into a Database
Article Author: David G.
Article URL: http://www.4GuysFromRolla.com/webtech/tips/t061301-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers