php - PDO Query not inserting in Database

588

The following code is created to register a user in my website. When I try to register a user the 'query' is being executed but the new record in the database is not shown.

The following code is the register form:

<div >
 <div >
  <div >
    <h2>Register Now</h2>

  </div>
  <div>
  <form id="defaultForm" method="post" name="registerform" action="index.php">
    <div >
      <div >
        <?php include('include/showErrors.php'); ?>
      </div>
    </div>
    <div >
      <label >Username</label>
      <div >
        <input type="text" name="username" autocomplete="off" />
      </div>
    </div>

    <div >
      <label >Email address</label>
      <div >
        <input type="email" name="email" autocomplete="off" />
      </div>
    </div>

    <div >
      <label >Password</label>
      <div >
        <input type="password" name="password" autocomplete="off" />
      </div>
    </div>

    <div >
      <label >Age</label>
      <div >
        <input type="text" name="age" autocomplete="off" />
      </div>
    </div>

    <div >
      <label >Sex</label>
      <div >
        <input type="text" name="sex" autocomplete="off" />
      </div>
    </div>


    <div >
      <label >Country</label>
      <div >
        <input type="text" name="country" autocomplete="off" />
      </div>
    </div>

    <div >
      <div >
        <button name="Submit" type="submit" >Sign up</button>
      </div>
    </div>
    <br>
  </form>
 </div>
</div>

The following is the doRegister user:

class Registration
{
    private $dbCon = null;
    public $regSuccessful = false;
    public $verificationSuccess = false;
    public $errors = array();
    public $messages = array();


    //the below function will automaticcaly start when a obejct of this class is created
    public function __construct()
    {
        //session_start();
        if(isset($_POST["Submit"]))
        {
            $this->registerUser($_POST['username'], $_POST['password'],$_POST['email'],$_POST['age'],$_POST['sex'],$_POST['country']);
        }
        else if (isset($_GET["id"]) && isset ($_GET["verification_code"]))
        {
            $this->verifyUser($_GET["id"], $_GET["verification_code"]);
        }
    }

    //the following methods checks if a database connection is open or not
    private function dbConnection()
    {
        if($this->dbCon != null)
        {
            return true;
        }
        else 
        {
            //create database connection
            try
            {
                $this->dbCon = new PDO('mysql:host='. DB_HOST .';dbname='. DB_NAME . ';charset=utf8', DB_USER, DB_PASS);
                return true;
            } catch (PDOException $ex) {
                $this->errors[] = MESSAGE_DATABASE_ERROR;
                return false;
            }
        }
    }

