A little syntax help if you will?

[outseeker]

I've got a snippet of phpnuke code I've tried to mod up here. It's the bit that writes the http referer to the database. phpnuke has a max of 100 or so referers by default, and does not keep count of how many times the same url has hit you. It just duplicates the database entry, and scrolls the oldest one off when it reaches max.

This is really quite stupid, as it can be fixed with a tiny mod to the code... I just dont quite (as always) know the syntax fully. Just so you know too, the only line of code that was originally in there is the last one. Everything at the start could be wrong, it's my attempt at it.


$result = $db->sql_query("SELECT times FROM ".$prefix."_referer WHERE url='".$referer."'");
if ($row = $db->sql_fetchrow($result)) {
$sql = "UPDATE ".$prefix."_referer SET rid=null, url='".$referer."', times='".$times."') WHERE url='$referer'";
$db->sql_query($sql);
} else {
$result = $db->sql_query("INSERT INTO ".$prefix."_referer VALUES (NULL, '".$referer."', '1')");
}


The mysql table I'm trying to update has 3 fields: "rid", "url", and I have added "times" (which is default to 1, as any new entry IS a visit) so I've tried to reflect that in the UPDATE statement. $referer is set and working in the environment to be the actual referer address.

What I am trying to do is check to see if the $referer exists in the mysql _referer table, if it does then update the times field + 1. If not, I know it will add to the referer table as-is, since that is all it does for the moment! :D

Thanks heaps

[mrnightowl]

I'm thinking something like this:

$result = $db->sql_query("SELECT times FROM ".$prefix."_referer WHERE url='".$referer."'");
$row = $db->sql_fetchrow($result));
if($row > 0){
$times = $times + 1;
$sql = "UPDATE ".$prefix."_referer SET rid=null, url='".$referer."', times='"$times."') WHERE url='$referer'";
$db->sql_query($sql);
} else {
$result = $db->sql_query("INSERT INTO ".$prefix."_referer VALUES (NULL, '".$referer."', '1')");
}

Something along that lines should work for adding + 1 to the amount of times...

[Andrew Johnson]

Firstly, I don't see where you define $times or $referer, are you sure they are the correct values?

Secondly if you just want to add 1 to the db value then instead of


$sql = "UPDATE ".$prefix."_referer SET rid=null, url='".$referer."', times='"$times."') WHERE url='$referer'";


you could use


$sql = "UPDATE " . $prefix . "_referer SET rid=null, url='" . $referer . "', times=times+1) WHERE url='" . $referer . "'";


Thirdly, what is the "rid" column for? Is there a reason you want it to be NULL ?

[outseeker]

I'm thinking something like this:

$result = $db->sql_query("SELECT times FROM ".$prefix."_referer WHERE url='".$referer."'");
$row = $db->sql_fetchrow($result));
if($row > 0){
$times = $times + 1;
$sql = "UPDATE ".$prefix."_referer SET rid=null, url='".$referer."', times='"$times."') WHERE url='$referer'";
$db->sql_query($sql);
} else {
$result = $db->sql_query("INSERT INTO ".$prefix."_referer VALUES (NULL, '".$referer."', '1')");
}

Something along that lines should work for adding + 1 to the amount of times...

Thanks man, but that code doesn't run! :/

[Andrew Johnson]

Oh sorry, then I suppose I can't really help you.

[outseeker]

Firstly, I don't see where you define $times or $referer, are you sure they are the correct values?

Secondly if you just want to add 1 to the db value then instead of


$sql = "UPDATE ".$prefix."_referer SET rid=null, url='".$referer."', times='"$times."') WHERE url='$referer'";


you could use


$sql = "UPDATE " . $prefix . "_referer SET rid=null, url='" . $referer . "', times=times+1) WHERE url='" . $referer . "'";


Thirdly, what is the "rid" column for? Is there a reason you want it to be NULL ?


Thanks for the help, and the questions Andrew. rid, times and url are the names of the database columns we are updating to. The only line phpnuke has is $result = $db->sql_query("INSERT INTO ".$prefix."_referer VALUES (NULL, '".$referer."', '1')");

That line of (semi)default code will add the entry to the database no problemo. It does that every time, and I am unsure how to make it check the name, then update visits if it's found. You dig? My code is quite possibly total garbage, as I've been working off this one line of working code and I know quite little about specifics! :)

I am hoping that knowing what I wanna do and knowing this line of code, and that the column this line of code is giving NULL value to is called rid may be enough to go on. Also $referer is defined by php earlier in the code from server vars.

Thanks for asking questions, if u don't see what I'm on about please ask more clarification. This would be a sweet mod!

[outseeker]

Oh sorry, then I suppose I can't really help you.

hehe don't worry buddy! :D

[mrnightowl]

Updating it to add the amount of times should be easy... but also having the orginal untampered code would be helpful... Also another way to do it would just have it count the amount of rows that exist with the same url and remove that 100 limit or extend it to say 1,000 or more because even at 10,000 or such it would be a quick query if indexed properly.

[outseeker]

lol in my previous post, that one line of code doing a write is the original code. Untampered, u can remove the third column update named times. That would be literaly 100% of the referer code!

