Menu
Anyone know how to handle SQL error in this script?
[QUOTE]
[i]Originally posted by pyro
[B]Here’s the modified script (includes a little bug fix).
SQL to create table:
[code]CREATE TABLE `log` (
`id` int(11) NOT NULL auto_increment,
`time` int(11) NOT NULL default ‘0’,
`ip` varchar(20) NOT NULL default ”,
`user_agent` varchar(255) NOT NULL default ”,
`referrer` varchar(255) NOT NULL default ”,
PRIMARY KEY (`id`)
)
PHP to log info
[code=php]<?PHP
mysql_connect(‘localhost’,’username’,’password’);
mysql_select_db(‘iplogger’);
$time = time();
$ip = $_SERVER[‘REMOTE_ADDR’];
$user_agent = $_SERVER[“HTTP_USER_AGENT”];
$referrer = @$_SERVER[‘HTTP_REFERER’];
$sql = “INSERT INTO `log` (`id`, `time`, `ip`, `user_agent`, `referrer`)
VALUES (”, ‘$time’, ‘$ip’, ‘$user_agent’, ‘$referrer’)”;
mysql_query($sql);
?>
PHP to read info:
[code=php]<?PHP
mysql_connect(‘localhost’,’username’,’password’);
mysql_select_db(‘iplogger’);
# get first date
$sql = “SELECT * FROM `log` ORDER BY `time` ASC LIMIT 1”;
$results = mysql_query($sql);
$time = mysql_fetch_array($results);
$mintime = $time[‘time’];
$mintime = mktime(0,0,0,date(“m,d,year”,$mintime))-1;
# get latest date
$sql = “SELECT * FROM `log` ORDER BY `time` DESC LIMIT 1”;
$results = mysql_query($sql);
$time = mysql_fetch_array($results);
$maxtime = $time[‘time’];
$maxtime = mktime(0,0,0,date(“m,d,year”,$maxtime));
$mintime -= 60*60*24;
$year = array();
$month = array();
$day = array();
while($mintime<$maxtime){
$mintime += 60*60*24;
if(!in_array(date(“Y”,$mintime), $year)){
$year[] = date(“Y”,$mintime);
}
if(!in_array(date(“m”,$mintime), $month)){
$month[] = date(“m”,$mintime);
}
if(!in_array(date(“d”,$mintime), $day)){
$day[] = date(“d”,$mintime);
}
}
sort($year);
sort($month);
sort($day);
if (isset($_POST[‘submit’])) {
$find_day = $_POST[‘day’];
$find_month = $_POST[‘month’];
$find_year = $_POST[‘year’];
}
else {
$timestamp = time();
$find_month = date(“m”, $timestamp);//n
$find_day = date(“d”, $timestamp);//j
$find_year = date(“Y”, $timestamp);
}
$beg = mktime(0, 0, 0, $find_month, $find_day, $find_year);
$end = mktime(24, 0, 0, $find_month, $find_day, $find_year);
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN”
“http://www.w3.org/TR/html4/strict.dtd”>
<html>
<head>
<title>Logs</title>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″>
<style type=”text/css”>
table {
font-family: Geneva, Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 100%;
}
table td {
border: 1px solid #819ebb;
padding: 1px 3px 1px 3px;
}
</style>
</head>
<body>
<?PHP
echo “<form action=””.$_SERVER[‘PHP_SELF’].”” method=”post”>”;
echo ” <p><select name=”year”>”;
foreach($year as $cur_year){
if ($cur_year == $find_year) {
echo ” <option value=”$cur_year” selected=”selected”>$cur_year</option>”;
}
else {
echo ” <option value=”$cur_year”>$cur_year</option>”;
}
}
echo ” </select>”;
echo ” <select name=”month”>”;
foreach($month as $cur_month){
if ($cur_month == $find_month) {
echo ” <option value=”$cur_month” selected=”selected”>$cur_month</option>”;
}
else {
echo ” <option value=”$cur_month”>$cur_month</option>”;
}
}
echo ” </select>”;
echo ” <select name=”day”>”;
foreach($day as $cur_day){
if ($cur_day == $find_day) {
echo ” <option value=”$cur_day” selected=”selected”>$cur_day</option>”;
}
else {
echo ” <option value=”$cur_day”>$cur_day</option>”;
}
}
echo ” </select>”;
echo ” <input type=”submit” name=”submit” value=”Go!”></p>”;
echo “<form>”;
?>
<table>
<tr>
<td style=”border-bottom: 2px solid #819ebb;”>Date/Time</td>
<td style=”border-bottom: 2px solid #819ebb;”>Ip Address</td>
<td style=”border-bottom: 2px solid #819ebb;”>User Agent</td>
<td style=”border-bottom: 2px solid #819ebb;”>Referrer</td>
</tr>
<?PHP
$sql = “SELECT * FROM `log` WHERE `time` > $beg AND `time` < $end”;
$results = mysql_query($sql);
if (mysql_num_rows($results) > 0) {
while($data = mysql_fetch_array($results)) {
echo ” <tr>n”
.” <td>”.date(“l, d F Y h:i a”, $data[‘time’]).”</td>n”
.” <td>”.$data[‘ip’].”</td>n”
.” <td>”.$data[‘user_agent’].”</td>n”
.” <td>”.$data[‘referrer’].”</td>n”
.” </tr>n”;
}
}
else {
echo ” <tr>n”
.” <td colspan=”4″>No results found</td>n”
.” </tr>n”;
}
?>
</table>
</body>
</html>
[/B]