MySQL updates wrong field

Anon

Hi All

I have a MySQL user logging script which inserts a new field when a user enters a page.

I have been loocking at the file and I've noticed something very strange:

SOMETIMES the script updates my 'query' field with the info which should go into the 'time_in' field next to it.

All pages call the same script and the same SQL statement for the update, and there doesn't seem to be any reason why it occasionally misses the right field (one user updated correctly and then three minutes later the next record got messed up).

My code (the only relevant bit) is here:

$sql_cont= "INSERT INTO visitor VALUES ('$id', '$cook', '$ip', '$refer', '$browse', '$timein, '$timedayin', '$date')";
$conn = mysql_connect($db_host,$db_user,$db_pass)
or die("Connection to database server failed");
$db = mysql_select_db($db_name,$conn)
or die("Selection of database failed");
$result_cont = mysql_query($sql_cont);

Thanks to anyone who throw any light on this bizarre behaviour.

matto

if you can't locate the bug (as me 🙁 ), try using the other insert syntax:

insert into table (field1, field2, ...) values (value1, value2, ...)

now, mysql MUST do the right thing. if an error message appears, this will help you finding the logical bug.

that's my suggestion, maybe there are better ways.

Anon

Oops, I posted the wrong syntax - I was actually using the method you described:

$sql_ins= "INSERT INTO warstats (id, cookie, url, path, query, time_in) VALUES ('', '$usercook', '$cur_url', '$path', '$query', '$timestamp')";
$conn = mysql_connect($db_host,$db_user,$db_pass)
or die("Connection to database server failed");
$db = mysql_select_db($db_name,$conn)
or die("Selection of database failed");
$result_ins = mysql_query($sql_ins)
or die("Execution of query 3 failed");

I can't figure out why it would do something differently on random occasions - surely a script like this is definitive?

[deleted]

Try using some error checking, read this:

http://www.xs4all.nl/~vinny/index.html#4

Anon

Thanks - I checked out the error checking stuff, but I already have die statements in my query.

The query definately doesn't fail, or there wouldn't be a record to view in the table.

I have just looked at a recent user who hit 14 pages. the first 13 logged fine, but the last one had moved the 'time_in' value and inserted it into the adjacent 'query' field which is normally filled with a query string.

The exact same script is called from the first line of each page.

This is so strange I have no ideas at all.

Please help, my stats are screwed and my boss is hassling me 🙁

[deleted]

using 'or die' is not error checking, it's error escaping :-)

anyway, is this a typo or are there qquotes missing here?

"INSERT INTO visitor VALUES ('$id', '$cook', '$ip', '$refer', '$browse', '$timein, '$timedayin', '$date')";

after the $timein...

Anon

Yep, you're right, those quotes were missing and that was probably throwing things out a bit...

After trawling the stats for the last couple of hours I spotted the pattern and have managed to track things back to a function call.

Essentially it was a variable passing problem which was shifting the records along one place - but due to the function call it was only happening under certain conditions.

Thanks a lot for your input anyway 🙂 just another case of 'code blindness'...