Confirmation of an UPDATE to table

[phppup]

The following code snippet will NOT provide what I'm interested in determining

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE lastname='Doe-Smith'";
if (mysqli_query($conn, $sql)) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . mysqli_error($conn);
}

For argument sake, let's presume that Ms Doe-Smith's marital status has changed and she is returning to using her maiden name only. The records of her 5 children will also be effected (although not all used Mr. Smith's surname).

The snippet's success/error message seems to fire based on the validity of $conn and $sql.

If no records are updated (because there are no instances of Doe-Smith), the success message displays anyway because nothing went wrong with the process.

How can I get a confirmation of whether an actual record was changed? Or how many? Or row numbers?

(I've considered running a SELECT before the UPDATE, but thought that might create an unnecessary load factor.)

Thanks for reading.

[Barand]

Check the number of rows affected by the query.

https://www.php.net/manual/en/mysqli.affected-rows.php

[phppup]

@Barand  AWESOME, as usual.

Thanks!!

Can you give me a template for implementing the UPDATE

$data = "Doe";

$sql = "UPDATE MyGuests SET lastname=$data WHERE lastname='Doe-Smith'";
if (mysqli_query($conn, $sql)) {
  echo "Record updated successfully";

with a prepared statement in procedural method, please.

[maxxd]

https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Honestly, if it's a possibility at this stage in your project I recommend switching to PDO. It's a much easier to deal with. And while you're at it, go ahead and look at OOP standards - they really do make a system easier to reason about IMO.

[phppup]

@maxxd Thanks, I think I've got the prepared statement accomplished.

I "understand" procedural.

The "shorthand" of PDO gets me confused and lost.

I have the same problem moving from vanilla JavaScript to jQuery.

If you have a remedy (or therapy.. LOL) that will help me overcome these issues, please share.

And please keep this confidential. Just between us.  I don't want my personal information on the internet.  *wink*

[mac_gyver]

converting an old query that has variables being put directly into it to to use prepared queries, with the much simpler and better designed PDO extension, is fairly straightforward -

1. remove, and keep for later, any php variables that are inside the sql query statement. note: any wild-card characters in a LIKE comparison are supplied as part of the data value not as part of the sql query statement.
2. remove any quotes or {} that were around the php variable and any concatenation dots/extra quotes that were used to get the php variable into the sql query statement.
3. put a simple ? prepared query place-holder into the sql query statement for each value.
4. call the PDO prepare() method for the sql query statement. this returns a PDOStatement object.
5. call the PDOStatement execute([...]) method with an array of the variables you removed in step #1.
6. for a query that returns a result set, fetch the data from the query. see the PDOStatement fetch() method when fetching a single row of data. the  PDOStatement fetchAll() method when fetching all the rows of data at once. and occasionally the PDOStatement fetchColum() method when fetching a single column from a single row of data. forget about any num rows function/method/property. just fetch then test if/how many rows of data there are.
7. for a query that doesn't return a result set, you can use the PDO lastInsertId() method and the PDOStatement rowCount() method with an insert/update/delete query to get the last insert id and the number of affected rows.
8. you should be using exceptions for database statement errors (this is the default setting now in php8+). for an insert/update query that could result in duplicate data (the appropriate column(s) need to be defined as unique indexes in the database table), you will need to catch and handle database exceptions from the insert/update query, test if the error number is for a duplicate index error, and setup a message for the user letting them know what was wrong with the data that they submitted. for all other query errors, simply rethrow the exception, and for all other type of queries, simply do nothing in your code, and let php catch and handle any database exception, where php will use its error related settings to control what happens with the actual error information via an uncaught exception error (database errors will 'automatically' get displayed/logged the same as php errors.)

[maxxd]

Making the transition from procedural to object-oriented can be daunting at first, but it's worth it in the long run. The code (when written correctly) ends up being much easier to parse and reason about and - as mac_gyver alluded to with the comment about exceptions - much safer and more stable. Not only that it's the way the core is headed, and most if not all frameworks are object-oriented by this point.

There are a ton of resources online, and of course you've got a great resource in this forum.

[phppup]

5 hours ago, maxxd said:

and of course you've got a great resource in this forum

I couldn't agree more!

But, @maxxd , are you encouraging me to post even more questions? LOL

I've certainly learned and overcome PLENTY of obstacles here. But moving deeper into the pool may be a step further than I'm ready to "bind" before being fully "prepared" (see what I did there 🙂 ).

TBH, I nearly fell overboard when mySQLi became mandatory

But hey, ya never know.

Thanks for the support.

[gizmola]

My first experience with OOP was ages ago, but the general idea of what objects are stayed with me, and made Oop much easier for me to understand.

Certainly with PHP you are used to working with PHP's complex data structure: the array.

I'm guessing you are comfortable with the idea that an array exists in program memory, and the data in the array can be manipulated.  When using database api's they typically have methods that return an array with the data from the database query filled in.

You are also familiar with functions.  You have written functions to do various things, and are familiar with calling functions that take parameters and can return a result.

An object is simply a combination of the two:  the data structure which includes some combination of data types, and functions you have defined which are typically pre-wired to be able to work with the variables you set up in the class.  A class is just the definition of the variables and the functions (typically called methods) that are designed to work with the class variables.

A class definition is simply the blueprint for creating an object that has this binding of data and functions.  Once you think about it in that way, I think it helps to demystify Oop.  

An object is just some data + functions (methods).  It's a data structure that combines both together.  The functions aren't actually copied in every object -- they exist in the code segment of the program/runtime environment, and objects basically just have a table that references the function code, but as an abstraction, you can just think of the object as being a complex variable type with functions bound into it.

When dealing with functions, you have to contend with the ways variables can be passed to a function, namely pass by reference vs pass by value.  With Objects, they are always passed by reference, so changing an object variable or executing a method that alters object variables will always change the object.  Compare this to a function you write, where you pass in an array, change the array inside the function.

For example:

<?php

$a = ['red', 'green'];

function addBlue($a) {
    $a[] = 'blue';
    return $a;
}

$b = addBlue($a);

var_dump($b);
var_dump($a);

You should know that $a is passed by Value here, so when the function completes, the original $a array is unchanged.

array(3) {
  [0]=>
  string(3) "red"
  [1]=>
  string(5) "green"
  [2]=>
  string(4) "blue"
}
array(2) {
  [0]=>
  string(3) "red"
  [1]=>
  string(5) "green"
}

In order to actually change the original $a array, you would need to declare that function addBlue's $a parameter is passed by reference:

function addBlue(&$a) {...}

 

With objects, you don't need to do this, as all objects are assumed to be passed by reference.

I won't complicate this with a discussion of static variable or methods, as they add some wrinkles, but just purely as a user of a database api, this binding of variables/methods into one thing offers a lot of utility, since you don't have to keep track of all the different variables you require or produce.  In general, because the objects already contain associated variables, there are less things to keep track of, and the functions designed to work with an object of that class are already a part of it. You aren't constantly having to figure out which function(s) you need to call and passing the variables that have the data you need into them.

Your code tends to be simpler, and easier to maintain, even as the complexity of the application increases. 

Level one of Oop is just getting comfortable using classes and objects that other people provided.  You don't need any sophisticated preparation or study of oop design patterns to use all the classes that are baked into libraries and extensions.