Page 1 of 2
Counting clickthru's on a news script
Posted: Fri Jul 18, 2003 2:25 pm
by robster
Hi all,
I've got a news script.
In the table for the news items I have a field called "link".
This field "link" contains the url that people can click when they want to read more.
Basically I am going to be serving quite a few news stories a day and want a way of tracking how many click thru's each story gets. I am not fussed about individual users, just the total quantity of clicks.
I want to use this information so that users can sort the news archives not just by id ASC and DESC but by popularity ASC or DESC(based on tracking the clickthru's).
I am presuming I will need to add a field to the table for news items for tracking the number of click thrus. I could call it "clicks" for instance.
I am at a loss as to how to do it easily.
I am also presuming it will be something to do with the way I process (or call to) the "link" field in the news items table?
Any help/idea/rants would be appreciated. I just don't know how to incriment the table based on the url. I know it's WHAT I've got to do, just not how to do it.
So who has the best method?
And lastly, thanks so much for your help here, I'm stretching the grey matter (hey, I'm an artist, not a REAL programmer (yet)

)
Rob
Posted: Fri Jul 18, 2003 2:31 pm
by Fredix
Any link shouldn't be direct but something like http://www.yoursite/link.php?id=001
now link.php would
1. open your news database and add 1 to the field "popularity" where id = 001
2. open the requested page (find out the real link in the database and redirect)
The script that shows the stories should order by popularity desc
I think that's all 
Posted: Fri Jul 18, 2003 2:48 pm
by robster
wow, inspiring and simple
so by what you're saying.. If I enter an entry in the news table it will get auto assigned an ID (from autoincriment).
I could in theory, use this ID as my url.
-In other words, news item displayed is ID "5".
-
http://www.yoursite/link.php?id=5 (or literally,
http://www.yoursite/link.php?id="get news ID from database#")
-link.php would take the id (in this case 5) and find news item 5 in the database, get the REAL url from the database and use it to open a new page.
-popularity would be incrimented..
etc etc.
Did that make sense, or is there a better/simpler way to do it?
Thanks so much Fredix
Posted: Fri Jul 18, 2003 2:58 pm
by Fredix
yes at least in my eyes that makes sense (as I suggested it

)
you would generate a table in popularity order and get links could indeed be genarated with the help of the id numbers
and yes as you can set the id to autoincrement any new news (<sounds strange??) would get a new ID and could be linked
though I can see a problem in what we are doing as any new entry would have no popularity and therefore appear at the end of the table and if the table gets longer you had scroll long until you find the real news.
maybe it would be better to order by date and put little red ! !! !!! (or something similar, maybe pictures) next to the entries name to show its importance/popularity
when scrolling through the news topics you would see what is popular
yes I think this is the final solution
Posted: Fri Jul 18, 2003 3:09 pm
by robster
i know this sounds silly, but could you (or anyone) please give me an idea of what the link.php might look like?
Also, what would the link look like?
I'm really at a loss, as I say, I understand the concept but need help to get started with the code. Just a rough guide would be great.
Thanks
Rob
Posted: Fri Jul 18, 2003 3:15 pm
by Fredix
oh man,
goto php.net and learn a little about $_GET and if you don't know how to communicate with a mysql db the read about the mysql functions.
the link would be generated by the show table script and look like
Code: Select all
<a href="http://yoursite/link?id=generated_id">News Topic</a>
?>
Posted: Fri Jul 18, 2003 3:30 pm
by robster
sorry, I don't mean to be a pain. I do understand how $_GET works (mostly anyway

) but what I don't get is how the url gets the id from the database.
In your example you have:
Code: Select all
<a href="http://yoursite/link?id=generated_id">News Topic</a>
That's understandable except I don't know where it gets generated_id from?
Is it from the database, where could it come from? I'm such an amateur.
Maybe my brains not screwed on correctly tonight

Posted: Fri Jul 18, 2003 4:04 pm
by robster
having had a think, I imagine the url would look like this:
<a href=\"link.php?id=$id\">read more...</a>
I will give it a go

Thanks Fredix
Posted: Fri Jul 18, 2003 4:10 pm
by robster
actually, after trying that, I get this fed to the browser as a URL:
http://localhost/vnews/link.php?id=
mmm... I'll keep going..

Posted: Fri Jul 18, 2003 4:19 pm
by robster
ok, I'm officially shooting off q's before I've researched it and i apologise. I can't believe it but I didn't assign $id in my loop for displaying the news, hence it didn't have a value.. I'll just stop typing now shall I. I hope somebody learns from this post anyway.

