Perl: Sorting by occurrence of specific character

Poetics · 09-26-2007, 01:08 PM

I have data in the following format pulled from a MySQL database in an array:

$array[0] = firstname -- lastname -- city -- favorites

I would like to find a way to sort by # of favorites (they are comma-separated) if possible, so that someone with 8 favorites comes before someone with 4, regardless of length (a solution proposed elsewhere).

I've tried playing with my SQL query to sort by the # of commas in the 'favorites' field, but that hasn't lead anywhere either (that would be a very simple fix, should I get it to work).

I've looked at using tr/// but I can't quite figure out how to sort by its results. Any ideas?

lakris · 09-26-2007, 01:54 PM

Could You give an example of the MySQL table, Your SQL-statement, or the output of Your SQL-statement?
A text file with a line for each person?
Does it have to be Perl?

Poetics · 09-26-2007, 02:07 PM

Unfortunately it does need to be perl. An example of the MySQL table is below.

Code:

+-------+----------+-----------------------------------------+
| fname |   lname  |               favorites                 |
+-------+----------+-----------------------------------------+
| rob   | baker    | Ice cream, candy, parties               |
| marky | mark     | The Funky Bunch, all-star concerts      |
| jeff  | goldblum | String theory, Science, apes, Dinosaurs |
+-------+----------+-----------------------------------------+

I hope that's legible

I've found a way to do it using a complex series of foreach loops but I'd rather not keep that as it's pretty convoluted. Basic gist of it goes [check # of commas] -> [put # in front of string] -> [sort into new array] -> [remove count from string] -> [print]

If I could do it all in the MySQL query it'd be fantastic and I can cut most of the perl out of the project (to reduce complexity and server CPU time). What I have currently "works" but isn't very pretty.

lakris · 09-26-2007, 03:17 PM

Oh, sorry, haven't been doing any Perl for over 7 years. I'm sure I could whip something up in bash really quick (use exec?) hehe,

,
It feels as if the correct MySQL statement could do the trick. I'll have a look at it.
Maybe a change in db-design could let you keep track of how many favorites are in each row?

Poetics · 09-26-2007, 03:58 PM

It's been suggested that I change the DB design, but unfortunately that's not in my hands; in addition, the non-programmers who are going to be inputting data to the form want to learn as little MySQL as possible so they currently cut and paste their commands for insertion. *shrug*

Currently with MySQL I'm running into the stumbling block that I can't find the correct way to call count() to return the # of a particular character (in this case, commas), and use that value for sorting.

matthewg42 · 09-26-2007, 04:46 PM

I'm not sure I have the correct data structure, but if I have, you can use this:

Code:

#!/usr/bin/perl 

use warnings;
use strict;

my @array;

$array[0] = [ "rob", "baker", "Ice cream, candy, parties" ];
$array[1] = [ "marky", "mark", "The Funky Bunch, all-star concerts" ];
$array[2] = [ "jeff",  "goldblum", "String theory, Science, apes, Dinosaurs" ];

print "Before sort:\n";
dump_data(@array);

my @array2 = sort my_cmp @array;

print "\nAfter sort:\n";
dump_data(@array2);

sub my_cmp {
        my @a_favs = split(/,/, $a->[2]);
        my @b_favs = split(/,/, $b->[2]);
        return $#a_favs <=> $#b_favs;
}

sub dump_data {
        foreach my $row (@_) {
                printf "%-10s %-10s %s\n", @$row;
        }
}