PHP MySQL Question and server eval question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
2dcoder
Forum Newbie
Posts: 6
Joined: Tue Feb 15, 2005 1:09 pm

PHP MySQL Question and server eval question

Post by 2dcoder »

1) I want to increment the field "hit" in my "banner" table
everytime a banner gets displayed. What's the quickest way to do
this?

2) Here's my current dedicated server configuration:

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Celeron(R) CPU 1.70GHz
Processor #1 speed: 1699.846 MHz
Processor #1 cache size: 128 KB

Memory InformationMemory: 250260k/260352k available (1498k kernel
code, 7672k reserved, 1100k data, 156k init, 0k highmem)

System InformationLinux 2.4.20-30.9bigmem #1
SMP Wed Feb 4 20:27:00 EST 2004 i686 i686 i386 GNU/Linux

Physcial Diskshda: WDC WD400EB-00CPF0, ATA DISK drive
hda: attached ide-disk driver.
hda: host protected area => 1
hda: 78165360 sectors (40021 MB) w/2048KiB Cache, CHS=4865/255/63,
UDMA(100)

Current Memory Usage total used free
shared buffers cached
Mem: 252600 232980 19620 0 10728
124260
-/+ buffers/cache: 97992 154608
Swap: 1052248 71868 980380
Total: 1304848 304848 1000000

Current Disk UsageFilesystem Size Used Avail Use%
Mounted on
/dev/hda3 36G 3.4G 32G 10% /
/dev/hda1 99M 45M 50M 48% /boot
none 124M 0 124M 0% /dev/shm

I run about 16k page views a day, I also serve some downloads, for a
grand total of around 3 gig in bandwidth per day.

How many page views, gig per day do you think my server can handle?

Thank you for your comments.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

using a download type script, a simple update query is all that's needed.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

i'm sure it can handle a lot more.. ;)

i once installed a little tracker for a site.. (P4-3200 if i'm not mistaken and mysql on a diferent machine) and saw that after a few minutes there were like +6000k rows in that table...

i decided to switch to an update table set counter=counter+1 query.. to reduce the rows a little :)
2dcoder
Forum Newbie
Posts: 6
Joined: Tue Feb 15, 2005 1:09 pm

Post by 2dcoder »

Hmm.. I'm more of a newbie that I thought.. so I can do something such as:

Get the record with this:

# $id = banner id
$result = mysql_query("SELECT * FROM bannerads WHERE id='$id' LIMIT 1", $connection);

# display the banner or whatever and then
# add 1 to the hits field such as:

$result = mysql_query("UPDATE bannerads SET hits=hits+1 WHERE id='$id'", $connection);

Do I need to do the WHERE when I update the hits field or since I've already pulled the record I can just:

$result = mysql_query("UPDATE bannerads SET hits=hits+1'", $connection);

Thanks!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you must use the where clause, otherwise you will update all records.
2dcoder
Forum Newbie
Posts: 6
Joined: Tue Feb 15, 2005 1:09 pm

Post by 2dcoder »

What about in a loop where you are pulling records such as:

$result = mysql_query("SELECT * FROM banners", $connection);
while ($row = mysql_fetch_array($result))
{
extract($row);
$result2 = mysql_query("UPDATE banners SET hits=hits+1 WHERE id='$id'", $connection);
}

Is there a way to just update the row I've extracted or is the way as shown above the only way to do it? Thank you for your comments.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

in this loop, if you have n rows, you have n update queries....

because you are going to update all rows anyway, just write it as
$query = "select ..";
$result = mysql_query($query).....
while ($row = mysql_Fetch_assoc($result))
{
.... do stuff with row
}
$query = "update foo set counter=counter+1";
mysql_query($query).........
Post Reply