Executing SQL statements with PDO

There are two methods to execute SQL statements in PDO:
   1. exec() is used to process statements that do not return SQL results, such as insert update delete create, etc., but it will return the number of affected rows
If you are executing an insert statement, you can use the lastinsertid() method to get the last auto insert ID. You can also use foreach to get results (but not often)
    2. query() is used to process statements with data return, such as select desc show. This method returns the object of PDOStatement class. In this class, get the result through the method,
                     .

PDO inserts data code into database:

 <?php

    try {
        //create object
        $pdo = new PDO("mysql:host=localhost;dbname=msg", "root", "***");
        //Set the mode of wrong use exception
        $pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $e) {
        echo "Database connection failed:".$e->getMessage();
        exit;
    }

    $pdo->exec("set names utf8");
    try {
        //Using methods in PDO to execute statements
        $affected_rows = $pdo -> exec("insert into users(name, pass, sex, age, email) values('aaa', 'bbb', 'nn', '11', 'aaa@b.com')");

        echo $affected_rows."<br>";
        echo $pdo->lastinsertid();

    }catch(PDOException $e) {
        echo "Error:".$e->getMessage();
    }
?>

When executing a database insert statement, if you use query to query, the program will throw an exception when it executes echo $affected rows. "< br >"; because using query can successfully execute the insert statement, but the return value is an object.

Query database with PDO

<?php
    try {
        //create object
        $pdo = new PDO("mysql:host=localhost;dbname=msgBoard", "root", "***");
        //Set the mode of wrong use exception
        $pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $e) {
        echo "Database connection failed:".$e->getMessage();
        exit;
    }

    $pdo->exec("set names utf8");
    try {
        //Using methods in PDO to execute statements
        $sqlresults = $pdo->query("select * from msg");

        //In this way, although data can be printed out, it is recommended to use PDO's own method
        foreach($sqlresults as $arr){
            print_r($arr);
            echo "<br />";
        }

    }catch(PDOException $e) {
        echo "Error:".$e->getMessage();
    }
?>

Keywords: PDO Database SQL PHP

Added by flOid on Sat, 04 Apr 2020 11:29:40 +0300