[SOLVED] Can not insert into databse - PHP/MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

[SOLVED] Can not insert into databse - PHP/MySQL

Post 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
Last edited by benyboi on Sat Feb 24, 2007 10:02 am, edited 2 times in total.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

have you used mysql_error() to check the error/



Regards

Reece
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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();
?>
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

Post by benyboi »

Thank you very much for that! :D

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.
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

Post by benyboi »

Stupid thing to ask!

I have just tried it but no different, same error.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

Post 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
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
benyboi
Forum Commoner
Posts: 80
Joined: Sat Feb 24, 2007 5:37 am

Post 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 :D

But thank you very much for the help you gave!
Post Reply