[RESOLVED] Database not updating

Jon12345

I have the following code to add a record into my mysql database:

<?php
$kid="abc";
$ipaddress="12342"; 
$sid="7Search";
$time = date("H:i:s"); 
$date = date("m.d.y");

$db=mysql_connect("localhost","user","pw");
mysql_select_db("pmm__log",$db);
$query="INSERT INTO visitor_log (ipaddress,date,time,kid,sid) VALUES ('$ipaddress','$date','$time','$kid','$sid')";
mysql_query($query);

mysql_close()  

?>

But it doesn't update. I cannot work out why. How would I check to find the cause of this? Is there some kind of error logging I can add? I've spent hours on this. 🙁

Thanks,

Jon

ClarkF1

Try changing

mysql_query($query);

mysql_query($query) or die('Database Query Error: '.mysql_error());

If you are using any of the mysql reserved words as column names which I think you may be, you need to put them in backquotes

e.g

$query="INSERT INTO visitor_log (`ipaddress`,`date`,`time`,`kid`,`sid`) VALUES ('$ipaddress','$date','$time','$kid','$sid')";

NogDog

More than likely, the query is failing because you've used a column name called "date" which is also a reserved word in MySQL (for the DATE() function). You'll need to enclose it in "back-quotes" so that MySQL knows it's an identifier and not a function name:

INSERT INTO visitor_log (ipaddress,`date`,time,kid,sid)......

(Note: it doesn't hurt to back-quote all table names and column names, just to be safe.)

A useful practice while creating your scripts is to always test mysql_query() calls for failures (and in this case also check to see if any rows were updated):

$result = mysql_query($query);
if(!$result)
{
  user_error("Query failed: $query - " . mysql_error(), E_USER_WARNING);
}
elseif(mysql_affected_rows() == 0)
{
  user_error("DB record not inserted: $query - " . mysql_error(), E_USER_WARNING);
}

NogDog

PS: You may be noticing a theme to my responses. In programming, if something in your code can fail, then it probably will (to paraphrase Murphy's Law); so it's usually best to assume it will fail and add defensive code to handle that error in some appropriate manner.

Jon12345

Using ClarkF1's code, I get the following error:

Database Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ipaddress','date','time','kid','sid') VALUES ('12342','12.11.06

I still can't work out what is wrong! Any ideas?

Jon12345

I notice under the Collation column in phpmyadmin, it says "latin1_swedish_ci" on all fields except id, time and date. Is this anything to do with it?

NogDog

Jon12345 wrote:
I notice under the Collation column in phpmyadmin, it says "latin1_swedish_ci" on all fields except id, time and date. Is this anything to do with it?

Assuming those columns are integer, time, and date type fields, then no it would not make a difference (the collation type only affecting text or varchar type fields).

NogDog

Jon12345 wrote:
Using ClarkF1's code, I get the following error:

Database Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ipaddress','date','time','kid','sid') VALUES ('12342','12.11.06

I still can't work out what is wrong! Any ideas?

Can you output the actual query you used along with the mysql_error() message (as per my previous code examples), so that we can see exactly what the query text is that is causing that error?

Jon12345

Found solution. I had two underscores here: pmm__log

Bloody stupid error!