Rob
Posted: Fri Jul 18, 2003 6:12 pm
by robster
ok, here's how I did it, for anyone that comes across this in the future...
Code: Select all
<?
require("config.php");
?>
<?
//get link number from the "read more..." url in the news file (variable passing)
$linknum = $_GET['id'];
//open database and get record that matches the linknum
mysql_select_db($dbname);
$sql = "SELECT * FROM newscontent where id = $linknum";
$content = mysql_query($sql);
//get the url from database
$Xcontent = mysql_fetch_array($content);
$link = $Xcontent["link"];
$count = $Xcontent["popularity"];
//add 1 to the counter for this link
$r = mysql_query ("select popularity from newscontent WHERE id='$linknum'");
$hits = @mysql_result($r,0);
$q = "UPDATE newscontent SET popularity = $hits+1 WHERE id='$linknum'";
//update database
mysql_query($q);
//redirect browser to whatever $link is
print "$link<br>";
print "This link has been clicked $count times<br>";
//redirect not working yet, still working on it...
//header("location: $link");
mysql_free_result($content);
?>
If anyone knows what's going on with my header("location: $link"); code I'd appreciate a bit of help. Current error message is:
Warning: Cannot add header information - headers already sent by (output started at c:\phpdev\www\vnews\news_link.php:6) in c:\phpdev\www\vnews\news_link.php on line 40
Hope I helped someone out there anyway...
Rob
Posted: Fri Jul 18, 2003 6:36 pm
by Drachlen
header ALWAYS has to come before any text, spaces, or ANYTHING is written onto the page.
Posted: Sat Jul 19, 2003 3:15 am
by robster
interesting, thank you
I moved it as shown, but I STILL get the error... how strange. I can't find anything being printed etc before the header function.
Code: Select all
<?
require("config.php");
?>
<?
//get link number from the "read more..." url in the news file (variable passing)
$linknum = $_GET['id'];
//open database and get record that matches the linknum
mysql_select_db($dbname);
$sql = "SELECT * FROM newscontent where id = $linknum";
$content = mysql_query($sql);
//get the url from database
$Xcontent = mysql_fetch_array($content);
$link = $Xcontent["link"];
$count = $Xcontent["popularity"];
//add 1 to the counter for this link
$r = mysql_query ("select popularity from newscontent WHERE id='$linknum'");
$hits = @mysql_result($r,0);
$q = "UPDATE newscontent SET popularity = $hits+1 WHERE id='$linknum'";
//update database
mysql_query($q);
//redirect not working yet, still working on it...
header("location: $link");
//redirect browser to whatever $link is
// print "$link<br>";
// print "This link has been clicked $count times<br>";
mysql_free_result($content);
?>
Posted: Sat Jul 19, 2003 3:21 am
by robster
oh, I get you now, you don't mean sending info to the page, you mean typing in the php page. I Stuck it RIGHT at the top, and yes, I can send... say:
Code: Select all
<?
header("location: http://www.google.com");
?>
but seeing (as in my previous code snippets) I'm trying to get a link passed... I need to get the linkID, find the url from the database, then add a count to the counter in the database..
then re-direct with the url taken from the database...
How can I do that if I can only put the redirect (header) function first?
I'd really appreciate help on this one
Rob
Posted: Sat Jul 19, 2003 3:39 am
by robster
ok, I did it with:
Code: Select all
//redirect browser to whatever $link is
echo "<meta http-equiv="refresh" content="0;URL=$link">";
So, for anyone who has the same task, they are free to use this code if it suits them, the whole thing:
Code: Select all
<?
require("config.php");
?>
<?
//get link number from the "read more..." url in the news file (variable passing)
$linknum = $_GET['id'];
//open database and get record that matches the linknum
mysql_select_db($dbname);
$sql = "SELECT * FROM newscontent where id = $linknum";
$content = mysql_query($sql);
//get the url from database
$Xcontent = mysql_fetch_array($content);
$link = $Xcontent["link"];
$count = $Xcontent["popularity"];
//add 1 to the counter for this link
$r = mysql_query ("select popularity from newscontent WHERE id='$linknum'");
$hits = @mysql_result($r,0);
$q = "UPDATE newscontent SET popularity = $hits+1 WHERE id='$linknum'";
//update database
mysql_query($q);
//redirect browser to whatever $link is
echo "<meta http-equiv="refresh" content="0;URL=$link">";
mysql_free_result($content);
?>