[RESOLVED] mysql_query: INSERT does not insert anything at all...

[Copenhagener]

Hi there,

I am able to read from my database table, but I am not able to write.

As far as I can see, my code is correct, and my database table has been set up correctly. However, when I test my script, nothing is inserted into my table. I have no idea what is wrong.

Can anyone figure out what is missing or incorrect in my SQL query?

My script is as follows:

			include("archive/file.inc");

		$connection = mysql_connect($host, $account, $password)
			or die(mysql_error());

		$db = mysql_select_db($dbname, $connection)
			or die(mysql_error());

		$query = "INSERT INTO users (subscr_date, subscr_time, subscr_ip, signature, password, email, key, name, gender, dob, area) VALUES ('$subscr_date', '$subscr_time', '$subscr_ip', '$signature', '$password', '$email', '$key','$name', '$gender', '$dob', '$area')";

		$result = $mysql_query($query)
			or die(mysql_error());

		mysql_close($connection);

PHP version: 5.2
MySQL version: 5.0

Thank you very much in advance for any help.

[NogDog]

Anything in the PHP error log, in case display_errors is turned off in your config?

[Copenhagener]

Rewrote the script to get some errors:

			include("archive/file.inc");

		$connection = mysql_connect($host, $account, $password)
			or die("Error: ".mysql_error());

		$db = mysql_select_db($dbname, $connection)
			or die("Error :".mysql_error());

		$create = "INSERT INTO users (subscr_date, subscr_time, subscr_ip, signature, password, email, key, name, gender, dob, area) VALUES ('$subscr_date', '$subscr_time', '$subscr_ip', 

'$signature', '$password', '$email', '$key','$name', '$gender', '$dob', '$area')";

		$result = mysql_query($create)
			or die("Error: ".mysql_error());

		mysql_close($connection);

Here is the error message from SQL:

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 'key, name, gender, dob, area) VALUES ('1257580509', '', '111.222.333.444', 'aa', ' at line 1

I wonder if it is related to my script, or to the values I try to enter? I am not strong at SQL. The MySQL server version is 5.0.

Can anyone spot the mistake here?

Thanks a lot in advance.

[Copenhagener]

I now suspect that there is a problem with the way I handle date() and time() and the way I attempt to store these values. It seems that a value based on the time() function becomes empty and returns an error when trying to insert it into a NOT NULL field.

Don't laugh if the following is complete nonsense; at least then that is where my error is to be found:

	$dob = (int)$year.$month.$day;

$subscr_date = (int)time('Ymd');

$subsrc_time = (int)time('Hi');

$dob should become YYYYmmdd (e.g. 19600704)

$subscr_date should become YYYYmmdd (e.g. 20091107)

$subscr_time should become HHii (e.g. 1705)

For some reason, $subscr_time becomes NULL.

Also, in the SQL query, I have removed the quotations around the numeric values:

			$create = "INSERT INTO users (subscr_date, subscr_time, subscr_ip, signature, password, email, key, name, gender, dob, area) VALUES ($subscr_date, $subscr_time, '$subscr_ip', 

'$signature', '$password', '$email', '$key','$name', '$gender', $dob, '$area')";

dob is INT (8) NOT NULL
subscr_date is INT (8) NOT NULL
subscr_time is INT (4) NOT NULL

I think I am approaching a solution, but any inputs or ideas are still more than welcome, as I have been stuck for hours with this.

Thanks..

[Copenhagener]

While testing some example results, I get:

$dob >> 19900304 (correct; 4 March 1990)

$subscr_date >> 1257584137 (something is definitely wrong here)

$subscr_time >> NULL

[Copenhagener]

I tried to fix the script for the values $subscr_date and $subscr_time.

Now, $subscr_time is no longer NULL, so that was not the only problem. The values become 10 digits long, while the coloumns are INT (8) each. Perhaps this is generating the syntax error now, and further work with thos two values are required.

[NogDog]

