PHP Making an easy way for MySQL inserts

A MySQL insert in PHP can be easily done however they can be long and tedious. Frustrating to essentially make a table and then repeat it again with an insert query in the code whilst ensuring your variables match up with the correct column.

An example table from here

CREATE TABLE IF NOT EXISTS test (
    task_id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    start_date DATE,
    due_date DATE,
    status TINYINT NOT NULL,
    priority TINYINT NOT NULL,
    description TEXT,
    PRIMARY KEY (task_id)
)  ENGINE=INNODB;

There is only 7 columns in this table and one is an auto increment INT so I don’t need to insert into that column.

If I needed to do a MySQL insert with PHP

$MYSQL_insert = "INSERT INTO `test` (`title`, `start_date`, `due_date`, `status`, `priority`, `description`) VALUES ('$title', '$start', '$due', '$status', '$priority', '$desc')";

Thats easy and small but had I need to build an insert query on a table with 20 or more columns even with PHPStorm auto complete and MySQL sync it would be a brain-dead task.

A way to almost automatically compile this insert query can be done as follows:

Using MySQL show columns we can return every column in the table by specifying the Field had i need the type of column id use Type and so forth. Using this in PHP with loops to ensure the last column and variable don’t have a , I can echo out the statement:

$connect = mysqli_connect("127.0.0.1", "root", "", "dev");
$table = 'test';
$sql = "SHOW COLUMNS FROM $table";
$result = mysqli_query($connect, $sql);
echo '$MYSQL_insert = "INSERT INTO `' . $table . '` (';
while ($row1 = mysqli_fetch_assoc($result)) {
    $db_array[] = $row1;
}
$length = count($db_array);
$i = 0;
$result = mysqli_query($connect, $sql);
while ($row2 = mysqli_fetch_array($result)) {
    $col = $row2['Field'];
    if ($i == $length - 1) {
        echo "`$col`";
    } else {
        echo "`$col`, ";
    }
    $i++;
}
echo ') VALUES (';
$i = 0;
$result = mysqli_query($connect, $sql);
while ($row3 = mysqli_fetch_array($result)) {
    $col = $row3['Field'];
    if ($i == $length - 1) {
        echo "'$" . $col . "'";
    } else {
        echo "'$" . $col . "', ";
    }
    $i++;
}
echo ')";';

will echo out

$MYSQL_insert = "INSERT INTO `test` (`task_id`, `title`, `start_date`, `due_date`, `status`, `priority`, `description`) VALUES ('$task_id', '$title', '$start_date', '$due_date', '$status', '$priority', '$description')";

obviously i would remove parts I don’t need (task_id) and rename variables were needed, as variable renaming and unassigned detection is quick with PHPStorm.