MYSQLI INSERT query

<?Php
require "config.php";// Database connection

$id=50;
$name = 'my_name';
$class='Three';
$mark=70;
$gender='male';
$query="INSERT INTO student (id,name,class,mark,gender) values($id,'$name','$class','$mark','$gender')";
if($connection->query($query)){
echo "<br>No of records inserted : ".$connection->affected_rows;
}else{
echo $connection->error;
}
?>

MySQLI database connection file

<?Php
require "config.php";// Database connection
//////////////////////////////
$id=50;
$name = 'my_name';
$class='Three';
$mark=70;
$gender='male';
$query="INSERT INTO student (id,name,class,mark,gender) values(?, ?,?,?,?)";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("issis", $id, $name, $class,$mark,$gender);
if($stmt->execute()){
echo "<br>No of records inserted : ".$connection->affected_rows;
}else{
echo $connection->error;
}
}else{
echo $connection->error;
}
?>

<?Php
require "config.php";// Database connection
//////////////////////////////
$name = 'my_name';
$class='Three';
$mark=70;
$gender='male';
$query="INSERT INTO student (name,class,mark,gender) values( ?,?,?,?)";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("ssis",  $name, $class,$mark,$gender);
if($stmt->execute()){
echo "<br>No of records inserted : ".$connection->affected_rows;
echo "<br>Insert ID : ".$connection->insert_id;

}else{
echo $connection->error;
}
}else{
echo $connection->error;
}
?>

Quick Add Without parameter binding

if($connection->query("insert into table_name (field_name) values('$field_value')")){
echo " Records added : ".$connection->affected_rows;
}else{
	echo " Records not added  ";
}

Inserting Data into a Student Table with MySQLi Prepared Statements and Try-Catch Block

• Database Connection: Establishes a connection to the MySQL database using the `$connection` object from the required `config.php` file.
• Prepared Statement: A prepared statement with placeholders (`?`) is used for inserting values into the `name`, `class`, `marks`, and `gender` columns.
• Binding Parameters: The `bind_param()` method binds the actual values to the placeholders. Here, `"sssi"` indicates that `name`, `class`, and `gender` are strings, while `marks` is an integer.
• Executing the Query: The query is executed using `execute()`. If successful, a success message is displayed.
• Affected Rows: After the query is executed, `affected_rows` displays the number of rows inserted (should be 1).
• Insert ID: If the table has an auto-increment ID, `insert_id` returns the ID of the last inserted record.
• Error Handling: The `try-catch` block is used to catch any `mysqli_sql_exception` and display an error message.

Code

<?php
require "config.php"; // Database connection having $connection

try {
    // Variables to be inserted
    $name = "John Doe";
    $class = "Three";
    $marks = 85;
    $gender = "Male";

    // Prepare the SQL INSERT query
    $query = "INSERT INTO student (name, class, mark, gender) VALUES (?, ?, ?, ?)";

    // Prepare the statement
    $stmt = $connection->prepare($query);
	if($stmt){
    // Bind parameters (s = string, i = integer)
    $stmt->bind_param("ssis", $name, $class, $marks, $gender);

    // Execute the statement
    if ($stmt->execute()) {
        echo "Record inserted successfully!";
        // Output affected rows
        echo "<br>No of records inserted: " . $connection->affected_rows;
        // Output the ID of the inserted record
        echo "<br>Insert ID: " . $connection->insert_id;
    } else {
        echo "Error: " . $stmt->error;
    }
	}
    // Close the statement
    $stmt->close();

} catch (mysqli_sql_exception $e) {
    // Handle any errors
    echo "Error: " . $e->getMessage();
}

// Close the connection
$connection->close();
?>

Output Explanation

• If the query is successful, the message "Record inserted successfully!" will be displayed.
• `affected_rows` will show how many records were inserted (typically 1).
• `insert_id` will display the auto-increment ID of the newly inserted record (if applicable).
• If any error occurs, the `catch` block will display the error message.

Here’s the procedural style equivalent of the above code, using MySQLi

try {
    // Variables to be inserted
    $name = "John Doe";
    $class = "Three";
    $marks = 85;
    $gender = "Male";

    // Prepare the SQL INSERT query
    $query = "INSERT INTO student (name, class, mark, gender) VALUES (?, ?, ?, ?)";

    // Prepare the statement
    $stmt = mysqli_prepare($connection, $query);
    if ($stmt) {
        // Bind parameters (s = string, i = integer)
        mysqli_stmt_bind_param($stmt, "ssis", $name, $class, $marks, $gender);

        // Execute the statement
        if (mysqli_stmt_execute($stmt)) {
            echo "Record inserted successfully!";
            // Output affected rows
            echo "<br>No of records inserted: " . mysqli_affected_rows($connection);
            // Output the ID of the inserted record
            echo "<br>Insert ID: " . mysqli_insert_id($connection);
        } else {
            echo "Error: " . mysqli_stmt_error($stmt);
        }

        // Close the statement
        mysqli_stmt_close($stmt);
    }
} catch (Exception $e) {
    // Handle any errors
    echo "Error: " . $e->getMessage();
}

Adding date to MySQL

$date = new DateTime($dt);
$dt=$date->format('Y-m-d');

$stmt->bind_param('s', $dt);

To add null to a date field

if(strlen($dt) >2){
$date = new DateTime($dt_approved);
$dt=$date->format('Y-m-d');
}else{
$dt=null;
}

MySQL DUMP of student table