How to determine if Update statement was successful

[foxclone]

I know this has a simple answer that I should know, but for the life of me, I can’t remember. I have the following update statement in my code:

$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
$stmt->execute (array($isoname, $md5file, $isosize2, 1)) ;

How do I test if the update was successful? I tried the following and it errors on the if clause:

$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
      $stmt->execute (array($isoname, $md5file, $isosize2, 1)) ;
      if $stmt = false
         exit;

Thanks in advance

[ErnieAlex]

Well, there are many ways. You could check the data to make sure it all matches the data you updated to.
Similar to your current version, but, acquire the results of the executed query. You already do that using the $stmt variable. Just test it this way instead:

$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
if ( $stmt->execute (array($isoname, $md5file, $isosize2, 1) ) {
    //  Executed query completed, do something or nothing actually!
} else {
    //  Query failed, therefore throw an error or  create a custom error handler to deal with it.
}

You must be very careful about using EXIT commands. They exit a routine, but, do not always pass the needed data backwards outside the function you are in. Meaning, for errors, you do not just exit the code, you handle the errors somehow. Either check out why it fails and let the user know OR post an alert to the ADMIN to handle why items are not being updated. I seem to never have errors while updating data. The SQL database is solid and most likely would only fail if you try to save data of the wrong data type. Or if you attempt to update a protected field like an auto increment ID field with the a duplicate ID number. Therefore, it is important to validate all of your data before using it to alter your database. Hope all this helps!

[foxclone]

@ErnieAlex - I’m using it to determine whether or not to continue processing the script. It’s an admin routine, not a user routine. There’s a message further down the routine. If it fails, I never see the completion message. I could change it to set a flag instead of exiting and add a routine failed message.

[foxclone]

@ErnieAlex

if ( $stmt->execute (array($isoname, $md5file, $isosize2, 1) ) {
    //  Executed query completed, do something or nothing actually!
} else {
    //  Query failed, therefore throw an error or  create a custom error handler to deal with it.

I’m getting errors on this code:

    if ( $stmt->execute (array($isoname, $md5file, $isosize2, 1) ) {
        $chk=1 }
     else {
        $chk=0
    }

[ErnieAlex]

Well, this code makes no sense at all!

 if ( $stmt->execute (array($isoname, $md5file, $isosize2, 1) ) {
        $chk=1 
} else {
        $chk=0
    }

If you look at this code, you set $chk equal to 1 or 0 but, you never end either assignment!
$chk=1 $chk=0 both never end! Try $chk=1; $chk=0;

Also, try just this way. (faster as no array is built!)

@ErnieAlex

if ( $stmt->execute( [$isoname, $md5file, $isosize2, 1] ) {

Also, it would help if you posted the error who you say you get one. Hope this helps!

[phdr]

The UPDATE query is successful if it executed without any errors and the number of affected rows is one.

You need both PDOStatement error handling, to test for errors, such as sql mistakes, out of range values, and duplicate values, which you probably already have in the form of PDO exceptions, and you also need to use the PDOStatement rowCount() method - https://www.php.net/manual/en/pdostatement.rowcount.php to test if a row was actually updated.

[foxclone]

The following code errors in VS code:

     $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
     $count= $stmt->execute ($isoname, $md5file, $isosize2, 1 ) ;
        if ($count=0)   <--------------------errors here
          echo ("Std update failed");

I get the following error in VS Code:
syntax error, unexpected ‘$count’ (T_VARIABLE), expecting ‘(’

[benanamen]

You do know that a single equals is an assignment operator right?

[foxclone]

How stupid of me Thanks for that reminder, it cleared the error.

[foxclone]

Getting a new error in VS code. Here’s the code as it exists:

if( isset( $isoname ) ) { 
      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
      $count= $stmt->execute ($isoname, $md5file, $isosize2, 1 ) ;
        if ($count==0) 
          goto failed;
        else
           $chk == 1;

It’s erroring on the $stmt=$pdo->prepare statement. Here’s the exact error:
“syntax error, unexpected ‘$stmt’ (T_VARIABLE)”

NOTE:

Got that fixed but when I run the actual code, on this line:
$count= $stmt->execute ($isoname, $md5file, $isosize2, 1 ) ;
I get the following error:

Warning: PDOStatement::execute() expects at most 1 parameter, 4 given in /var/www/foxclone/public_html/update.php on line 89

DUH!! Need to give it an array to execute.

[foxclone]

Got it all working.Thanks to every who contributed. Here’s the final version of the code:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, height=device-height, initial-scale=1.0">
  <title>Foxclone backup, restore, and cloning utility</title>  
  <meta name="description" content= "FoxClone is a Linux based image backup, restore and clone tool using a simple point and click interface." />
  
    <link rel="stylesheet" type="text/css" media="screen" href="css/update.css"> 

    <!-- Favicon  -->
    <link rel="apple-touch-icon" sizes="180x180" href="images/favicon/apple-touch-icon.png">
    <link rel="icon" type="image/png" sizes="32x32" href="images/favicon/favicon-32x32.png">
    <link rel="icon" type="image/png" sizes="16x16" href="images/favicon/favicon-16x16.png">
    
    <link rel="mask-icon" href="images/favicon/safari-pinned-tab.svg" color="#5bbad5">
    <meta name="msapplication-TileColor" content="#da532c">
    <meta name="theme-color" content="#ffffff">

</head>

<body>
 
  <div class="container"> 
      <div class="row" style="text-align:center;" >
        <h1>Foxclone Filelist Updating</h1>
      </div>
         <div class="header"> </div>
<?php
  $chk=0;
  
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);
   
       $isos = glob('download/foxclone_std*.iso');
       $iso = $isos[count($isos) -1];
       $isoname =  basename($iso);
       $md5file = md5_file($iso);            /*  get md5sum of file          */
       $isosize = filesize($iso);            /*  yields filesize in bytes    */
       $isosize2 = ($isosize/1048576);       /*  yields filesize in MB       */
  
       $edges = glob('download/foxclone_edge*.iso');
       $edge = $edges[count($edges) -1];
       $edgename =  basename($edge);
       $md5edge = md5_file($edge);
       $edgesize = filesize($edge);
       $edgesize2 = ($edgesize/1048576);

       $pdfs = glob('download/foxcloneV*.pdf');
       $pdf = $pdfs[count($pdfs) -1]; 
       $pdfname = basename($pdf);
     
       $debs = glob('download/*.deb');
       $deb = $debs[count($debs) - 1];
       $debname =  basename($deb);
       $debmd5 = md5_file($deb);                   
       $debsize = filesize($deb);
       $debsize2 = ($debsize/1048576);


       $srcs = glob('download/*.tar.gz');
       $src = $srcs[count($srcs) - 1];
       $srcname = basename($src);  
       $srcmd5 = md5_file($src);  
       $srcsize = filesize($src);
       $srcsize2 = ($srcsize/1048575);

       $issuess = glob('download/news.pdf');
       $issue = $issuess[count($issuess) -1]; 
       $issname = basename($issue);
       
    
$php_scripts = '../php/';
require $php_scripts . 'PDO_Connection_Select.php';

if (!$pdo = PDOConnect("foxclone_data"))
{	
	$chk=0;
    exit;
}


    if( isset( $isoname ) ) { 
      
      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
      $count= $stmt->execute (array($isoname, $md5file, $isosize2, 1 )) ; 
        if ($count==0) 
            exit("Failed to update std iso");

      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?");
      $count= $stmt-> execute (array($edgename, $md5edge, $edgesize2, 6)) ;
          if ($count==0) 
              exit("Failed to update edge iso");
          
      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `logtime`=now() WHERE `id` = ?");
      $count= $stmt-> execute (array($pdfname, 2)) ;  
          if ($count==0) 
              exit("Failed to update pdfname");
          
      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?, `logtime` = now() WHERE `id` = ?");
      $count= $stmt->execute (array($debname, $debmd5,$debsize2, 3)) ;   
          if ($count==0) 
          exit("Failed to update deb");
          
      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?,`filesize` = ?,`logtime` = now() WHERE `id` = ?");
      $count= $stmt->execute (array($srcname, $srcmd5, $srcsize2,4)) ; 
          if ($count==0) 
              exit("Failed to update std iso");
          
      $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `logtime`=now() WHERE `id` = ?");
      $count= $stmt-> execute (array($issname, 5)) ; 
          if ($count==0) 
          exit("Failed to update news");
          
    }
    ?>
 <div class="row" style="text-align:center;" </div>   
    <h1>Database Update Successful</h1>

[benanamen]

@foxclone

Got it all working.

In theory…

See all that duplicate code? There must be a better way right?

[foxclone]

If there is, I don’t know how. With 6 different SQL prepare statements and 6 different sets of data, it would take a two sets of arrays to reduce what code I presently have. The same problem with the data retrieval statements.

Maybe a For Each loop for both sections.

[benanamen]

When you find yourself repeating code, that is a good time to call on a user defined function.
https://www.php.net/manual/en/language.functions.php

It seems you need some logic to actually figure out what your trying to update.

Would it be correct to say that the real problem you are trying to solve with this code is to log downloads?

This is just a partial view of your duplicate code.

$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?, `logtime` = now() WHERE `id` = ?");
$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?, `logtime` = now() WHERE `id` = ?");
$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?, `logtime` = now() WHERE `id` = ?");
$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?, `logtime` = now() WHERE `id` = ?");

$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `logtime`=now() WHERE `id` = ?");
$stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `logtime`=now() WHERE `id` = ?");

$count= $stmt->execute (array());
$count= $stmt->execute (array());
$count= $stmt->execute (array());
$count= $stmt->execute (array());
$count= $stmt->execute (array());
$count= $stmt->execute (array());

if ($count==0) exit("");
if ($count==0) exit("");
if ($count==0) exit("");
if ($count==0) exit("");
if ($count==0) exit("");
if ($count==0) exit("");

[foxclone]

You’re correct, we’re updating the database when we provide new files for users to download. I’ll look at that link and see what I can come up with.

[benanamen]

@foxclone

we’re updating the database when we provide new files for users to download

Your query doesn’t make much sense for tracking downloads. All you are doing is updating an existing record with the same exact data that is already there except for updating the datetime data.

What is it that you are actually wanting to do? Please provide a high level overview of what your doing.

[foxclone]

Periodically, we upload new files with different filename versions, sizes, and md5sums,etc. That data is displayed on a download page (See Foxclone download page. This script is intended to update the table that the download page retrieves the data from. I hope this provides the overview you need.

[ErnieAlex]

Foxclone, once you prepare a query, you only do it once and then call the execute using the same already prepared query. No need to process it a second time. If you want to learn how to do this with PDO, this PDF page will explain it all from start to end: https://phpdelusions.net/pdo

Also, since you know where all the data is and the names of it, you can just create a loop to get the names of the data to use in the execute statement.

[foxclone]

This is what I’ve been able to come up with. Much cleaner than what I had but I’m getting an error in VS code that I don’t know how to handle. Here’s the script with the error noted as a comment:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, height=device-height, initial-scale=1.0">
  <title>Foxclone backup, restore, and cloning utility</title>  
  <meta name="description" content= "FoxClone is a Linux based image backup, restore and clone tool using a simple point and click interface." />
  
    <link rel="stylesheet" type="text/css" media="screen" href="css/update.css"> 

    <!-- Favicon  -->
    <link rel="apple-touch-icon" sizes="180x180" href="images/favicon/apple-touch-icon.png">
    <link rel="icon" type="image/png" sizes="32x32" href="images/favicon/favicon-32x32.png">
    <link rel="icon" type="image/png" sizes="16x16" href="images/favicon/favicon-16x16.png">
    
    <link rel="mask-icon" href="images/favicon/safari-pinned-tab.svg" color="#5bbad5">
    <meta name="msapplication-TileColor" content="#da532c">
    <meta name="theme-color" content="#ffffff">

</head>

<body>
 
  <div class="container"> 
      <div class="row" style="text-align:center;" >
        <h1>Foxclone Filelist Updating</h1>
      </div>
         <div class="header"> </div>
<?php
  $chk=0;
  
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);


    function updater () {

    $php_scripts = '../php/';
    require $php_scripts . 'PDO_Connection_Select.php';
    
    if (!$pdo = PDOConnect("foxclone_data"))
    {	
      $chk=0;
        exit;
    }

        $meg = 1048576 ;  // needed to convert filesize bytes to Mb
        $stmt = $pdo->prepare("UPDATE `files` SET `filename` = ?, `md5` = ?, `filesize` = ?,`logtime` = now() WHERE `id` = ?") ;
        $path = 'download/';

        foreach(glob($path.'*.*') as $file) {
          $file = new SplFileInfo($path.$file);
          $extension  = $file->getExtension();          
------> if (substr($file, 10,3) = "std")  //**Errors with Can't use function return value in write context**
                  $getdata = (array($file, md5_file($file), filesize($file)/$meg, 1)) ;
                  $count= $stmt->execute ($getdata) ; 
                     if ($count==0) 
                        exit("Failed to update std iso");

              elseif (substr($file, 9,1) = "V")
              $getdata = (array($file, md5_file($file), filesize($file)/$meg, 2)) ;
              $count= $stmt->execute ($getdata) ; 
                 if ($count==0) 
                    exit("Failed to update pdf");
                             
              elseif ($extension = "deb")
              $getdata = (array($file, md5_file($file), filesize($file)/$meg,3)) ;         
              $count= $stmt->execute ($getdata) ; 
              if ($count==0) 
                   exit("Failed to update deb");
                                   
              elseif ($extension = "gz")
              $getdata = (array($file, md5_file($file), filesize($file)/$meg,4)) ;
              $count= $stmt->execute ($getdata) ; 
              if ($count==0) 
                  exit("Failed to update gz");

              elseif (substr($file, 1,4) = "news")
              $getdata = (array($file, md5_file($file), filesize($file)/$meg, 5)) ;
              $count= $stmt->execute ($getdata) ; 
              if ($count==0) 
                  exit("Failed to update news");
                                     
              elseif (substr($file(10,3) = "edg"))
              $getdata = (array($file, md5_file($file), filesize($file)/$meg,6)) ;
              $count= $stmt->execute ($getdata) ; 
              if ($count==0) 
                  exit("Failed to update edge iso");
        
                         
        }
  }

    ?>
 <div class="row" style="text-align:center;" </div>   
    <h1>Database Update Successful</h1>

[benanamen]

That is better. There is still repeated code which means there is still more you can do to improve it. Lets see what you can do from here.