My first thought is that those fields should be DATE or TIME column types, not integers. If so, then the values you would insert would be strings ('2009-02-31' or '12:51') instead of being cast to integers. (In fact, I would just create a single DATETIME column for subscription date/time.)

Anyway, if you want to continue with the current scheme, use [man]date/man instead of [man]time/man to get the values for the current date/time.

[Copenhagener]

Thanks for your inputs, MotDog.

I rewrote my script with regards to the date and time, and now at least I get the right values:

	$time_array = getdate();

foreach($time_array as $item => $val ) {
	$item = $val;
}

$thisYear = $time_array[year];
$thisMonth = $time_array[mon];
$thisDay = $time_array[mday];
$thisHour = $time_array[hours];
$thisMin = $time_array[minutes];

if($thisMonth < 10) $thisMonth = "0".$thisMonth;
if($thisDay < 10) $thisDay = "0".$thisDay;
if($thisHour < 10) $thisHour = "0".$thisHour;
if($thisMin < 10) $thisMin = "0".$thisMin;

$subscr_date = (int)$thisYear.$thisMonth.$thisDay;

$subscr_time = (int)$thisHour.$thisMin;

However, I still get a MySQL error:

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 'key, name, gender, dob, area) VALUES (20091107, 1239, '123.123.123.123', 'aa', 'i' at line 1

My SQL query again:

$create = "INSERT INTO users (subscr_date, subscr_time, subscr_ip, signature, password, email, key, name, gender, dob, area) VALUES ($subscr_date, $subscr_time, '$subscr_ip', '$signature', '$password', '$email', '$key','$name', '$gender', $dob, '$area')";

I store the md5'ed password as VARCHAR (32) is that correct?

Anything else that could generate the error?

I hoped I had found the problem, but appearantly it was not enough...

[Lars_Berg]

key is a reserved word in Mysql. Rename column or escape it as key

[Copenhagener]

Wow! Thanks, Lars - that was really the KEY to the whole problem!

Honestly, I would never had realised that I was using a reserved term.

Here is my final script and query which WORKS:

			include("archive/file.inc");

		$connection = mysql_connect($host, $account, $password)
			or die("Error: ".mysql_error());

		$db = mysql_select_db($dbname, $connection)
			or die("Error: ".mysql_error());

		$create = "INSERT INTO users (subscr_date, subscr_time, subscr_ip, signature, password, email, token, name, gender, dob, area) VALUES ($subscr_date, $subscr_time, '$subscr_ip', '$signature', '$password', '$email', '$token', '$name', '$gender', $dob, '$area')";

		$result = mysql_query($create)
			or die("Error: ".mysql_error());

		mysql_close($connection);

And the coloumn "key" has been renamed to "token" in the database table, being a VARCHAR (8).

Thank you, Lars.

[Copenhagener]

An important note to the solution:

I also found a related mistake, that I used $password for both the MySQL login and for the user password.

Once the script and query worked, I wondered why it stored the same password every time, although I tested with different password - and it was not stored as a 32-digit hex, either. Then I finally noticed that it was my own MySQL password, and when I looked at my script again, I knew I had to rename the password value from the form input to something else, and made it $userpw instead:

			include("archive/file.inc");

		$connection = mysql_connect($host, $account, $password)
			or die("Error: ".mysql_error());

		$db = mysql_select_db($dbname, $connection)
			or die("Error: ".mysql_error());

		$create = "INSERT INTO users (subscr_date, subscr_time, subscr_ip, signature, userpw, email, token, name, gender, dob, area) VALUES ($subscr_date, $subscr_time, '$subscr_ip', 

'$signature', '$userpw', '$email', '$token', '$name', '$gender', $dob, '$area')";

		$result = mysql_query($create)
			or die("Error: ".mysql_error());

		mysql_close($connection);

At the same time, in MySQL I renamed the coloumn 'password' to 'userpw'.

And now everything works correctly.