Re: creating a SQL statement from an array

well... first you need an array of field names for the data... I will assume the second number ie the 36 in ( 169 , 36 ) is the column (field specifier) and the first number IE 169 is the row number, and that you put the field names in an array @field_names. With that I would do the following...

my $oldrow;
my(@fields,@values);
while(<DATA>) {
  chomp;
  my ($row,$col,$data) = /^\s*\(\s*(\d+)\s*,\s*(\d+)\s*\)\s*=>(.*)$/;
  # that puts the row number in $1, col number in $2 and data in $3, a
+nd in the variable respectively
  if($oldrow != $row) {
    if(@fields) { #make sure there is data
      $query = "INSERT INTO tablename(".(join ',', @fields)." VALUES("
+.(join ',', @values).')';
      ... do DB calls ...
      @fields = @values = ();
    }
    $oldrow = $row;
  }
  push @fields, $field_names[$col]; # field name with proper data
  push @values, db->quote($data); #run data through DB quoter
}
[download]

so you go through, adding data to @fields and @values in order so they match up, then when you come to a new row number, you build your query (works with MySQL, think it will work in others) and execute it, then clear the fields and values and go onto next row. Hope that is clear enough
- Ant

Comment on Re: creating a SQL statement from an array Download Code

Replies are listed 'Best First'.
Re: Re: creating a SQL statement from an array by JSchmitz (Canon) on Apr 16, 2001 at 21:42 UTC
so if the original SQL statement is: SELECT junk.name, st.site_type, junk.location, junk.mapref FROM junksite junk, site_type st WHERE junk.site_type_id = st.id the arrays would map as follows `name -> 0<br> site_type -> 1<br> location -> 2<br> mapref -> 3<br>` [download] so from there you can write `$array_ref = $sth->fetchall_arrayref( [0, 2] );` [download]	[reply] [d/l] [select]