http://www.perlmonks.org?node_id=215459


in reply to Sorting hash values according to order of an array

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.

Replies are listed 'Best First'.
Re: Re: Sorting hash values according to order of an array
by mt2k (Hermit) on Nov 24, 2002 at 02:13 UTC
    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'}});

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

      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.