MySQL Problem...

[Anon]

Hi again, Im having a problem doing something here is basically what im trying to do.

When a user comes to the main page (index.php) I run a check to see if they have a cookie (it is to only up the hit counter if u havent visited in 3hrs) and Now I tried to add where it will also store the Browser you use / OS you use when it does this.

Im trying to make A statistics program kind of like Analog (if you have ever heard of it...)

Basically I have a table called "stats" so far it just has this..

hits = 163723

Now if the browser does not exist I want to INSERT it with the value 1, otherwise increase it so it would be like..

hits = 163723
IE 5.5 = 1 or.. IE 5.5 = 32 (whatever)

Same with OS, so the table would then be..

hits = 163723
IE 5.5 = 1 or (whatever)
Windows98 = 1 or (whatever

And if a new browser is found it makes that..

ie..

hits = 163723
IE 5.5 = 3
Windows98 = 12
IE 5.0 = 2
IE 5.1 = 45
Windows95 = 3
Windows NT = 32

etc...

Here is my code:

require("header.php");

if (!$TheHardyZcomCounter) {
	setcookie("TheHardyZcomCounter","visited",time()+10800);

	$thc->update_site_hits();
	$temp = explode(";",$HTTP_USER_AGENT);
	$browser = "$temp[1]";
	$os = substr("$temp[2]",0,strlen($temp[2])-1);
	$thc->stats_update($browser,$os);

	if ($TheHardyZcom) {
		$thc->update_user_hits($TheHardyZcom);
	}
 }

This is my function:

function stats_update ($browser,$os) {
	mysql_connect($this->server, $this->db_user, $this->db_pass);
	mysql_select_db($this->database);

	$query = mysql_query("SELECT '$browser' from stats");

	list ($browser) = mysql_fetch_row($query);

	if (!$browser) {
		$query = mysq_query("INSERT into stats ('$browser') values ('1')");
	}
	elseif ($browser) {
		$browser_uses = "$browser"+"1";
		$query = mysql_query("UPDATE stats SET '$browser'='$browser_uses'");
	}

	$query = mysql_query("SELECT '$os' from stats");

	list ($os) = mysql_fetch_row($query);

	if (!$os) {
		$query = mysq_query("INSERT into stats ('$os') values ('1')");
	}
	elseif ($os) {
		$os_uses = "$os"+"1";
		$query = mysql_query("UPDATE stats SET '$os'='$os_uses'");
	}

	mysql_close();
}

I am a newbie to PHP & MySQL (only been doing it about 3 weeks or less) so Im sure its something stupid or simple... Ive been stumped a good while as usual so I Came here for help.

Anyone who can point me in the right direct Id appreciate it.

Here is some addition information

The cookie is fine, it incs the site hits and 'visits' for that user (used for X visits since <registration date>)

Also, The $browser / $os variables are correct, I read about the functions earlier on php.net and ran it through a test.php to check if they set correctly, and they do.

Once again anyone who could help me out it would be much appreciated.

Thank you for your time again.

[Anon]

well, I'm also a newbie to PHP and MySQL, but this:

$browser_uses = "$browser"+"1";

Can't be right!

this is how I'd do it:
mysql_pconnect($db_server, $db_login, $db_pass);

$sql="select '$browser' from stats";
$data=mysql_query("$sql");
$check=mysql_num_rows($data);
if($check == 0){//NOT YET SEEN!
$sql="insert into stats('$browser') values('1')";//insert it!
$data=mysql_query("$sql");} else {//FOUND!
while($line = mysql_fetch_array($data)) {
while(list($col_name, $col_value) = each($line)){
if (is_string($col_name)) {
$number=$col_value; };
};
};
$sql="insert into stats('$browser') values('$number');//update it!
$data=mysql_query("$sql"); }

//$browser done

$sql="select '$os' from stats";
$data=mysql_query("$sql");
$check=mysql_num_rows($data);
if($check == 0){//NOT YET SEEN!
$sql="insert into stats('$os') values('1')";//insert it!
$data=mysql_query("$sql");} else {//FOUND!
while($line = mysql_fetch_array($data)) {
while(list($col_name, $col_value) = each($line)){
if (is_string($col_name)) {
$number=$col_value; };
};
};
$sql="insert into stats('$os') values('$number');//update it!
$data=mysql_query("$sql"); }

//$os done

Hope it works,

Sander.

[Anon]

oops, it didn't really come out the way I wanted it, here's a copy:

mysql_pconnect($db_server, $db_login, $db_pass);

$sql="select '$browser' from stats";
$data=mysql_query("$sql");
$check=mysql_num_rows($data);
if($check == 0){ //NOT YET SEEN!
$sql="insert into stats('$browser') values('1')"; //insert it!
$data=mysql_query("$sql");
} else { //FOUND!
while($line = mysql_fetch_array($data)) {
while(list($col_name, $col_value) = each($line)){
if (is_string($col_name)) {
$number=$col_value;
};
};
};
$sql="insert into stats('$browser') values('$number'); //update it!
$data=mysql_query("$sql");
}

//$browser done

$sql="select '$os' from stats";
$data=mysql_query("$sql");
$check=mysql_num_rows($data);
if($check == 0){ //NOT YET SEEN!
$sql="insert into stats('$os') values('1')";//insert it!
$data=mysql_query("$sql");
} else { //FOUND!
while($line = mysql_fetch_array($data)) {
while(list($col_name, $col_value) = each($line)){
if (is_string($col_name)) {
$number=$col_value;
};
};
};
$sql="insert into stats('$os') values('$number');//update it!
$data=mysql_query("$sql");
}

//$os done

Hope this works 🙂

[Anon]

just found I had an error myself, you should first SELECT THE DATABASE!

I normally use:
$sql = "use $db_name";
$data = mysql_query("$sql");

I know there are probably better ways, but like this I can remember how to do it in a mysql command prompt as well 🙂

[mickro]

GrimZ,

You need to declare the variables inside your function as globals.

function stats_update ($browser,$os) {

// ADD THIS LINE
global $this,$query,$os_uses,$browser_uses;

mysql_connect($this->server, $this->db_user, $this->db_pass);
mysql_select_db($this->database);

$query = mysql_query("SELECT '$browser' from stats");

list ($browser) = mysql_fetch_row($query);

if (!$browser) {
$query = mysq_query("INSERT into stats ('$browser') values ('1')");
}
elseif ($browser) {
$browser_uses = "$browser"+"1";
$query = mysql_query("UPDATE stats SET '$browser'='$browser_uses'");
}

$query = mysql_query("SELECT '$os' from stats");

list ($os) = mysql_fetch_row($query);

if (!$os) {
$query = mysq_query("INSERT into stats ('$os') values ('1')");
}
elseif ($os) {
$os_uses = "$os"+"1";
$query = mysql_query("UPDATE stats SET '$os'='$os_uses'");
}

mysql_close();
}

I think this should sort it out (I haven't checked the code too closely)

Hope this helps

Mike
www.digitalegg.net