LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
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 01-09-2006, 09:36 AM   #1
domquem
LQ Newbie
 
Registered: Jul 2004
Location: WASHINGTON,DC AREA
Distribution: SUSE LINUX 9.1
Posts: 17

Rep: Reputation: 0
Angry 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

Last edited by domquem; 01-09-2006 at 09:55 AM.
 
Old 01-09-2006, 07:06 PM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
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.
 
Old 01-10-2006, 07:57 AM   #3
domquem
LQ Newbie
 
Registered: Jul 2004
Location: WASHINGTON,DC AREA
Distribution: SUSE LINUX 9.1
Posts: 17

Original Poster
Rep: Reputation: 0
Question

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"<font color=green size=5>PRINTING LIST OF ALL PROMOTIONAL ENTRIES.\n<br></font>";

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"<font color=green>MOST RECENT PROMO ID IS: </font><b>$id</b><br>
<font color=green face=verdana size=2>Fullnames: </font>$firstname $midname $lastname<br>
<font color=green face=verdana size=2>Promotion Type | Promotion Code: </font>$promotype | $promocode<br>
<!--<font color=green face=verdana size=2>Age: </font>$age<br>-->
<font color=green face=verdana size=2>Mobile #: </font>$mobile<br>
<font color=green face=verdana size=2>Email: </font>$email<br>
<font color=green face=verdana size=2>No. of times Entered: </font> Entries<br>

<!--<font color=green face=verdana size=2>CATEGORY ACCESSED</font>: $access<br>
<font color=green face=verdana size=2>MOBILE NUMBER</font>: $phone<br>
<font color=green face=verdana size=2>REGISTRATION DATE/TIME</font>: $regdate<br>-->
\n";

print"<b>........................................................................................... .......\n</b><br>";
}
print"<font color=green SIZE=6><B>TOTAL PROMOTION ENTRIES IS : $totalpromos Entries.<BR></font></B>";
print"<font color=green SIZE=6><B>MOST FREQUENT ENTRANT ID is : | FREQUENCY IS : Entries<BR></font></B>";
print"<font color=green SIZE=6><B>2ND MOST FREQUENT ENTRANT ID is : | FREQUENCY IS :<BR></font></B>";
print"<font color=green SIZE=6><B>3RD MOST FREQUENT ENTRANT ID is : | FREQUENCY IS : Entries.<BR></font></B>";
print"<font color=green SIZE=6><B>.<BR></font></B>";
print"<font color=green SIZE=6><B>.<BR></font></B>";
print"<font color=green SIZE=6><B>.<BR></font></B>";
print"<font color=green SIZE=6><B>. SO ON <BR></font></B>";
print"<font color=green SIZE=6><B>LEAST FREQUENT ENTRANT ID is : | FREQUENCY IS : Entries.<BR></font></B>";

Last edited by domquem; 01-11-2006 at 04:40 AM.
 
  


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
Bugzilla - MySQL - perl - DBD::mysql install problem Runningonair Linux - Software 8 10-12-2007 01:42 AM
perl-DBD-MySQL installation problem kartheekpn Linux - Software 3 09-23-2005 04:01 AM
Converting a Windows Perl script to a Linux Perl script. rubbercash Programming 2 07-19-2004 11:22 AM
Bizarre MySQL vs Perl PROBLEM... ruiseixas Programming 1 02-07-2004 11:10 AM
Problem with a perl script Nexis Linux - Software 3 08-08-2002 09:51 PM


All times are GMT -5. The time now is 07:24 PM.

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