Page 1 of 1
[SOLVED] Can not insert into databse - PHP/MySQL
Posted: Sat Feb 24, 2007 6:01 am
by benyboi
This may be a MySQL question but it uses PHP so here goes:
I have on one of my webpages this code:
Code: Select all
<?
include("dbaccess.php");
$ref2=$_GET['ref'];
$invalidChars=array("/","\\","\"",";");
$ref=str_replace($invalidChars,"",$ref2);
$ip=$_SERVER['REMOTE_ADDR'];
$datetime = date("Y-n-j, H-i-s, O I");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO visit_history VALUES ('','$ip','$datetime','$ref')";
mysql_query($query);
mysql_close();
?>
This script used to work until i moved to a dedicated server. Now nothing is inserted into the database - but there are no errors reported (and yes error reporting is on).
Any help much apprechiated.
Thanks,
Ben
Posted: Sat Feb 24, 2007 6:33 am
by reecec
have you used mysql_error() to check the error/
Regards
Reece
Posted: Sat Feb 24, 2007 6:43 am
by benyboi
That would be a start, could you possibly tell me how to link this into my existing code? I just did a google search but theres nothing really that helpful to me.
Thank you very much.
Posted: Sat Feb 24, 2007 9:07 am
by RobertGonzalez
Code: Select all
<?php
// always make sure to use full opening php tags
include("dbaccess.php");
$ref2=$_GET['ref'];
$invalidChars=array("/","\\","\"",";");
$ref=str_replace($invalidChars,"",$ref2);
$ip=$_SERVER['REMOTE_ADDR'];
$datetime = date("Y-n-j, H-i-s, O I");
// I think you might want to quote localhost here
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO visit_history VALUES ('','$ip','$datetime','$ref')";
// error check the query
mysql_query($query) or die('Error: ' . mysql_error());
// You might want to look into mysql_affected_rows() as well
if (mysql_affected_rows())
{
echo 'Yippee!';
}
else
{
echo 'It didnt take';
}
mysql_close();
?>
Posted: Sat Feb 24, 2007 9:20 am
by benyboi
Thank you very much for that!
Now next problem, getting error:
Error: Out of range value adjusted for column 'id' at row 1
Would this be better for mysql query:
Code: Select all
mysql_query("INSERT INTO visit_history (id, ip, datetime, ref) VALUES('','$ip','$datetime','$ref' ) ") or die(mysql_error());
Thank you very much for your help.
Posted: Sat Feb 24, 2007 9:28 am
by benyboi
Stupid thing to ask!
I have just tried it but no different, same error.
Posted: Sat Feb 24, 2007 9:37 am
by RobertGonzalez
Have you recently changed the id field to an autoincrement field? That error you are getting usually happens when you a) change to an autoincrement type column after there is data in the table or b) set a maximum value on the column and try to add a value that is more than that max value.
Posted: Sat Feb 24, 2007 9:44 am
by benyboi
That could be it. I didn't change the autoincrement, that was done when i first made the table. But the max values, that could be it.
Here is my structure for the table:
Field Type
id int(32)
ip varchar(255)
datetime varchar(255)
ref varchar(255)
I thought that the 32 meant how many digits, not max value. I tried increasing this but 255 is the max. Would changing type to text work?
Thanks
Posted: Sat Feb 24, 2007 9:51 am
by benyboi
Cancel that, i tried changing to text but it said:
#1063 - Incorrect column specifier for column 'id'
(using phpmyadmin)
I also tried BIGINT but max value is 255 again.
Thanks
Posted: Sat Feb 24, 2007 9:53 am
by RobertGonzalez
I thought INT maxed at 11 (characters, not value). I think to get to 32 you need to use BIGINT. I think. I would check to make sure your data is of the correct types before inserting. You may be getting the error because of that. I think I did that once, changing a field from text to int and got the same error.
Posted: Sat Feb 24, 2007 10:02 am
by benyboi
Changed to BIGINT(255), still no luck.
So i tried something different:
Code: Select all
<?php
include("dbaccess.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM visit_history ORDER BY id DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < 1) {
$nid=mysql_result($result,$i,"id") + 1;
$i++;
}
$ref2=$_GET['ref'];
$invalidChars=array("/","\\","\"",";");
$ref=str_replace($invalidChars,"",$ref2);
$ip=$_SERVER['REMOTE_ADDR'];
$datetime = date("Y-n-j, H-i-s, O I");
// I think you might want to quote localhost here
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = ("INSERT INTO `visit_history` ( `id` , `ip` , `datetime` , `ref` ) VALUES ('$nid','$ip','$datetime','$ref')") or die(mysql_error());
// error check the query
mysql_query($query) or die('Error: ' . mysql_error());
mysql_close();
?>
Basically I get the last ID from table and then insert new row with ID + 1.
And now it works
But thank you very much for the help you gave!