CODEWITHSUNDEEP
mysqlsqldatabase
Umar Farooq Khan
Umar Farooq Khan

Reputation: 11

I cannot insert into database

I can't insert data to MySQL database

  DROP TABLE IF EXISTS `register`;
    CREATE TABLE `register` (
      `id` int(11) NOT NULL,
      `username` varchar(100) NOT NULL,
      `unique_code` varchar(300) NOT NULL,
      `email` varchar(300) NOT NULL,
      `name` varchar(300) NOT NULL,
      `mobile` varchar(200) NOT NULL,
      `address` longtext NOT NULL,
      `state` varchar(50) NOT NULL,
      `country` varchar(50) NOT NULL,
      `zipcode` varchar(50) NOT NULL,
      `city` varchar(200) NOT NULL,
      `password` varchar(300) NOT NULL,
      `temp_password` varchar(300) NOT NULL,
      `image` varchar(300) NOT NULL,
      `phone_verified` int(11) NOT NULL,
      `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `status` int(11) NOT NULL,
      `gcm_code` longtext NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When I try to register I get this error and no data is added in msql table.

INSERT INTO register (
    username,
    mobile,
    password,
    temp_password,
    email,
    status
)
VALUES (
    "user",
    "1234678",
    "202cb962ac59075b964b07152d234b70",
    "123",
    "[email protected]",
    ""
)
Error in query.

Here is my MySQL database connection file 

define('DIRECT_ACCESS', true);
define('HOST','localhost');
define('DATABASE','id9346797_market');
define('USERNAME','idxxxxxxxxxxxx');
define('PASSWORD','xxxxxxxx');
define('DB_PREFIX','');
define('SITE_URL', (@$_SERVER['HTTPS'] ? "https" : "http") . "://" . $_SERVER['HTTP_HOST']."/fruitmarket/");

i would really appreciate if someone resolves this issue.

Update: I change the previous table to this.

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `username` varchar(60) NOT NULL,
  `mobile` varchar(60) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(255) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `type` varchar(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and change mysql database last line

from this

define('SITE_URL', (@$_SERVER['HTTPS'] ? "https" : "http") . "://" . $_SERVER['HTTP_HOST']."/fruitmarket/");

to this 

define('SITE_URL','http://localhost/fruitmarket/');

i am still getting the same error.

here is registeration form

<?php  common::user_access_only("admin");
    if(isset($_POST['submit']))
    {
        form_validation::add_validation('username', 'required', 'Registration Name');
         form_validation::add_validation('username', 'no_space', 'Registration Name');
         form_validation::add_validation('mobile', 'required', 'mobile');
        form_validation::add_validation('email', 'required', 'Registration email');
        form_validation::add_validation('email', 'email', 'Registration email not valid');
        form_validation::add_validation('password', 'required', 'Registration Passowrd');
        form_validation::add_validation('password', 'no_space', 'Registration Passowrd');
        if(form_validation::validate_fields())
        {
            $username=common::get_control_value("username");
            $mobile = common::get_control_value("mobile");
            $password=common::get_control_value("password");
            $email=common::get_control_value("email");
            $active = common::get_control_value("status"); 

            $q = new Query();
            $q->insert_into("register",array("username"=>$username,"mobile"=>$mobile,"password"=>md5($password),"temp_password"=>$password,"email"=>$email,"status"=>$active))
            ->show()
            ->run();
            common::set_message(3);
            common::redirect_to(common::get_component_link(array("appuser","list")));

        }
    }
?>

Upvotes: 1

Views: 157

Answers (4)

mgcarpizo
mgcarpizo

Reputation: 33

The record is not inserted because you have placed NOT NULL for each column which means that in order for a record to be inserted values for all columns should be provided.

If you like to insert a record with only those values set, you have to remove the NOT NULL definition for the other columns.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You have a zillion columns in your table that are declared NOT NULL without default values, such as zip_codeand city.

You need to do one of three things:

  • Insert values for all such columns when inserting rows.
  • Provide default values in the definition of the table.
  • Remove the NOT NULL constraint.

I'm not sure which is appropriate for your purposes.

Upvotes: 4

alroc
alroc

Reputation: 28144

The error message is doing you no favors here, is that the entirety of what MySQL is returning?

You have a large number of fields in your table which are defined as NOT NULL and do not have default constraints. But your INSERT query does not specify values for all of them. You need to do one (or more) of the following:

  • INSERT values for all the fields in your table
  • Create default constraints for your NOT NULL fields
  • Redefine the table to allow those fields to be NULL

Upvotes: 5

Hien Nguyen
Hien Nguyen

Reputation: 18975

The status is INT not VARCHAR

INSERT INTO register (
    username,
    mobile,
    password,
    temp_password,
    email,
    status
)
VALUES (
    "user",
    "1234678",
    "202cb962ac59075b964b07152d234b70",
    "123",
    "[email protected]",
    0 // not varchar
)

Upvotes: 1

Related Questions