write into DB instead of txt file

[smallcompany]

Hi,

I have a script that gets the landing page URL and writes it into the text file. If the URL has already been written, it only increases the counter besides it.
Now I'm thinking about switching it to MySQL database, so I can look into it as needed, and probably build some simple web page that would show it in the table.

The current script:

$urlp=parse_url(strtok($_SERVER["HTTP_REFERER"],'?'), PHP_URL_PATH);

$file_name = "/path/logs/write.txt" ;

$content = @file_get_contents ( $file_name ) ;
$list_var = array ( ) ;

if ( $content !== false )
{
 $rows = explode ( "\n" , $content );
 foreach ( $rows as $row )
 {
 $fields = explode ( "," , $row ) ;
 $field_name = trim ( $fields [ 0 ] ) ;
 if ( $field_name != '' )
 {
 $counter = trim ( isset ( $fields [ 1 ] ) ? $fields [ 1 ] : 0 ) ;
 $list_var [ $field_name ] = $counter ;
 }
 }
}

if ( isset ( $list_var [ $urlp ] ) )
{
 $list_var [ $urlp ] ++ ;
}
else
{
 $list_var [ $urlp ] = 1;
}

$content = '' ;

foreach ( $list_var as $field_name => $counter )
{
 $content .= $field_name . ',' . $counter . "\n" ;
}

file_put_contents ( $file_name , $content , LOCK_EX ) ;

How to change this so it's written into the MySQL database (new page into the new table row, or just increase the counter if the page already exists)?

Thank you

[topr8]

personally i wouldn't do the increase counter in the db

i'd have a table with 3 fields: key,url,timestamp

and just add a row for every hit, that way you would easily be able to get a count of each url, but you'd also be able to get better stats like when was each hit, or hits in the last x period.

you could add extra fields for referer, useragent etc - although then you'd be heading for a full on stats/analysis package

... how many landing pages are there? are they already listed in the db anyway? are they static pages or generated by a database anyway?
as there are so many ways to do this and also variations of the table too, for instance if the landing pages are all known and numbered, it would be better to store the landing_page_number in the table than the url, however unless you have a very large number of hits it doesn't really matter i suppose.

[LifeinAsia]

Ditto what topr8 says about writing a new row for each one. If you have registered users, you can also log their userids for more granularity in your log analysis.

Make sure you index the table so that reading the data will be quick.

[smallcompany]

Thanks very much.

I already have stat tracking in place (two, GA and Piwik), but this was primarily to get the URL and pass it onto transaction tracking which is not done via stat platforms. Then I got an idea of counting the number of landings, regardless of the fact I already have sophisticated stat tracking in place. The DB idea was mostly so I can sort it by number of landings, and later create some simple front interface, just for the sake of learning how to do it.

Thank you

[topr8]

it's easy to get a count of the number of entries using SELECT COUNT(*)
if you are leqrning how to do it then learning how to use aggregate functions in SQL is a very useful skill.

to do what you actually asked in the way you want the easiest way is two queries, the first to check if the entry has already been made the second to either INSERT a new row or add one to the counter by running an UPDATE - UPDATE table set counterrow = counterrow +1