Mr. Bipin S. Rupadiya

Part-2: Create a MySQL Database and a table using PDO.

In part two, I am going to create a setupdb.php file where I do following task.

1. Database connection in PHP and MySQL using PDO,
2. Create database in MySQL which name we define in config.php
3. Create table in MySQL using PDO
4. Insert sample records using prepared statements.



Setupdb.php file will be really helpful to create sample database to test our web application. It is also useful to those who does not have enough working knowledge to deal with database management .

setupdb.php

<?php

require("config.php");

try
{

$conn = new PDO("mysql:host=localhost", username, password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$conn->query("CREATE DATABASE IF NOT EXISTS ".mydb);
$conn->query("use ".mydb);

echo "Database [ ".mydb." ]  Successfully Connected";


$tbl="tbl";
// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS $tbl (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
phone VARCHAR(30),

)";

// use exec() because no results are returned
$conn->exec($sql);
echo "<br>Table [ $tbl ] created successfully";


// prepare sql and bind parameters
$stmt = $conn->prepare("INSERT INTO $tbl (name, email, phone, message)
VALUES (:name, :email, :phone, :message)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':phone', $phone);


// insert a row
$name = "bipin";
$email = "bipin@gmail.com";
$phone = "+919999999999";
$stmt->execute();

$last_id = $conn->lastInsertId();
echo "<br> Record [ $last_id ] created successfully" ;

// insert a row
$name = "hitesh";
$email = "hitesh@gmail.com";
$phone = "+919999999999";
$stmt->execute();

$last_id = $conn->lastInsertId();
echo "<br> Record [ $last_id ] created successfully" ;

// insert a row
$name = "jignesh";
$email = "jignesh@gmail.com";
$phone = "+919999999999";
$stmt->execute();

$last_id = $conn->lastInsertId();
echo "<br> Record [ $last_id ] created successfully" ;
    }
catch(PDOException $e)
    {
echo "Connection failed: " . $e->getMessage();
    }
$conn = null;
?>

This example is divided in multiple part.

Part-1: Configure and Setup the MySQL database connection using PDO.
Part-2: Create a MySQL Database and a table using PDO.
Part-3: Insert record using prepared statement in PDO.
Part-4: Display Records using prepared statement.
Part-5: Search records using AJAX and PDO in PHP.
Part-6: Update Record using prepared statement in PDO.
Part-7: Delete Record using prepared statement in PDO.