LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 09-26-2007, 02:08 PM   #1
Poetics
Senior Member
 
Registered: Jun 2003
Location: California
Distribution: Slackware
Posts: 1,181

Rep: Reputation: 49
Perl: Sorting by occurrence of specific character


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?
 
Old 09-26-2007, 02:54 PM   #2
lakris
Member
 
Registered: Sep 2004
Location: Stockholm, Sweden
Distribution: Ubuntu, RedHat, SuSe, Debian, Slax
Posts: 102

Rep: Reputation: 15
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?

Last edited by lakris; 09-26-2007 at 03:03 PM.
 
Old 09-26-2007, 03:07 PM   #3
Poetics
Senior Member
 
Registered: Jun 2003
Location: California
Distribution: Slackware
Posts: 1,181

Original Poster
Rep: Reputation: 49
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.
 
Old 09-26-2007, 04:17 PM   #4
lakris
Member
 
Registered: Sep 2004
Location: Stockholm, Sweden
Distribution: Ubuntu, RedHat, SuSe, Debian, Slax
Posts: 102

Rep: Reputation: 15
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?

Last edited by lakris; 09-26-2007 at 04:45 PM.
 
Old 09-26-2007, 04:58 PM   #5
Poetics
Senior Member
 
Registered: Jun 2003
Location: California
Distribution: Slackware
Posts: 1,181

Original Poster
Rep: Reputation: 49
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.
 
Old 09-26-2007, 05:46 PM   #6
matthewg42
Senior Member
 
Registered: Oct 2003
Location: UK
Distribution: Kubuntu 12.10 (using awesome wm though)
Posts: 3,530

Rep: Reputation: 63
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;
        }
}
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting all files of home folder modified after a specific date for backup fc6_user Linux - General 2 03-30-2007 03:24 AM
Perl: replace character in file vippie Programming 4 03-23-2007 05:26 AM
Perl Array Sorting craig467 Programming 8 10-02-2006 06:32 AM
perl post character escaping kapilcool Programming 3 12-20-2005 08:42 AM
How do I map a key sequence to a specific character? QtCoder Linux - General 0 11-28-2003 01:03 PM


All times are GMT -5. The time now is 12:24 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration