MySQL Counters

[Anon]

hi there

I have been trying to make a MySQL counter systme but everytime I try it comes up eith errors.

My pages on my site are called by:
navigator.php3?pageid=[pagename here]

As a result I wanted a script that would update a table with 1 row that has the names of all my pages in it.

Could someone tell me why it is not working?

Thanks
Ben Periton

This is the code I came up with:

<?
$hostname = "#####";
$username = "#####";
$password = "#####";
$dbname = "#####";

function mydbconnect ($hostname,$username,$password,$dbname)
{
$link = @mysql_pconnect("$hostname","$username","$password");
if ($link && mysql_select_db ("$dbname"))
return ($link);
return (FALSE);
}

mydbconnect($hostname,$username,$password,$dbname);

$querya = "SELECT $pageid FROM pagecounter";
$exec = MYSQL_QUERY($querya);
$oldvalue = $exec;

$newvalue = $oldvalue+1;

$queryb = "UPDATE pagecounter SET $pageid = '$newvalue'";
$exec = MYSQL_QUERY($queryb);

MYSQL_CLOSE();
?>

[Anon]

How is your table set up? From reading this it looks like you only have 1 record in your table with a bunch of columns that can match whatever is in the variable $page_id.

Here is what you are doing in english:

Select the column named $pageid(what ever is in this variable at this time) from the table pagecounter. This will give you the field $pageid from every row in your table pagecounter.

Then you have a problem because you are setting the variable called $oldvalue to the variable $exec, the function mysql_query() doens't return any results just a link to those results. To get you're value into $oldvalue you will need to do a mysql_fetch_field(), or mysql_fetch_array() look them up to find out what is best for your situation.

Then you run a query that says update the table pagecounter and set the column named $pageid (the value in this variable) to $newvalue (which you have set as $oldvalue+1, and as I said $oldvalue isn't what you think it is). This query will update every row in your table pagecounter and set the column $pageid to equal the value of $newvalue which is probably 2 at this point.

If you need more help put up a copy of your table schema so we can find out what is wrong.

[Anon]

Yea i only have 1 record but multiple colums.
Is it possible to do what i want to do?

Thanks

Here is a dump of my table:

CREATE TABLE pagecounter (
main varchar(20) DEFAULT '0' NOT NULL,
help varchar(20) DEFAULT '0' NOT NULL,
contact varchar(20) DEFAULT '0' NOT NULL,
otracking varchar(20) DEFAULT '0' NOT NULL,
ordering varchar(20) DEFAULT '0' NOT NULL,
examples varchar(20) DEFAULT '0' NOT NULL,
aboutus varchar(20) DEFAULT '0' NOT NULL,
feedback varchar(20) DEFAULT '0' NOT NULL,
advertising varchar(20) DEFAULT '0' NOT NULL,
meritech varchar(20) DEFAULT '0' NOT NULL,
disclaimer varchar(20) DEFAULT '0' NOT NULL,
privacy varchar(20) DEFAULT '0' NOT NULL,
more varchar(20) DEFAULT '0' NOT NULL,
more2 varchar(20) DEFAULT '0' NOT NULL,
more3 varchar(20) DEFAULT '0' NOT NULL,
rural varchar(20) DEFAULT '0' NOT NULL,
urban varchar(20) DEFAULT '0' NOT NULL
);

[Anon]

Sure change this code:

$querya = "SELECT $pageid FROM pagecounter";
$exec = MYSQL_QUERY($querya);
$oldvalue = $exec;

$newvalue = $oldvalue+1;

To:

$querya = "SELECT $pageid FROM pagecounter";
<b>$result</b> = MYSQL_QUERY($querya);
<b>$oldvalue = mysql_fetch_row($result);

$newvalue = $oldvalue[0]+1;</b>

That will get your row that is fetched, and put the data into the array $oldvalue, then you take the value at position 0 (the first value) and add 1 to it and put it in your variable $newvalue.

But instead you should change the way your table is laid out so that you have a cleaner set of code, something like this.

create table pagecounter(
page_id int not null auto_increment primary key,
name varchar(255),
page_count int);

Then fill this table with all of the names of your pages (this way if you add a new page you don't have to change the table, just add a new record).

Now you can change your queries to this:

select page_id, page_count from pagecounter where name = '$pageid';

and

update pagecounter set page_count = $newvalue where page_id = $page_id;

Using the field page_id as an auto incrementing primary key will give you an indexed unique ID for each record in your table executing your queries faster when you use the page_id. If you used page_id instead of name in your select query you'd get even better performance.

This type of setup is why you have a database, the setup you were using doesn't get you any benefits, other that using SQL.