Soldato
- Joined
- 27 Dec 2005
- Posts
- 17,316
- Location
- Bristol
I'll try and keep this short. I have a visitor log database of approx. 15,000 records. I've recently changed how it logs visitors from session ID to IP, as session ID was kicking up to many problems (visitors not being tracked correctly resulting in 10 different records for the same person when they visit 10 pages etc).
As a result I want to combine all the database's records based on IP, signifying the difference in days (if over 24h) of each record.
I think I've got something that *should* work (I have backed up the database
) but I get an 500 Internal Server error when doing it through my browser, and PHPMyAdmin kicks up an error on the first line when doing a SQL Query directly through there (different syntax? no idea).
The code is as follows:
I know it's a hella-lot of database querying/updating which I assume is why I'm getting a 500 through my browser. I assume it'd help to take the DELETE outside of the second while(), but I'm not experienced with arrays so not sure how to do this for all the found/matching IP records.
Any advice as to how to do this in a non-totally bodged way would be great.
Edit: Now get...
As a result I want to combine all the database's records based on IP, signifying the difference in days (if over 24h) of each record.
I think I've got something that *should* work (I have backed up the database
) but I get an 500 Internal Server error when doing it through my browser, and PHPMyAdmin kicks up an error on the first line when doing a SQL Query directly through there (different syntax? no idea).The code is as follows:
PHP:
$sql = mysql_query("SELECT * FROM sessionlog ORDER BY date"); // get all records
while($row_change = mysql_fetch_array($sql)){ // for each record
$firstid = $row_change['id']; // get fields
$ip = $row_change['ip'];
$history = $row_change['history']; // this is the browsing history
$lastdate = $row_change['date'];
$sql2 = mysql_query("SELECT * FROM sessionlog WHERE ip='$ip'"); // find all records with the same IP
while($row_change2 = mysql_fetch_array($sql2)){ // for each matching record
$curhist = $row_change2['history']; // get fields
$curid = $row_change2['id'];
$date = $row_change2['date'];
$datediff = $date - $lastdate; // subtract dates (all unix)
if($datediff > 86400){ // if result is 24 hours
$dategap = date('d-m-Y', $row_change2['date']); // convert the date to a legible format
$curhist = "$dategap -> $curhist"; // append the current history with the date of the visit
}
$history = "$history -> $curhist"; // add history of current IP record onto the existing record
$lastdate = $row_change2['date']; // reset the last visit date to current IP record's date
$query = "DELETE FROM sessionlog WHERE id=$id LIMIT 1"; // delete the current IP row
mysql_query($query);
}
$query = "UPDATE sessionlog SET history='$history' WHERE id='$firstid' LIMIT 1"; // update the history of the first record with the new history
mysql_query($query, $link);
}I know it's a hella-lot of database querying/updating which I assume is why I'm getting a 500 through my browser. I assume it'd help to take the DELETE outside of the second while(), but I'm not experienced with arrays so not sure how to do this for all the found/matching IP records.
Any advice as to how to do this in a non-totally bodged way would be great
.Edit: Now get...
Last edited:
.
.
. The last export (yesterday) was 1.3MB, so the script must've been doing something, though no idea what. I've deleted and replaced the table with the 1.3MB backup now anyway.
.