How to Insert into MySQL Using a Prepared Statement with PHP

How to insert into MySQL using a prepared statement with PHP

###File sample.html

<form action="sample.php" method="POST">
    <input name="sample" type="text">
    <input name="submit" type="submit" value="Submit">
</form>

###File sample.php

<?php

if (isset($_POST['submit'])) {
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli('localhost', 'user', 'password', 'mysampledb');

    $stmt = $mysqli->prepare("INSERT INTO SampleTable VALUES (?)");
    $stmt->bind_param('s', $sample);   // Bind $sample to the parameter

    $sample = $_POST['sample'] ?? '';

    /* Execute prepared statement */
    $stmt->execute();

    printf("%d Row inserted.\n", $stmt->affected_rows);
}

This is a very basic example. Many PHP developers today are turning to PDO. Mysqli isn’t obsolete, but PDO is much easier, IMHO.

PHP Insert Prepared Statement

Found the answer for this issue.

<?php
 $servername = "mysql";
 $username = "root";
 $password = "passwrd";
 $dbname = "dbname";

 try {
 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username,
    $password);
 // set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare sql and bind parameters
$stmt = $conn->prepare("INSERT INTO tablename (name, email, commtype, 
comment, confirm) 
VALUES (:name, :email, :commtype, :comment, :confirm)");
  $stmt->bindParam(':name', $name);
  $stmt->bindParam(':email', $email);
  $stmt->bindParam(':commtype', $commtype);
  $stmt->bindParam(':comment', $comment);
  $stmt->bindParam(':confirm', $confirm);

// insert a row
   $name = $_POST['name'];
   $email = $_POST['email'];
   $commtype = $_POST['commtype'];
   $comment = $_POST['comment'];
   $confirm = $_POST['confirm'];
   $stmt->execute();

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

Inserting (DATE(now)) in MySql using prepared statements

Solution 1: Database Side (recommended choice)

You may use curdate() to do the job which is actually available in MySQL, in that case simply change these lines :

$sql = $con->prepare("INSERT into free_ad(user_id, user_name, user_email,title,timenow) values 
     (?,?,?,?,(DATE(now)))");
$sql->bind_param("isss",$user_id,$user_name,$user_email,$get_text1);

to

 $sql = $con->prepare("INSERT into free_ad(user_id, user_name, user_email,title,timenow) values 
     (?,?,?,?,curdate())");
      $sql->bind_param("isss",$user_id,$user_name,$user_email,$get_text1);

Solution 2: Application Side

You may also use the application side date (the date of the web server which runs the PHP), in that case you can use the date() function. Such as date('Y-m-d').
Hence , try changing these 2 lines:

$sql = $con->prepare("INSERT into free_ad(user_id, user_name, user_email,title,timenow) values 
     (?,?,?,?,(DATE(now)))");
$sql->bind_param("isss",$user_id,$user_name,$user_email,$get_text1);

to

$currentdate=date('Y-m-d');
$sql = $con->prepare("INSERT into free_ad(user_id, user_name, user_email,title,timenow) values 
     (?,?,?,?,?)");
$sql->bind_param("issss",$user_id,$user_name,$user_email,$get_text1, $currentdate);

In modern servers which have NTP enabled and running, the two time should be the same if they are using the same locale.

However, if NTP are not applied in both servers, the datetime data in the two servers can be different in that case you have to decide which one to use. (better use the solution 1). Solution 2 is added here just to show that in case there is a piece of data which is not available in database side, you can switch to use application side as a resort.

How to insert array result into mysql using prepared statement in PHP

Try this:

$test=array_map('trim',$_POST["test"]);
$test1=array_map('trim',$_POST["test1"]);

$stmt = $conn->prepare("INSERT INTO test(v1,v2) VALUES (?,?)");
$error=false;
foreach($test AS $key=>$value){
    if(! stmt->execute([$value,$test1[$key]])){
       $error=true;  
       break;
    }
}
if($error) // handle error

Running queries in loops like this is what prepared statements are for.

MYSQL insertion using PHP prepared Statement

// think you should do as follows, insert statement only allows where clause when select statement is used,

<?php
$ActivityDate = $_POST["ActivitytDate"];
$CoreSite = $_POST["CoreSite"];
$ActionAuditor = $_POST["ActionAuditor"];
$DCOSPOC = $_POST["DCOSPOC"];

if($stmt = $mysqli->prepare("Insert INTO DCO_Database (ActivityDate, CoreSite, ActionAuditor, DCOSPOC) values (?,?,?,?)");
{
$stmt->bind_param("ssss", $ActivityDate, $CoreSite, $ActionAuditor, $DCOSPOC);
$stmt->execute();
$stmt->close();
}
else{
echo ("Failed");
$mysqli->close();    
}
?>

How to use both SELECT and INSERT query on single php prepared statement?

You can't prepare multiple statements. That is why your prepare is failing and $stmt is a boolean (false). To fix it, remove the SELECT LAST_INSERT_ID() from your INSERT query and change the code to this, using insert_id to get the last insert id:

$insertRecord = "INSERT INTO ". $natureFlight ."(`typeFlight`, `dateTimeSubmit`, `emb`, `acType`, `reg`, `companyName`, `callSign`, `dateFlight`) VALUES (?,?,?,?,?,?,?,?)";
$stmt = $conn->prepare($insertRecord);
$stmt->bind_param("ssssssss", $typeFlight, $dateTimeSubmit, $emb, $acType, $reg, $companyName, $callSign, $dateFlight);
if (!$stmt) {
    $res['error'] = true;
    $res['message'] = $conn->error;
}
elseif ($stmt->execute()) {
    echo $conn->insert_id;
} 
else {
    $res['error'] = true;
    $res['message'] = $stmt->error;
}

Failing to insert data into database using prepared statement in PHP, no error reported

First, you have a typo for password (you have $_POST['passowrd']) and second, this is based on the example from the documentation:

# Prepare (use the OOP version of this library)
$query  =   $connection->prepare("INSERT INTO users (`name`, `email`, `password`) VALUES (?, ?, ?)");
# Bind parameters and spell "password" correctly
$query->bind_param('sss', $_POST['name'], $_POST['email'], $_POST['password']);
# Execute
$query->execute();
# See if the row was created and echo success
echo ($query->affected_rows > 0)? 'Success!' : 'Failed';

You should be using password_hash() (storing) and password_verify() (validating) or a bcrypt equivalent library (if your version of php doesn't have those native functions). When using these functions, make sure your password column has like 255 character length so not as to cut off the password hash.

php prepared statement (INSERT not working)

As you are using PDO not mysqli interface you need to use named parameters in SQL query like exlained here:
https://www.php.net/manual/en/pdo.prepare.php

Currently you are relying on bind_param which is a method on mysqli interface.

Based on PDO docs:

<?php
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();
?>

Additionally I would recommend to use bindValue to make it more explicit that you are setting some values to placeholders like this:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

---

Related Topics