RE: fast,efficient query for counting
You can avoid the step of extracting the row data by doing:
"UPDATE mytable SET numclicks = numclicks + 1"
-Kevin
-Original Message-
From: rory oconnor [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 20, 2002 8:44 AM
To: [EMAIL PROTECTED]
Subject: fast,efficient query for counting
I'm setting up some tracking stats for (opt-in) e-mail campaigns, and
one of the things I'm tracking is click-thrus. I'm going to re-direct
traffic thru a script that will just count the number of clicks, and
store that number in a mysql table.
It needs to be fast and efficient, and I'm somewhat of a mysql newbie,
so I was wonderinf if there is any more efficient way to simply add a
number to the existing number in that table with mysql and perl. My way
seems like a lot of code to do a little task. Any help is appreciated!
# set up the db connection and SQL
$dbh =
DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpa
ssword",{'RaiseError'=>1});
$select_number = "select total_clicked from $table";
# actually execute the checking query
$sth = $dbh->prepare("$select_number");
if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
# loop thru the query and set the result variables
while (my $ref = $sth->fetchrow_arrayref) {
$total_clicked = $$ref[0];
}
# add 1 to total_clicked
$total_clicked=$total_clicked+1;
# update the number
$update_number = "UPDATE $table set total_clicked = $total_clicked";
# actually execute the update query
$sth = $dbh->prepare("$update_number");
if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: fast,efficient query for counting
$dbh =
DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1});
$update_number = "UPDATE $table set total_clicked = total_clicked + 1";
$sth = $dbh->prepare("$update_number");
if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
--Greg Johnson
-Original Message-
From: rory oconnor [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 20, 2002 10:44 AM
To: [EMAIL PROTECTED]
Subject: fast,efficient query for counting
I'm setting up some tracking stats for (opt-in) e-mail campaigns, and
one of the things I'm tracking is click-thrus. I'm going to re-direct
traffic thru a script that will just count the number of clicks, and
store that number in a mysql table.
It needs to be fast and efficient, and I'm somewhat of a mysql newbie,
so I was wonderinf if there is any more efficient way to simply add a
number to the existing number in that table with mysql and perl. My way
seems like a lot of code to do a little task. Any help is appreciated!
# set up the db connection and SQL
$dbh =
DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1});
$select_number = "select total_clicked from $table";
# actually execute the checking query
$sth = $dbh->prepare("$select_number");
if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
# loop thru the query and set the result variables
while (my $ref = $sth->fetchrow_arrayref) {
$total_clicked = $$ref[0];
}
# add 1 to total_clicked
$total_clicked=$total_clicked+1;
# update the number
$update_number = "UPDATE $table set total_clicked = $total_clicked";
# actually execute the update query
$sth = $dbh->prepare("$update_number");
if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fast,efficient query for counting
From: "rory oconnor" <[EMAIL PROTECTED]> > I'm setting up some tracking stats for (opt-in) e-mail campaigns, and > one of the things I'm tracking is click-thrus. I'm going to re-direct > traffic thru a script that will just count the number of clicks, and > store that number in a mysql table. > > It needs to be fast and efficient, and I'm somewhat of a mysql newbie, > so I was wonderinf if there is any more efficient way to simply add a > number to the existing number in that table with mysql and perl. My way > seems like a lot of code to do a little task. Any help is appreciated! Instead of getting the current # out of the db, incrementing it, and then updating the field, try this: CREATE TABLE foo (clicks INT); INSERT INTO foo SET clicks=0; SELECT * FROM foo; UPDATE foo SET clicks=clicks+1; SELECT * FROM foo; UPDATE foo SET clicks=clicks+1; SELECT * FROM foo; UPDATE foo SET clicks=clicks+1; SELECT * FROM foo; DROP TABLE foo; You can increment 'columnname' with 'columnname+1'; that way, you don't need to get the current value first. -- denonymous www.coldcircuit.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fast,efficient query for counting
Hi,
You can just do an UPDATE:
UPDATE $table set total_clicked = total_clicked+1
Regards
rory oconnor wrote :
>
> I'm setting up some tracking stats for (opt-in) e-mail campaigns, and
> one of the things I'm tracking is click-thrus. I'm going to re-direct
> traffic thru a script that will just count the number of clicks, and
> store that number in a mysql table.
>
> It needs to be fast and efficient, and I'm somewhat of a mysql newbie,
> so I was wonderinf if there is any more efficient way to simply add a
> number to the existing number in that table with mysql and perl. My way
> seems like a lot of code to do a little task. Any help is appreciated!
>
> # set up the db connection and SQL
> $dbh =
>
>DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1});
> $select_number = "select total_clicked from $table";
>
> # actually execute the checking query
> $sth = $dbh->prepare("$select_number");
> if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
> if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
>
> # loop thru the query and set the result variables
> while (my $ref = $sth->fetchrow_arrayref) {
> $total_clicked = $$ref[0];
> }
>
> # add 1 to total_clicked
> $total_clicked=$total_clicked+1;
>
> # update the number
> $update_number = "UPDATE $table set total_clicked = $total_clicked";
>
> # actually execute the update query
> $sth = $dbh->prepare("$update_number");
> if (!$sth) { die "Error:" . $dbh->errstr . "\n"; }
> if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; }
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Joseph Bueno
NetClub/Trader.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
