can't insert data to mysql

[mrlol]

Hi guys,

Need your help. Currently my development server using Wamp server. please read below for services details:
* Apache/2.2.11 (Win32) PHP/5.3.0
* MySQL client version: mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $
* PHP extension: mysqli
* Server: localhost (MySQL host info: localhost via TCP/IP)
* Server version: 5.1.36-community-log
* Protocol version: 10
* User: root@localhost
* MySQL charset: UTF-8 Unicode (utf8)

My production server using manual configuration. read below for details:
* Apache/2.2.17 (Win32) PHP/5.3.4
* MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
* PHP extension: mysql
* Server: localhost via TCP/IP
* Server version: 5.5.8
* Protocol version: 10
* User: root@localhost
* MySQL charset: UTF-8 Unicode (utf8)

Im using PHP as web programming language. my problem is right now (production server), i cant insert data to table that have more then 40 fields.
but no problem with table that have 10, 7, 22, etc fields.
development server also got no problem with that 40fields.

what should I do now? please guide me.. thank you.

[R0bb0b]

Hi guys,

Need your help. Currently my development server using Wamp server. please read below for services details:
* Apache/2.2.11 (Win32) PHP/5.3.0
* MySQL client version: mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $
* PHP extension: mysqli
* Server: localhost (MySQL host info: localhost via TCP/IP)
* Server version: 5.1.36-community-log
* Protocol version: 10
* User: root@localhost
* MySQL charset: UTF-8 Unicode (utf8)

My production server using manual configuration. read below for details:
* Apache/2.2.17 (Win32) PHP/5.3.4
* MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
* PHP extension: mysql
* Server: localhost via TCP/IP
* Server version: 5.5.8
* Protocol version: 10
* User: root@localhost
* MySQL charset: UTF-8 Unicode (utf8)

Im using PHP as web programming language. my problem is right now (production server), i cant insert data to table that have more then 40 fields.
but no problem with table that have 10, 7, 22, etc fields.
development server also got no problem with that 40fields.

what should I do now? please guide me.. thank you.

what response/errors are you getting from mysql?

[mrlol]

no error msg from mysql, but the error was defined by me. please take a look:

if($result) {
echo "<script language=\"JavaScript1.2\">alert(\"Assignment Created Succesfully\") </script>";

   }
   else { echo "Error: User Not Created. Please Try Again. If Problem Persist, Please Contact John" ; }

[R0bb0b]

no error msg from mysql, but the error was defined by me. please take a look:

if($result) {
echo "<script language=\"JavaScript1.2\">alert(\"Assignment Created Succesfully\") </script>";

   }
   else { echo "Error: User Not Created. Please Try Again. If Problem Persist, Please Contact John" ; }

you should output and copy and paste your query into the command line interface or what ever you are using to manage your database to get the actual error. You should be able to echo mysql_error() and get the error that way as well.

[mrlol]

ok sir,

I run this script in SQL query at PhpMyadmin

INSERT INTO auditor_only(ag_title) VALUES("awefwefaa")

MYSQL fields:
Id, ag_code, ag_title, etc..............45fields total..

result at production server (real):

#1364 - Field 'ag_code' doesn't have a default value

result at development server (testing):

1 row(s) inserted.
Inserted row id: 81 ( Query took 0.1559 sec )

same script but different result. any clue sir?

[Shanti C]

set ag_code field to null in your data base table or try to insert null value for ag_code like:

INSERT INTO auditor_only(ag_title,ag_code) VALUES("awefwefaa","")

And check these type of fields any more in your table and set them for null..

or post your structure of table..

[mrlol]

thanks for your reply sir,

but sir the table structure 100% same with testing and production server. In the testing server, its fine... but in the production server, i dont know what is happening...

and of course the script is 100% same.

for your information, another table with less field is doing fine.. even if there's no value in the field.. the query still can run successfully..

please refer attachments for your reference...

this is the problem table (only at production server).. 45 fields..

this one is fine both at testing and production server.. 12fields.. can accept null value run without problem at testing server

same query but got error at production server..

I'm totally blur.. please guide and help me. Thank you.

[R0bb0b]

thanks for your reply sir,

but sir the table structure 100% same with testing and production server. In the testing server, its fine... but in the production server, i dont know what is happening...

and of course the script is 100% same.

for your information, another table with less field is doing fine.. even if there's no value in the field.. the query still can run successfully..

please refer attachments for your reference...

[ATTACH]18666[/ATTACH]
this is the problem table (only at production server).. 45 fields..

[ATTACH]18667[/ATTACH]
this one is fine both at testing and production server.. 12fields.. can accept null value

[ATTACH]18668[/ATTACH]
run without problem at testing server

[ATTACH]18669[/ATTACH]
same query but got error at production server..

I'm totally blur.. please guide and help me. Thank you.

"#1364 - Field 'ag_code' doesn't have a default value"
this means that you are trying to force a null on a not null field that doesn't have a default value.

So you have three options:
change the field to accept null values:

alter table tablename change ag_code ag_code varchar(100);

add a default value for the field:

alter table tablename change ag_code ag_code varchar(100) not null default 'default value';

or do what Shanti explained.

EDIT: the number of fields may seem like it is the issue but I assure you that it is not.

[mrlol]

thanks for your reply sir.

Ok I've done few troubleshooting through the system and phpmyadmin query.

please see the attachments.

I copy and paste the query from the system and paste it at testing server. query run successfully.

same with above but i run the query at production server. still got error. i've change the setting for the field to NOT NULL and DEFAULT value is 0000-00-00 but still cant. please refer attachment below.

I've also changed the PHP extension from mysql to mysqli, but still can't.

what i am thinking right now?:
1. If the php script got an error in it, of course both of servers can't execute the query. but currently, testing server got no problem.
2. If table structure was wrong, why in testing server the same query doing well?
3. Is it because of mysql/php version issue??? supposedly it shouldnt have any problem because in the production server, we're using latest version of mysql and php. OR actually because of latest version can't support null value?

please guide me. your commitment to help me to solve this problem is highly appreciated.

thank you.

[R0bb0b]

thanks for your reply sir.

Ok I've done few troubleshooting through the system and phpmyadmin query.

please see the attachments.

[ATTACH]18670[/ATTACH]
I copy and paste the query from the system and paste it at testing server. query run successfully.

[ATTACH]18671[/ATTACH]
same with above but i run the query at production server. still got error. i've change the setting for the field to NOT NULL and DEFAULT value is 0000-00-00 but still cant. please refer attachment below.
[ATTACH]18672[/ATTACH]

I've also changed the PHP extension from mysql to mysqli, but still can't.

what i am thinking right now?:
1. If the php script got an error in it, of course both of servers can't execute the query. but currently, testing server got no problem.
2. If table structure was wrong, why in testing server the same query doing well?
3. Is it because of mysql/php version issue??? supposedly it shouldnt have any problem because in the production server, we're using latest version of mysql and php. OR actually because of latest version can't support null value?

please guide me. your commitment to help me to solve this problem is highly appreciated.

thank you.

This may not be the only field that is giving you issues. Continue outputing errors when you run the query in production. Just follow the same steps that you followed to get the "#1364 - Field 'ag_code' doesn't have a default value" error.