Exact PHP Code to Insert Data into MySql Database

How to Safely and Securely Insert Data into a Database Using MySql in a Way the Eliminates MySql Inject Attacks and PHP Code that is the Shortest Most Efficient Code for Inserting Data into MySql Database

Home Short:

Inserting data into a database via mysql is fairly uncomplicated however the most important thing to keep in mind is security. Improperly inserting data via php mysql can result in a mysql injected attack where your database and perhaps your entire website becomes vulnerable and destroyed. With this in mind you want to regularly back up your database and store the backup offsite.

Inserting a new column into a database via php occurs with 3 steps.

Connect to the database and the table where you wish to insert new data.
Insert the new data to the database.
Closing the connection.

To connect to your database via php:

The second step is simplified via 2 function calls and 2 lines of code which are calls to these 2 php function.

Creating the new row for your database via php:

/* -----------------------------------------------------------------------------------------------
   Insert new entry into database

[$dbConn] active database connection
    [$tablename] the name of the table to insert the new database entry
    [$arrUserData] associate array (columnName => valueToInsertInColumn)

example usage:

$userData = 
    [
      "firstname" => "Henry",
      "age" => 30,
      "balance" => 100.50,
      "profile_image" => null  // Assuming this is a BLOB or similar
    ];

[$success, $affectedRows, $newRowId, $error] = insertIntoDatabase($dbConn, "users", $userData);
 --------------------------------------------------------------------------------------------------

function insertIntoDatabase($dbConn, $tablename, $arrUserData) 
{
    # Generate placeholders for the prepared statement
    $placeholders = rtrim(str_repeat('?, ', count($arrUserData)), ', ');
    
    # Generate comma-separated list of column names
    $columns = implode(', ', array_keys($arrUserData));
    
    # Construct the SQL query for insertion
    $sql = "INSERT IGNORE INTO $tablename ($columns) VALUES ($placeholders)";
    
    # Prepare the SQL statement
    $stmt = mysqli_prepare($dbConn, $sql);

if ($stmt === false) 
    {
        # handle error if statement preparation fails
        return [false, 0, 0, "Error preparing statement: " . mysqli_error($dbConn)];
    }

# automatically determine the type string for binding
    $types = '';
    $values = [];

foreach ($arrUserData as $value) 
    {
        if (is_int($value)) {
            $types .= 'i'; # Integer
        } elseif (is_float($value)) {
            $types .= 'd'; # Double
        } elseif (is_string($value)) {
            $types .= 's'; # String
        } else {
            $types .= 'b'; # Blob or other types
        }

$values[] = $value;
    }

# Bind parameters to the prepared statement
    $bindResult = mysqli_stmt_bind_param($stmt, $types, ...$values);

if ($bindResult === false) 
    {
        # Handle error if binding fails
        return [false, 0, 0, "Error binding parameters: " . mysqli_stmt_error($stmt)];
    }

# Execute the prepared statement to insert data into the database
    $success = mysqli_stmt_execute($stmt);

if ($success === false) 
    {
        # Handle error if execution fails
        return [false, 0, 0, "Error executing statement: " . mysqli_stmt_error($stmt)];
    }

# Get the number of affected rows and the last inserted ID
    $affectedRows = mysqli_stmt_affected_rows($stmt);
    $newRowId = mysqli_insert_id($dbConn);

# Close the statement
    mysqli_stmt_close($stmt);

# Return success status, affected rows, new row ID, and no error message
    return [$success, $affectedRows, $newRowId, null];
}

Closing the connection to your database via php:

Use the code struction above to put new data into a database table and inserting new rows into your database using php will be smooth, safe and efficient.

source code home