PHP Database connection methods

There are 3 ways to get data from the database.

1 Using Procedural Mysqli statement

$con = mysqli_connect("localhost","my_user","my_password","my_db");

2. Object-oriented Mysqli statement

$mysqli = new mysqli("localhost", "username", "password", "databaseName");

Using prepare & bind & execute methods in Object-oriented Mysqli with positional parameters. This is not PDO but looks like PDO.

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
// here s is string & i is integer
$stmt->execute();

WONDERING – Using prepare & bind & execute methods in Mysqli with named parameters – MYSQLi doesn’t support named parameters. Check Google why?

3. Using simple PDO in Mysql without any prepare, bind & execute method with either a positional parameter or named parameter.

$dsn = 'mysql:hostname=' . $hostname . ';dbname=' . $dbname;
$pdo = new PDO($dsn, $username, $password);

With try..catch block

$dsn = "mysql:host=localhost;dbname=2024php";
        try {
            $pdo = new pdo($dsn, 'root', '');
        } catch (PDOException $e) {
            echo $e->getMessage();
        }


$stmt = $pdo->query('SELECT * FROM posts');
while ($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
    print_r($row);
}

The best practice is to use a named parameter in the PDO class.

$pdo->prepare('SELECT * FROM posts WHERE id=?');
$stmt->bindValue(1, 2);
$stmt->execute();

OR

$pdo->prepare('SELECT * FROM posts WHERE id=:id');
$stmt->bindValue(':id', 2);
$stmt->execute();