LinuxQuestions.org - Perl Script /MySQL Problem

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Perl Script /MySQL Problem (https://www.linuxquestions.org/questions/programming-9/perl-script-mysql-problem-401171/)

Perl Script /MySQL Problem

I need some help in accomplishing something in perl as i am new in the language.

BASICS:
--------

Im developing a promotional system that basically accepts promotional entries and stores them in a MYSQL db.
Im conducting the promo where the users have to submit unique promo codes as many times as they wish via a CGI web form, the unique code is then stored.

THE PROBLEM:
-------------

I need to track the number of times a unique user/visitor has submited an entry into a MySQL Db table.

Fields from the table are:

ID FIRSTNAME MIDNAME LASTNAME PROMOTYPE PROMOCODE AGE PHONE EMAIL ADDRESS

I therefore need to find the Most Frequent Participant upto the least Frequent partcipant and their corresponding frequencies using a perl/cgi script.

I already have a developed a script BUT its not reading the Frequencies for each user. You can view it partially working from this URL
http://www.ccskenya.com/cgi-bin/coms...ewallpromo.cgi
Therefore i just need a simple addition of reading EACH users entry and incrementing it everytime then displaying it where labelled No. of times Entered:

NOTE:: Each users entry is unique - so there repetition of users details everytime they submit the Unique code - ONLY the code and promotion type changes BUT other details remain the same.

The script should fetch the ID of the entrant and the No. of entries made so far as is in the DB. e.g. ID - FREQUENCY
EXAMPLE "200 - 34 ENTRIES" - from the MySQL table so that an award can be presented to the MOST frequent entrant.

any help greatly appreciated

Like this ?
select id, count(id) as CNT from table group by promocode
store in hash (using CNT as key), and sort the hash.
Of course you may get more than one id with same cnt, so you may be better off using id as hash key and looping through in Perl to check for highest count ids.

Hey Chrism01 , thanks is it possible u do for me a quick code snippet on how to accomplish the above..here is the code for viewallpromo.cgi script that is working right now.
where should i implement your proposed solution as shown above..??

#! /usr/bin/perl5 --

######DATE::- 18/01/2005

use CGI;
use CGI qw(:cgi-lib);
use CGI::Carp qw(fatalsToBrowser);
&ReadParse;
$in=new CGI;
require("comsell_sub.cgi");
print "Content-Type: text/html \n\n";

print<<"END";
<HEAD><TITLE>ADMINISTRATOR | ALL REGISTERED PROMOTION ENTRIES REPORT</TITLE></HEAD>
END

$sth = $dbh->prepare("SELECT * FROM promotions");
$sth->execute;

$totalpromos=0;

print"PRINTING LIST OF ALL PROMOTIONAL ENTRIES.\n ";

while (@response = $sth->fetchrow())
{
$totalpromos++;

$id =$response[0];
$firstname =$response[1];
$midname=$response[2];
$lastname=$response[3];
$promotype=$response[4];
$promocode=$response[5];
$age=$response[6];
$mobile=$response[7];
$email=$response[8];

print"MOST RECENT PROMO ID IS: $id 
Fullnames: $firstname $midname $lastname 
Promotion Type | Promotion Code: $promotype | $promocode 

Mobile #: $mobile 
Email: $email 
No. of times Entered: Entries 


\n";

print"........................................................................................... .......\n ";
}
print"TOTAL PROMOTION ENTRIES IS : $totalpromos Entries. ";
print"MOST FREQUENT ENTRANT ID is : | FREQUENCY IS : Entries ";
print"2ND MOST FREQUENT ENTRANT ID is : | FREQUENCY IS : ";
print"3RD MOST FREQUENT ENTRANT ID is : | FREQUENCY IS : Entries. ";
print". ";
print". ";
print". ";
print". SO ON ";
print"LEAST FREQUENT ENTRANT ID is : | FREQUENCY IS : Entries. ";