    //the following method will handle and the registration errors
    private function registerUser($username,$password,$email,$age, $sex,$country)
    {
        //this will remove extra spaces
        $username = trim($username);
        $email = trim($email);
        $sex = trim($sex);
        $country = trim($country);


        if(empty($username))
        {
            $this->errors[] = MESSAGE_USERNAME_EMPTY;
        }
        else if(empty($password))
        {
            $this->errors[] = MESSAGE_PASSWORD_EMPTY;
        }
        else if(empty($country))
        {
            $this->errors[] = MESSAGE_COUNTRY_EMPTY;
        }
        else if(empty($sex))
        {
            $this->errors[] = MESSAGE_SEX_EMPTY;
        }
        else if(empty($age))
        {
            $this->errors[] = MESSAGE_AGE_EMPTY;
        }
        else if(strlen($password) < 6)
        {
            $this->errors[] = MESSAGE_PASSWORD_TOO_SHORT;
        }
        elseif (strlen($username) > 64 || strlen($username) < 2) 
        {
            $this->errors[] = MESSAGE_USERNAME_BAD_LENGTH;
        }
        elseif (!preg_match('/^[a-z\d]{2,64}$/i', $username)) {
            $this->errors[] = MESSAGE_USERNAME_INVALID;
        } elseif (!preg_match('/^[a-z\d]{2,64}$/i', $country)) {
            $this->errors[] = MESSAGE_COUNTRY_INVALID;
        }
         elseif (!preg_match('/^[a-z\d]{2,64}$/i', $sex)) {
            $this->errors[] = MESSAGE_SEX_INVALID;
        }
        elseif (empty($email)) {
            $this->errors[] = MESSAGE_EMAIL_EMPTY;
        } elseif (strlen($email) > 64) {
            $this->errors[] = MESSAGE_EMAIL_TOO_LONG;
        } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $this->errors[] = MESSAGE_EMAIL_INVALID;

    }else if ($this->dbConnection())
    {
        // check if username or email already exists
            $check_user_name = $this->dbCon->prepare('SELECT username, email FROM tbl_users WHERE username=:username OR email=:email');
            $check_user_name->bindValue(':username', $username, PDO::PARAM_STR);
            $check_user_name->bindValue(':email', $email, PDO::PARAM_STR);
            $check_user_name->execute();
            $result = $check_user_name->fetchAll();

            // if username or/and email find in the database
            // TODO: this is really awful!
            if (count($result) > 0) {
                for ($i = 0; $i < count($result); $i++) {
                    $this->errors[] = ($result[$i]['username'] == $username) ? MESSAGE_USERNAME_EXISTS : MESSAGE_EMAIL_ALREADY_EXISTS;
                }
            } else {
                // check if we have a constant HASH_COST_FACTOR defined (in config/hashing.php),
                // if so: put the value into $hash_cost_factor, if not, make $hash_cost_factor = null
                //$hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);

                //the following will encrypt users password with the PHP 5.5's hash function
                //$userPassHash = password_hash($password, PASSWORD_BCRYPT);

//                $userPassHash = password_hash( $password, PASSWORD_BCRYPT, array(
//                    'cost' => 12
//                ));
                //this will generate a random hash for email verification
                $user_activation_hash = sha1(uniqid(mt_rand()), true);

                //the following will write a new user data into the database
                $queryUserInsert = $this->dbCon->prepare('INSERT INTO tbl_users (username, password, email, user_activation_hash, user_registration_ip, user_registration_datetime,age,sex,country) VALUES (:username, :userPassHash, :email, :user_activation_hash, :user_registration_ip, now()), :age, :sex, :country');
                $queryUserInsert->bindValue(':username', $username, PDO::PARAM_STR);
                $queryUserInsert->bindValue(':userPassHash', $password, PDO::PARAM_STR);
                $queryUserInsert->bindValue(':email', $email, PDO::PARAM_STR);
                $queryUserInsert->bindValue(':user_activation_hash', $user_activation_hash, PDO::PARAM_STR);
                $queryUserInsert->bindValue(':user_registration_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
                $queryUserInsert->bindValue(':age', $age, PDO::PARAM_STR);
                $queryUserInsert->bindValue(':sex', $sex, PDO::PARAM_STR);
                $queryUserInsert->bindValue(':country', $country, PDO::PARAM_STR);
                $queryUserInsert->execute();

                //id of the new user registered
                //$user_id = $this->dbCon->lastInsertId();


                //checks if the query was succesfull, and send verification email
                if($queryUserInsert)
                {
                   $this->messages[] = MESSAGE_REGISTRATION_ACTIVATION_SUCCESSFUL;
                }
                else
                {
                    $this->errors[] = MESSAGE_REGISTRATION_FAILED;
                }
            }

    }


}


}

Screenshot of the Database:

enter image description here

603

Answer

Solution:

Use this insert query:

$queryUserInsert = $this->dbCon->prepare('INSERT INTO tbl_users (username, password, email, user_activation_hash, user_registration_ip, user_registration_datetime,age,sex,country) VALUES (:username, :userPassHash, :email, :user_activation_hash, :user_registration_ip, now(), :age, :sex, :country)');

You have added an extra) afternow() function in query; Put it after:country

315

Answer

Solution:

Change

$queryUserInsert = $this->dbCon->prepare('INSERT INTO tbl_users (username, password, email, user_activation_hash, user_registration_ip, user_registration_datetime,age,sex,country) VALUES (:username, :userPassHash, :email, :user_activation_hash, :user_registration_ip, now()), :age, :sex, :country');
                                                                                                                                                                                                                                                                                ^ Extra Closing Bracket ^ Closing Bracket For VALUES missing 

To

$queryUserInsert = $this->dbCon->prepare('INSERT INTO tbl_users (username, password, email, user_activation_hash, user_registration_ip, user_registration_datetime,age,sex,country) VALUES (:username, :userPassHash, :email, :user_activation_hash, :user_registration_ip, now(), :age, :sex, :country)');
  1. Extra closing bracket in now()
  2. Didn't closed bracket for VALUES.

People are also looking for solutions to the problem: php - Symfony2 multiple select tags from one entity

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.