CODEWITHSUNDEEP
phpmysqli
Reham Fahmy
Reham Fahmy

Reputation: 1128

how to use update statement using mysqli

I want to show up an error when there is no results upon updating an statement so i've tried using this code with none existed ID.

But this code is always giving me success! however i'm trying to test it with wrong id that not exist! can you show me what is wrong with this code.

<?PHP
include("config.php"); // For connection

$product_name = '52 inch TV';
$product_code = '9879798';
$find_id = 188; // id not exist should gives Error

$statement = $mysqli->prepare("UPDATE products SET product_name=?, product_code=? WHERE ID=?");
$statement->bind_param('ssi', $product_name, $product_code, $find_id);
$results =  $statement->execute();

if($results){
    print 'Success! record updated';
}else{
    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
}
?>

Upvotes: 0

Views: 63

Answers (4)

Panayiotis Georgiou
Panayiotis Georgiou

Reputation: 1253

What I typically do is something like this.

Also, you need to make sure you have a field or something that is unique to this record. Basically, it will always INSERT the way it's written, since we're just checking one value (birthday)

Here's an example
$conn = new mysqli('localhost', 'username', 'pwd', 'db');

        // check connection
        if (mysqli_connect_errno()) {
          exit('Connect failed: '. mysqli_connect_error());
        }          
                // check to see if the value you are entering is already there      
                $result = $conn->query("SELECT * FROM birthday WHERE name='Joe'");
                if ($result->num_rows > 0){ 
                    // this person already has a b-day saved, update it
                    $conn->query("UPDATE birthday SET birthday = '$birthday' WHERE name = 'Joe'");
                }else{
                    // this person is not in the DB, create a new ecord
                    $conn->query("INSERT INTO `birthday` (`birthday`,`name`) VALUES ('$birthday','Joe')");
                }

Check the additional example here

Upvotes: 1

RiggsFolly
RiggsFolly

Reputation: 94652

The probelm with your current logic is that the query you issue does not generate an error it just does not update any rows.

If you want to ensure an error try making the query so it wont compile at all.

Also as these statement return objects OR false it is better to test specifically for false using the === rather than a if($var)

<?php
include("config.php"); // For connection

$product_name = '52 inch TV';
$product_code = '9879798';
$find_id = 188; // id not exist should gives Error

$statement = $mysqli->prepare("UPDATE products 
                                  SET product_name=?, 
                                      product_code = ? 
                                WHERE IDXX = ?");    // <-- generate error

$statement->bind_param('ssi', $product_name, $product_code, $find_id);
$results =  $statement->execute();

if($results === false){
    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
}else{
    print 'Success! record updated';
}
?>

Otherwise if you actually want to know if the query actually amended something you must check for the affected row count

<?php
include("config.php"); // For connection

$product_name = '52 inch TV';
$product_code = '9879798';
$find_id = 188; // id not exist should gives Error

$statement = $mysqli->prepare("UPDATE products 
                                  SET product_name=?, 
                                      product_code = ? 
                                WHERE ID = ?");

$statement->bind_param('ssi', $product_name, $product_code, $find_id);
$results =  $statement->execute();


if($results === false){
    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
}else{
    print 'Success! record updated';
    echo sprintf('Query changed %d rows', $mysqli->affected_rows);
}

?>

Upvotes: 1

Brijal Savaliya
Brijal Savaliya

Reputation: 1091

Please use below code 

if($statement->affected_rows > 0){
    print 'Success! record updated';
}else{
    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
}

Upvotes: 1

Aju John
Aju John

Reputation: 2234

Use mysqli_affected_rows after $results = $statement->execute();. This will give you the count of updated rows. So you can check accordingly

Upvotes: 1

Related Questions