使用OOP PHP和PDO将数据插入到mysql中

Question

I am new to PHP and trying to learn OOP PHP.

I am creating a registration form (a simple one) to learn OOP PHP, when I submit data the success message shows up but data is not inserted in data base.

Below is my code:

connection.php

<?php

class DBConnection extends PDO
{
    public function __construct()
    {
        $host='mysql:host=localhost;dbname=OOP';
        $user='root';
        $password='';
        parent::__construct($host,$user,$password);
        $this->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // always disable emulated prepared statement when using the MySQL driver
        $this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }
}

?>

Index.php

<?php
session_start();
/*include 'classes.php';
//$dbHandle = new DBConnection();
$ins=new basic_operation();
$ins->Insert_Data();*/



?>


    <div class="col-lg-12">

        <div class="col-lg-1"></div>

        <div class="col-lg-7">
            <form action="user_data.php" method="post" >
                <input type="text" name="username" id="username" class="form-control"><br>
                <input type="email" name="email" id="email" class="form-control"><br>
                <input type="password" name="password" class="form-control" id="password"><br>
                <input type="submit" name="submit" id="submit" value="submit">
            </form>
                    </div>

                </div>
<div>
    <?php if (isset($_SESSION['insert']))
    {
        echo $_SESSION['insert'];
        unset($_SESSION['insert']);
    }

    ?></div>

        </div>
    </div>

User_data.php

<?php
session_start();
include 'classes.php';

    $insert=new basic_operation();
    $usr=$insert->Insert_Data();
    //return $insert;
    $_SESSION['insert']='data inserted successfuly';
    header('location:index.php');

?>

Classes.php

<?php
include 'connection.php';

class basic_operation
{

    public function Insert_Data()
    {
        if (isset($_POST['submit'])) {
            $user = $_POST['username'];
            $email = $_POST['email'];
            $pass = $_POST['password'];
            $smt = new DBConnection();
            $qry = $smt->prepare("insert into student(User_Name,Email,Password) VALUES ('" . $user . "','" . $email . "','" . $pass . "')");
            $qry->execute();

        }
    }
}
?>

If any one knows how to perform basic CRUD (insert, update, delete, select) operation in PHP using OOP PHP and PDO Then please provide the link, source, Example, so i can learn from it

I searched on the internet but bearly able to find the connection code... Looking for a descriptive answer, as I think this question will also help a lot of people who try/want to learn OOP PHP.

Any help will appreciated.

Answer 1

Use try-catch in your connection script to get errors.

try {
    $pdo = new PDO(
    'mysql:host=localhost;port=3306;dbname=your_db;charset=utf8mb4',
    'user',
    'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4")
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Database connection has failed. Contact system administrator to resolve this issue!<br>';
    $e->getMessage();
    die();
}

Enable error reporting in php (add this on top of your php script).

error_reporting(E_ALL);

Since you want to use prepared statements then you will have to bind your parameters in order to get escaped properly.

        $qry = $smt->prepare("INSERT INTO `student` (`User_Name`,`Email`,`Password`) 
                              VALUES (:user,:email,:pass);");
        $qry->bindParam(':user', $user, PDO::PARAM_STR, 255);
        $qry->bindParam(':email', $email, PDO::PARAM_STR, 255);
        $qry->bindParam(':pass', $pass, PDO::PARAM_STR, 255);
        $qry->execute();

Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

source: Description of PDO::quote in php.net

And last but not least... Always use backticks “`” in your MySQL queries to properly quote MySQL Keywords and Reserved Words such as PASSWORD. You use it in your query as a field but you don't quote it.