Topic awaiting preservation: PHP/MySQL Code Check...

This code is suposed to grab the host and then store it in the database if there is no listing of it yet. And increment hits if it is listed. This is used when I am checking which parked domain names are delivering the most hits to the site.

I can't seem to get it to work right, as it keeps using the insert function instead of the increment.

I wouldn't mind some input on this.

<? 

	

	$database = "****";

	$table = "hitTracker";



	$referer = "http://$HTTP_HOST";

	$referer = str_replace("http://www.","",$referer);

	$referer = str_replace("http://","",$referer);



	$connection = @mysql_connect("localhost","****","****") 

		or die("Could not Connect");

	

	$db = @mysql_select_db($database,$connection)

		or die("Could not select db");



	$sql = "SELECT * FROM $table WHERE domain = $referer";



	$result = mysql_query($sql);

	$sql = "";



	while($row = @mysql_fetch_array($result))

	{

		$id = $row['id'];

		$sql = "UPDATE $table SET hits = hits + 1 WHERE id = $id";  

		$result = mysql_query($sql) or die("Invalid Query");

	}



	if ($sql == "")

	{

		$sql = "INSERT INTO $table (id, domain, hits) VALUES ('','$referer','0')";

		$result = mysql_query($sql) or die("Invalid Query");

	}

	

?>

The following code should work (I haven't tested it, because I don't have time):

$sql = "INSERT INTO $table (id, domain, hits) VALUES ('','$referer',1)";
$result = mysql_query($sql);

if (!$result) {
$sql = "UPDATE $table SET hits = hits + 1 WHERE domain = '$referer'";
$result = mysql_query($sql);
}

BTW Replace all your SQL queries with this code (leaving the database connect part).

The code you sugest should make my result resemble:

$database = "****";	

$table = "hitTracker";

	

$referer = "http://$HTTP_HOST";	

$referer = str_replace("http://www.","",$referer);	

$referer = str_replace("http://","",$referer);	

$connection = @mysql_connect("localhost","****","****")

  or die("Could not Connect");		

$db = @mysql_select_db($database,$connection)		

 or die("Could not select db");



$sql = "INSERT INTO $table (id, domain, hits) VALUES ('','$referer',1)";

$result = mysql_query($sql);



if (!$result) {

$sql = "UPDATE $table SET hits = hits + 1 WHERE domain = '$referer'"; 

$result = mysql_query($sql);

}

Wouldn't this first create a new field for the domain entered.
And then update all the listings.

In this way I would have multiple listings of the same name, all with incremented hits?

I will work on this code to see if I can get it to work.

<edit> Correction, this code would simply create a new entry of the same name with a +1 to it. </edit>



[This message has been edited by WarMage (edited 10-08-2001).]

Oops...

Made a slight mistake.

The orrigional code would work.

However remember to select the date you don't quote the result.

$id = $row[id];

instead of

$id = $row['id'];

My bad...

The code currently resembles

<? 

	

	$database = "****";

	$table = "hitTracker";



	$referer = "http://$HTTP_HOST";

	$referer = str_replace("http://www.","",$referer);

	$referer = str_replace("http://","",$referer);



	$connection = @mysql_connect("localhost","****","****") 

		or die("Could not Connect");

	

	$db = @mysql_select_db($database,$connection)

		or die("Could not select db");



	$sql = "SELECT * FROM $table WHERE domain = '$referer'";



	$result = mysql_query($sql);



	$row = @mysql_fetch_array($result);

	$id = $row[id];

	

	if ($id != "")

	{

		$sql = "UPDATE $table SET hits = hits + 1 WHERE id = $id";  

		$result = mysql_query($sql) or die("Invalid Query(1)");

	}

	else

	{

		$sql = "INSERT INTO $table (id, domain, hits) VALUES ('','$referer','1')";

		$result = mysql_query($sql) or die("Invalid Query(2)");

	}

	

?>

Maybe someone will find it useful.

