Page 1 of 1

[56k WARN] Advanced DB Page

Posted: Sun Feb 05, 2006 10:35 pm
by nickman013
Hello,

I have a DB now that tracks the screen names of people who visit my site via aim.

It works good.

However I would like to add onto it.

My current script is
track.php

Code: Select all

<?
$username= "******";  
$password= "****";  
$database= "********";  
$connection = mysql_connect('localhost',$username,$password);  
mysql_select_db($database); 
$get=array(); 
foreach(explode('|',$_SERVER['QUERY_STRING']) as $get) { 
     $get = explode('-',$get); 
     $my_get[$get[0]]=$get[1]; 
} 
$sn2 = str_replace("%20", "", $get[0]); 
$sn = str_replace("sn=", "", $sn2);
if ($_SERVER['REMOTE_ADDR']  != '68.195.158.8') 	{
if ($sn  != '') 						{
$date = date("m.d.y");
$time = date('g:i a',time()+3600);
$ip = @$REMOTE_ADDR; 
$sql = "INSERT INTO  `Tracker` (  `Number` ,  `Screen Name` ,  `Page` ,  `Date` ,  `Time` ,  `IP` ) 
VALUES (
'',  '$sn',  '$page',  '$date',  '$time',  '$ip'
);
";
$result = mysql_query($sql) or die(mysql_error()); 


													}
										}

?>
And I Display it very basic on a page with this code

Code: Select all

<?php
$sql = "SELECT * 
FROM  `Tracker` 
ORDER BY  `Number` DESC ";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
echo "<tr>";
echo "<td align=center>".$row['Number']."</td>";
echo "<td align=center>".$row['Screen Name']."</td>";
echo "<td align=center>".$row['Page']."</td>";
echo "<td align=center>".$row['Date']."</td>";
echo "<td align=center>".$row['Time']."</td>";
echo "<td align=center>".$row['IP']."</td>";
echo "</tr>";
}
?>
It displays the data in a table for those who dont know.

I would like to make my stats look like this
Image

without the details link, but i do want the delete link. and when the delete link is clicked it will delete the screen name out of the DB.

when a screen name is clicked, i would like a new window to pop up, and display all the pages the screen name has been to in the same format, with the delete link.

This is very hard to do I am guessing.

I just need advice on how to do this, and then I will start it myself.

Thank You

Burrito: removed username and password.

Posted: Mon Feb 06, 2006 12:41 am
by josh
First of all, for the last visit instead of using date() to insert into the DB, use time(). This will give you a timestamp that can be run through date() later. This way you can subtract the timestamp in the database off of the current time to get an offset from the current time (ex. 4 minutes ago).


What exactly do you need help with in creating output like the screenshot you posted?


Also:

Code: Select all

echo "<td align=center>".$row['Number']."</td>";
should be

Code: Select all

echo "<td align=\"center\">".htmlentities($row['Number'])."</td>";
for each one of your outputs, you can't trust user-input.. anyone could just go and inject arbitrary javascript into your pages

Posted: Mon Feb 06, 2006 3:59 am
by jayshields
Buggery. Now I know why it's such a hassle dealing with dates when using PHP/MySQL.

I always insert NOW() into MySQL, and then have to fetch it with UNIX_TIMESTAMP(date) AS date, and then I have to mess with date() in PHP.

I didn't know time() made it 100x easier :'(

Posted: Mon Feb 06, 2006 6:06 am
by nickman013
The things I need help with is, the delete button, and the time counting back thing, will time stamp do that? I also need help with if the screen name is clicked it will sort out the other screen names and show everypage that screen name visited.

I have an idea of how it works, but I cant type right now, I got school.

Thanks