Creating a database (MySQL PDO) in XAMPP and connect it with the php code

Question

I have written the code for login and registration form using Sublime3. I need to create a database in XAMPP using Mysql PDO and link it to the code so that the validation of login takes place through the database. Below is the code i have written for database. But it doesnt automatically creates a table when i sign up using a new username. The data entered during the signup are not stored in the database. Code written in phpmyadmin

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `middle_name`varchar(255),
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Code for database connection

<?php
    $dsn = 'mysql:host=localhost;dbname=ecs;';
    $user = 'root';
    $password = '';
    try
    {
        $dbh = new PDO($dsn, $user, $password);
    }
    catch (PDOException $e)
    {
        echo 'Connection failed: ' . $e->getMessage();
    }
?>

Code for register

<?php
    session_start();
    require_once("../includes/dbconfig.php");

    try{
        $uname = $_POST["username"];
        $pword = $_POST["password"];
        $first_name = $_POST["firstname"];
        $middle_name = $_POST["middlename"];
        $last_name = $_POST["lastname"];
        $user_type = $_POST["user_type"];

        $dbh = new PDO($dsn, $user, $password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $sql = "INSERT INTO users (username, password, first_name, middle_name, last_name, user_type) 
                VALUES (:username, :password, :first_name, :middle_name, :last_name, :user_type)";
        $stmt = $dbh->prepare($sql);
        $stmt->bindValue(':username', $uname);
        $stmt->bindValue(':password', $pword);
        $stmt->bindValue(':first_name', $first_name);
        $stmt->bindValue(':middle_name', $middle_name);
        $stmt->bindValue(':last_name', $last_name);
        $stmt->bindValue(':user_type', $user_type);
        $stmt->execute();

    } 
    catch (PDOException $e)
    {
        echo 'PDO Exception Caught.';
        echo 'Error with the database: <br />';
        echo 'SQL Query: ', $sql;
        echo 'Error: ' . $e->getMessage();
    }
?>

Can you tell me what I am doing wrong? Is it the code or some installation changes in XAMPP?

**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, May 02 '16 at 21:51
If you provide a `NOT NULL` column definition you must also provide a `DEFAULT` value for the column. — Jay Blanchard, May 02 '16 at 21:52

score 0 · Answer 1 · answered May 02 '16 at 22:00

0

I don't see user_type column in your table so I think that's what causing the problem. Can you create that column and try again or remove it from your PHP code? Also, please encrypt your user password as @Jay Blanchard mention before.

answered May 02 '16 at 22:00

KD Quality

26
3

Creating a database (MySQL PDO) in XAMPP and connect it with the php code

1 Answers1