MySQL Updates Will not Save in PHP

[Alans]

I have crated a form and the DML to update the data in a MySQL database.  I have confirmed the data is coming over from the form however it is not getting saved/committed to the database.   The user in the DB connection $db_elect has full privileges to the database.  I have tried all of these with no luck

  $db_elect->commit();

  $sql_commit = mysqli_query($db_elect ,"COMMIT");

  if ( !mysqli_commit($db_elect) )
   { $sql_commit = 'FAILED'; }

Here is the update statement.  When it is ran I get no errors in the error_log file

     $sql_result_update = mysqli_query( $db_elect
                                       ,"UPDATE campaign_positions
                                            SET  position_text  = '$lv_position_text'
                                                   ,position_actv  = '$lv_position_actv'
                                                   ,position_dt    = '$lv_position_dt'
                                                 ,position_title = '$lv_position_title'
                                                  ,position_order = '$lv_position_order'
                                          WHERE position_id = $lv_position_id");

[Barand]

Why have you turned auto_commit off?

When you connect to the server, are you telling mysqli to report errors? EG...

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);       // turn on error reporting                        
$db = mysqli_connect(HOST,USERNAME,PASSWORD,$database);                                
$db->set_charset('utf8');                 

 

[Alans]

I had found this, but your error reporting statement works much better. 

    error_reporting(E_ALL);
    ini_set('display_errors', 1);

after running again I found it was a data issue.  there was a rogue single quote in my text.  I removed that and now it is working as expected

many thanks

[Barand]

8 minutes ago, Alans said:

there was a rogue single quote in my text.

You should be using prepared statements. They have the added benefit that you then dont have to worry about data values like "O'Reilly" or "The Bull's Head"

[gizmola]

There is no reason to be writing obsolete/insecure php mysql code in 2022.

Literally every veteran member of this forum will offer you the same advice:

• The mysqli_ interface to mysql is annoying to use.  Learn/use PDO instead!
• There is a fast easy to read guide to using PDO that will teach you everything you need to know, as well as offer best practices.

Whether it be mysqli_ or PDO, all variables should be prepared/bound, as Barand advised.  It's just cleaner/easier to use PDO to write your code.

Now every single person like yourself that is learning, when they receive this suggestion, has the same reaction which is "thanks, I will have to learn PDO ... sometime in the future (aka never) because they will have to learn PDO and rewrite some of their existing code.  

I understand that this is a natural reaction to a learning curve and the unknown.  With that said, aside from understanding how to set up a PDO connection, you really will thank us all later, if you make the change now.