http://qs321.pair.com?node_id=215458

mt2k has asked for the wisdom of the Perl Monks concerning the following question:

Okay, I am working on a function that inserts a row into a MySQL table. Now, I am trying to make this thing slightly intelligent. So I have a hash that looks like the following:

my %TABLES = (      # Hash identifying one database
  'users' => [      # Hash keys are the database's tables
    'username',
    'password',     # Columns of the table in an array
    'first_name',
    'email_address',
    'rating'
  ]
);

[download]

Next I have a subroutine in my module that is called like the following:

$handle->insert (
  -table        =>  "users",
  -primary_key  =>  "username",
  -values       =>  { 
    username      =>  "mt2k",
    first_name    =>  "Nathan",
    email_address =>  'email@example.net',
    password      =>  crypt("password", $salt),
    rating        =>  10
  }
);

[download]

Next comes the part I am having problems with. Here is a sample part that is close to that of my subroutine:

sub insert {
  my ($self, %q) = @_;

  # Set up placeholders
  my $ph = join ', ', ('?') x values %{$q{'-values'}};

  # Prepare the query for the database
  my $sth = $self->{DB}->prepare(
    "INSERT INTO $q{'-table'} VALUES($ph)"
  );

  # This is the line that causes problems. Read below for more.
  $sth->execute(values %{$q{'-values'}});

[download]

If you didn't catch on immediately, the problem lies in the fact that I am supplying the values of the hash to execute(). This means they are not in the correct order that MySQL requires them to be in. This is where the %TABLES hash comes in. This hash contains the arrays that hold the correct order in which the values must be passed as placeholders in the execute() statement.

So what I need to do is take the values of %{$q{'-values'}} and return them sorted in the correct order as identified by %TABLES.

I hope I've been clear enough in order to recieve some answers to this question which has perplexed me. Thanks in advance!

Replies are listed 'Best First'.
Re: Sorting hash values according to order of an array
by tadman (Prior) on Nov 24, 2002 at 01:51 UTC
    The simple trick to fix this is to supply the list of columns as well, such as:
    "INSERT INTO $q{'-table'} (".join(',', @columns).") VALUES ($ph)"
    You can make a list of columns based on your hash. It's unfortunate that you've prefixed them with a dash, though.
    my @columns = map { substr($_,1) } keys %{$q{'-values'}};
    That's the basics of it.

    I'm not sure why you're quoting your placeholders. That's supposed to be done automatically, if required, by DBI.

    Update:
    I must've misread your code, because what I meant was this:
    my @columns = keys %{$q{'-values'}};
    I didn't notice that you hadn't prefixed your columns with dashes, unlike your properties.
[reply]
[d/l]
[select]
      my @columns = map { substr($_,1) } keys %{$q{'-values'}};
      Um, all that does is create a list of the columns with the first letter of the column name missing...

      Anyhow, about specifying the columns in any order in the SQL query, would the following code always work correctly?

        my $ph = join ', ', ('?') x values %{$q{'-values'}};
  my $sth = $self->{$db_handle}->prepare(
    "INSERT INTO $q{'-table'} (" .
    join(',', keys %{$q{'-values'}}) .
    ") VALUES($ph)"
  );
  $sth->execute(values %{$q{'-values'}});

      [download]

      All I'm really asking is whether the order of keys %hash and values %hash are always the same in perspective to each other...

      Update: lol, as soon as I finished posting this post, your update was there :) I understand now why you supplied the code you did. It was to get rid of the hyphens you thought were there :)

[reply]
[d/l]
        I've been reassured by many that yes, the order will be the same. I think the only condition is that you don't do anything crazy like change the hash between calling values and keys. This isn't an issue as long as you have a single thread, but with threads now being more supported, your mileage may vary.

        It's pretty risky to just throw things in to a table without naming names. You could delete a column and replace it with a different one with the same type, and you'd be populating the wrong columns with nary a hint of an error.
[reply]
(bbfu) (hash slice) Re: Sorting hash values according to order of an array
by bbfu (Curate) on Nov 24, 2002 at 03:36 UTC

    As an alternative to tadman's solution, you could also use a hash slice, like so: 

    # Everything else is the same as your original code
$sth->execute(@{$q{'-values'}}{@{$TABLES{$q{'-table'}}}});

# Or, a little more readably using temp vars:
my $table   = $q{'-table'};
my @columns = @{$TABLES{$table}};
my %values  = %{$q{'-values'}};

$sth->execute(@values{@columns});

    [download]

    bbfu
    Black flowers blossum
    Fearless on my breath

[reply]
[d/l]

Back to Seekers of Perl Wisdom