PHP Update Prepared Statement

PHP UPDATE prepared statement

$stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?");
/* BK: always check whether the prepare() succeeded */
if ($stmt === false) {
  trigger_error($this->mysqli->error, E_USER_ERROR);
  return;
}
$id = 1;
/* Bind our params */
/* BK: variables must be bound in the same order as the params in your SQL.
 * Some people prefer PDO because it supports named parameter. */
$stmt->bind_param('si', $content, $id);

/* Set our params */
/* BK: No need to use escaping when using parameters, in fact, you must not, 
 * because you'll get literal '\' characters in your content. */
$content = $_POST['content'] ?: '';

/* Execute the prepared Statement */
$status = $stmt->execute();
/* BK: always check whether the execute() succeeded */
if ($status === false) {
  trigger_error($stmt->error, E_USER_ERROR);
}
printf("%d Row inserted.\n", $stmt->affected_rows);

Re your questions:

I get a message from my script saying 0 Rows Inserted

This is because you reversed the order of parameters when you bound them. So you're searching the id column for the numeric value of your $content, which is probably interpreted as 0. So the UPDATE's WHERE clause matches zero rows.

do I need to declare all the fields or is it ok to just update one field??

It's okay to set just one column in an UPDATE statement. Other columns will not be changed.

How to UPDATE in database using mysqli prepared statement?

For procedural way

$query = "UPDATE `crew_info` SET `updated_photo` = ? WHERE `id` = ?";
$stmt = mysqli_prepare($conn, $query);
// you should use i instead of s for id
mysqli_stmt_bind_param($stmt, 'si', $imageData, $_GET['id']);
mysqli_stmt_execute($stmt);

Try this out in object-oriented style

$query = "UPDATE `crew_info` SET `updated_photo` = ? WHERE `id` = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param("si", $imageData, $_GET['id']);
$stmt->execute();

PHP Update Prepared Statement Issue

You still have a bit of a mix of PDO and MySQLi although only in your call to bindParam now, which you are calling as if it was MySQLi::bind_param. Also in your last edit the query string got messed up with the addition of Values=? I'm not sure why you did that? Anyway, this should do what you want:

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("UPDATE test SET title=:title WHERE id=:id");
    $stmt->bindParam(':title', $title);  
    $stmt->bindParam(':id', $id);

    // Update a row
    $title = $_POST['title'];
    $stmt->execute();
    echo "Row updated";
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

Update with mySQLi Prepared Statement

I figured it out, I finally found an example that works although I had to tweak some parts. The code below will grab the id passed from the url, do a prepared statement to update the selected id, and then redirect to a url location. The only thing missing is a Limit statement which I haven't figured out how to make work.

The code will also work to delete will be nearly identical with a few minor tweaks.

<?php
     // check if the 'id' variable is set in URL, and check that it is valid
     if (isset($_GET['id']) && is_numeric($_GET['id']))

     // get id value
     $id = $_GET['id'];

    $results = $id;

    $firstname  = $_POST['firstname']; 
    $lastname   = $_POST['lastname'];
    $phonenumber    = $_POST['phonenumber'];    
    $city       = $_POST['city'];
    $state      = $_POST['state'];
    $zipcode    = $_POST['zipcode'];
    $dob        = $_POST['dob'];
    $doi        = $_POST['doi'];
    $adjustername   = $_POST['adjustername'];
    $claimrefnumber = $_POST['claimrefnumber'];
    $providernature = $_POST['providernature'];
    $created    = $_POST['created'];
    $language   = $_POST['language'];
    $client     = $_POST['client'];
    $amountauthorized = $_POST['amountauthorized'];
    $active     = $_POST['active'];
    $invoiceformat  = $_POST['invoiceformat'];

   $connection = new mysqli("localhost", "some directory", "some password", "some user");
   $statement = $connection->prepare("update table set firstname = ?, lastname = ?, phonenumber = ?, city = ?, state = ?, zipcode = ?, dob = ?, doi = ?, adjustername = ?, claimrefnumber = ?, providernature = ?, created = ?,language = ?, client = ?, amountauthorized = ?, active = ?, invoiceformat = ? where id = ?");
   $statement->bind_param("sssssssssssssssssi", $firstname, $lastname, $phonenumber, $city, $state, $zipcode, $dob, $doi, $adjustername, $claimrefnumber, $providernature, $created, $language, $client, $amountauthorized, $active, $invoiceformat, $id);

   $statement->execute();
    if($statement->execute()){
       header("some location");
    }else{
        die('Error : ('. $mysqli->errno .') '. $mysqli->error);
    }
    $statement->close();
?>

php update query using prepared statement not working

Add $stmt->close(); before your next mysqli interaction.

if(sendMail($email,$verify_key)) {  // send mail first then update DB
    $stmt->close();
    $query = " UPDATE `signup` SET `name`=?,`password`=?,`verify_key`=? WHERE `email`=? ";

or you might just always want to execute the close after you're finished with your previous mysql interaction.

e.g.:

$query = " SELECT active FROM signup WHERE email=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s",$email);
$stmt->execute();
$stmt->bind_result($dbactive);
$stmt->close();

and remove the $stmt->close(); from the if. I've never seen official documentation on this but I've seen other threads were this was an underlying issue. The manual page itself actual almost states the opposite:

So, while explicitly closing open connections and freeing result sets is optional, doing so is recommended.

that is optional is false (... in my experience, I also primarily use PDO).

How to prepare statement for update query?

An UPDATE works the same as an insert or select. Just replace all the variables with ?.

$sql = "UPDATE Applicant SET phone_number=?, street_name=?, city=?, county=?, zip_code=?, day_date=?, month_date=?, year_date=? WHERE account_id=?";

$stmt = $db_usag->prepare($sql);

// This assumes the date and account_id parameters are integers `d` and the rest are strings `s`
// So that's 5 consecutive string params and then 4 integer params

$stmt->bind_param('sssssdddd', $phone_number, $street_name, $city, $county, $zip_code, $day_date, $month_date, $year_date, $account_id);
$stmt->execute();

if ($stmt->error) {
  echo "FAILURE!!! " . $stmt->error;
}
else echo "Updated {$stmt->affected_rows} rows";

$stmt->close();

PHP Prepared Statement insert or update If row exists

Thanks @RamRaider for the hint and suggestion. I'm posting the solution that worked for me, if in case anyone would require an instant reference.

Few edits that were done to MySql column prior to change in my original PHP code were that I altered my column to UNIQUE.

<?php

include'../db.php';

$exercise_days=$_POST['exercise_days'];
$id=$_POST['id'];

if ($exercise_days!='' && is_numeric($exercise_days))  
{
        $query=$db->prepare("INSERT INTO exercisetable (`id`, `exercise_days`) VALUES (?,?) ON DUPLICATE KEY UPDATE exercise_days =?");
        $query->bind_param('sss', $id, $exercise_days, $exercise_days);
        $query->execute();
        if($query){
        $message='Exercise Days Inserted';
        $success='Success';
        echo json_encode(array('message'=>$message,'success'=>$success));
        }
        $query->close();
}

else
{
    $message='Excercise Days Can Only Be Numbers';
    $success='Error';
    echo json_encode(array('message'=>$message,'success'=>$success));
} 
 ?>

---

Related Topics