CODEWITHSUNDEEP
phpmysql
Nigel Charles
Nigel Charles

Reputation: 23

What is the proper way to insert data into database?

From php I am Trying to insert an array using INSERT query. I am getting the error:

Check db Connection Column count doesn't match value count at row 1.

I am new to doing this type of insert and new to using implode. Please Help with some type of solution. My table columns are 

IaasForm_id (PK), From_id(FK), roleApp, os, vCPU, Memory, val, Performance, HighWrite, fwconfig, transIn, transOut, bkUp

and sql

$sql = "INSERT INTO FORM(From_id, roleApp, os, vCPU, Memory, val, 
        Performance, HighWrite, fwconfig, transIn, transOut, bkUp) 
        VALUES ((SELECT MAX(Form_id) FROM FORM ), '" . implode("','", $array[$i]) . "','" . $hw . "','" . $_POST[ 'fwconfig' ] . "','" . $_POST[ 'transIn' ] . "','" . $_POST[ 'transOut' ] . "','" . $_POST[ 'bkUp' ] . "')";

Upvotes: 0

Views: 124

Answers (1)

unclexo
unclexo

Reputation: 3941

You should never insert data into database this way. It was very old inserting-data fashion. Now there is a elegant way by which you can do sql jobs very efficiently. And best practices are always granted by all and used broadly.

See a very simple example how you should do this:

<?php

$host = "localhost";
$username = "your_db_username";
$password = "your_db_password";
$dbname = "your_db_name";

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    // Sets the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepares sql with PDO's PDO::prepare() method
    $sql = "INSERT INTO FORM(From_id, roleApp, os, vCPU, Memory, val, Performance, HighWrite, fwconfig, transIn, transOut, bkUp)
VALUES ((SELECT MAX(Form_id) FROM FORM), :roleApp, :os, :vCPU, :Memory, :val, :Performance, :HighWrite, :fwconfig, :transIn, :transOut, :bkUp)";
    $stmt = $conn->prepare($sql);

    // Prepares data
    $data = [
      ':roleApp' => 'value',
      ':os' => 'value',
      ':vCPU' => 'value',
      ':Memory' => 'value',
      ':val' => 'value',
      ':Performance' => 'value',
      ':HighWrite' => 'value',
      ':fwconfig' => 'value',
      ':transIn' => 'value',
      ':transOut' => 'value',
      ':bkUp' => 'value',
    ];

    // Inserts row into table
    $stmt->execute($data);

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

I hope this would help you understand. Happy coding!

Upvotes: 1

Related Questions