Ahhhhhhhhhhhhhh, I didn't see 'id' field (and therefore my code won't work correctly). Anyway, IMHO You don't need 'id' filed at all... This is how I would do this (I've tested this and it works fine):

#
# Table structure for table 'hittracker'
#
CREATE TABLE `hittracker` (
&nbsp;&nbsp;`domain` varchar(255) NOT NULL default '',
&nbsp;&nbsp;`hits` int(11) NOT NULL default '0',
&nbsp;&nbsp;PRIMARY KEY (`domain`)
) TYPE=MyISAM;

<?php

&nbsp;&nbsp;&nbsp;&nbsp;$database = "****";
&nbsp;&nbsp;&nbsp;&nbsp;$table = "hitTracker";

&nbsp;&nbsp;&nbsp;&nbsp;$referer = "http://$HTTP_HOST";
&nbsp;&nbsp;&nbsp;&nbsp;$referer = str_replace("http://www.", "", $referer);
&nbsp;&nbsp;&nbsp;&nbsp;$referer = str_replace("http://", "", $referer);

&nbsp;&nbsp;&nbsp;&nbsp;$connection = @mysql_connect("localhost", "****", "****")
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;or die("Could not Connect");
&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;$db = @mysql_select_db($database, $connection)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;or die("Could not select db");

&nbsp;&nbsp;&nbsp;&nbsp;$sql = "INSERT INTO $table (domain, hits) VALUES ('$referer', 1)";
&nbsp;&nbsp;&nbsp;&nbsp;$result = mysql_query($sql);

&nbsp;&nbsp;&nbsp;&nbsp;if (!$result) {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$sql = "UPDATE $table SET hits = hits + 1 WHERE domain = '$referer'";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$result = mysql_query($sql);
&nbsp;&nbsp;&nbsp;&nbsp;}

?>

Enjoy!

Here is a problem.

It is not working correctly.

So here is what is suposed to happen.

suppose we have the url www.x.com
and www.y.com

So when someone types www.x.com into their address bar they would be taken to our site and in the table domain x.com would either get added or incremented.

Next when another user enters www.y.com into their browser and y.com is either added or incremented.

This is not working right now.

So the question is:
"How do I determine which parked domain the index is being called from?"

How would I do this?



[This message has been edited by WarMage (edited 10-08-2001).]

The last version of my script from above works fine. I have tested it locally and it worked without problems. I even added two bogus domains like you said - with www.x.com and www.y.com that point to the same server. After that I've called the script from both domains (i.e. www.x.com/log.php & www.y.com/log.php ) and domains were added to the database. When I tried to access them again counter was incremented correctly (duplicate entry wasn't created). So, I really don't know what's wrong in your case. Did you include the script in all your pages? Also, did you modify SQL table according to SQL scheme that I posted above?




[This message has been edited by mr.maX (edited 10-08-2001).]

I am running it simply on the index.php page.

When I try the ip address of the site it does record the site as coming from the ip address.

When i try the main domain name the script records the domains name and increments it correctly.

However, when I try a different parked name it increments the main domain name.

I am at a complete lose as to why it does this. It makes no sence to me whatsoever.

The HTTP_HOST call must being doing something different that what I would expect it to do.

Another thing that might be related.

When I ping a parked name it returns a different IP address, than the main sites IP. But when I attempt to access the IP that is returned it does not go to our site, instead a different site. That was of interest to me. But I don't know what it all means.

I am at a real lose as to how I should resolve this.

Any ideas at all.

Your script is a thing of beauty by the way. Question about the code for personal learning. Isn't MyISAM the default table type for MySQL? and what is the significance of it?

If it increments main domain counter when you access parked domain, then something is probably wrong with HTTP_HOST variable (it is probably set to the same value). You can easily check this by doing an "echo $HTTP_HOST" from both domains...

I like to specify "MyISAM" so that I can always see table type (before creating it). This can be useful, especially since now we have other table types, like "InnoDB" with support for transactions, etc...