[outseeker]

Updating it to add the amount of times should be easy... but also having the orginal untampered code would be helpful... Also another way to do it would just have it count the amount of rows that exist with the same url and remove that 100 limit or extend it to say 1,000 or more because even at 10,000 or such it would be a quick query if indexed properly.

If I can get it to count, the database will only ever be as big as how many different links come into me. Not too big guaranteed ;) Thanks for another idea tho pal!

[Andrew Johnson]

Well if this is the current line:


$result = $db->sql_query("INSERT INTO ".$prefix."_referer VALUES (NULL, '".$referer."', '1')");


Then I would think you could just do the following:


$result = $db->sql_query("UPDATE " . $prefix . "_referer SET times=times+1 WHERE url='" . $referer . "'");


This is of course assuming that the names of the columns you have provided are correct.

[outseeker]

I will break it down into "code without code" if you will ;)

$referer = http://website.com/linkagesitein.htm

if (any mysql entry in the database column "url" = $referer) {
update same row, "times" = times+1
}
that's all i need!! :D

[outseeker]

oh yeah and rid is set to auto increment in the mysql database keeping track of global how many link clicks in. that's what that was doing in all this

The code you posted doesn't seem to update the table. I removed all my crap and just added your code on its own to test it. :(

[Andrew Johnson]

If you just have my code and theres no IF's, CASE's, LOOPs, etc that are making it unreachable then it is a problem with either $prefix or $referer. Because if that INSERT statement works then my UPDATE has to work too...

[outseeker]

AHAHHA.. Thanks so much for your help! I noticed the stored referer address was a liitle bit short! So it never matched the $referer!!!!!!!!!!!!!! AAAARGHHH!! Stupid jerks limited the column url to only 100 characters, and a google referal is over 100 chars XD

I will now go back and try all the code and add thanks and praise :thumbsup:

[Andrew Johnson]

Hahaha, as long as it all worked out man.

Happy coding!

[outseeker]

you could use


$sql = "UPDATE " . $prefix . "_referer SET rid=null, url='" . $referer . "', times=times+1) WHERE url='" . $referer . "'";


This code has a random close bracket in it XD

I used this code in the end to get it to update after removing the rid column:

$result = $db->sql_query("UPDATE ". $prefix . "_referer SET times = times+1, url='$referer' WHERE url='$referer'");

I still need to check to see if it needs updating, or if I need to add a new row as it will only update if there is a match on referer. Any help for an if statement, oh master? :)

[Andrew Johnson]

errr, maybe:


if (mysql_num_rows(mysql_query("SELECT * FROM " . $prefix . "_referer WHERE url='" . $referer . "'")) > 0)
{
//update
}
else
{
//insert
}

[outseeker]

OHOOO! You sir have got yourself some reputation click action!! That code works spot on. I hit the page from google, the database counts 1 up on its link in the referer list. SIIIIIIIIIIIIIIIIIIICCCCKKKKKKKKK!! Thanks champ! Could not likely have done it without you! :cool::thumbsup::D

[outseeker]

Hey, I hope you notice this thread again mate! The code I'm now using (courtesy of yourself) works almost the way I want it to when there is less than 3 entries in the table or less!

eg. I can dummy a request to give a referer, which is logged with a 1 in the times column. No problem. I can do that as many times as I like and it will update the times. With 2 entries, it was updating BOTH count columns +1 each time. When I triggered a 3rd referer, it added like it was supposed to and +1 the other two as well! :D

If you can think of anything incorrect in this code, or a different way to do it please let me know champ. Oh I am naturally coding this in PHP, since its a mod for phpnuke so apologies for the \" markup ;) you can see my commented out test points too // updated etc. so I know what it SHOULD have done to the DB lol


$referer = $_SERVER['HTTP_REFERER'];
// referer mod v1.o begins
if (mysql_num_rows(mysql_query("SELECT * FROM nuke_referer WHERE url='".$referer."'")) != 0) {
mysql_query("UPDATE nuke_referer SET times=times+1, url=\"$referer\" WHERE url=\"$referer\"");
//echo "updated";
}
else
{
mysql_query("INSERT INTO nuke_referer (times, url) VALUES (times+1, \"$referer\")");
//echo "added";
}
// referer mod v1.o finish
}

Am I missing a WHERE from the INSERT query?? Like WHERE times<1 or something?

Cheers bruv!

[Andrew Johnson]

$referer = $_SERVER['HTTP_REFERER'];

if (mysql_num_rows(mysql_query("SELECT * FROM nuke_referer WHERE url='" . $referer . "'")) != 0)
{
@mysql_query("UPDATE nuke_referer SET times=times+1 WHERE url='" . $referer . "'");
}
else
{
@mysql_query("INSERT INTO nuke_referer (times, url) VALUES ('1', '" . $referer . "')");
}

[outseeker]

Thanks heaps Andrew, I try to code a lot of different stuff but ain't very good at any of it. I was escaping the " marks for php etc. where it was unneccessary. (wat a Noob) lol it works great now, thank you once again for putting up with my uselessness :D