Jump to content
Larry Ullman's Book Forums

Myquli UPDATE not working with prepared statement


Recommended Posts

Hi

I am having a lot of trouble trying to get this code working.  It is a mysqli UPDATE.  The values in the database are not changing.  I have tries to trap the error but it doesn't provide anything meaningful - at least for me.

The date and time are entered as form data.  After validation, a timestamp is created.  The time can be submitted in two formats, so I had to make sure both formats could be resolved to form the timestamp.  The time stamp is then passed to the database and this is the part that's not working.

 

Here is the error message: Binding parameters failed: (0) Error: .

Could not retrieve the data because: . The query was UPDATE alexbasketballdraw SET matchdate=?, umpire=?, venue=? WHERE row_id = 92 AND grade=8.

Here is the code:

foreach($_POST['match_id'] as $id){
    $date=validate_input($_POST['date'][$id]); 
    $time=validate_input($_POST['time'][$id]); 
    $umpire=validate_input($_POST['umpire'][$id]); 
    $venue=validate_input($_POST['venue'][$id]); 

    //Create timestamp from one date format and two time formats.
if (preg_match('/:/',$time)){
        
        $time_exploded = explode(":", $time);//hh:mm format
        $ampm = substr($time_exploded[1],-2,2);
        if ($ampm == "pm"){
            $hh = $time_exploded[0] +12;
               $ml = strlen($time_exploded[1]);
            $mm = substr($time_exploded[1],-4,2);
                   
            }else{
            $hh = $time_exploded[0] ;
            $ml = strlen($time_exploded[1]);
            $mm = substr($time_exploded[1],-4,2);
                      
              }
    }else{
          $hh = substr($time, -4, 2);
        $mm = substr($time, -2,2);
}
        


$exploded_date = explode("/", $date);
$d = $exploded_date[0];
$m = $exploded_date[1];
$y = $exploded_date[2];

$timestamp = mktime($hh,$mm,0,$m,$d,$y);//Not sure if using a '0' is how you make mktime ignore the seconds.

$query = "UPDATE $compdraw SET matchdate=?,
                    umpire=?, venue=?
                    WHERE row_id = $id  AND grade=$grd";
    $stmt=mysqli_prepare($dbc,$query);
    if(!mysqli_stmt_bind_param($stmt, 'iss', $timestamp,$umpire,$venue));
    {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;// I found this error reporting on-line - not sure if I have used it correctly.
}
    mysqli_stmt_execute($stmt);                
             
    if (mysqli_stmt_affected_rows($stmt) ==1) { // Execute the query.
    }else{ // Query didn't run.
                    printf("Error: %s.\n", mysqli_stmt_error($stmt));
                    die ('<p>Could not retrieve the data because: <b>' . mysqli_error($dbc) . "</b>. The query was $query.</p>");
                    
    }
    //mysqli_$stmt_close($stmt); //Have commented this out as it threw an error.  Try to resolve this once the rest is going.
}

 

I hope I have provided too much code.  I have other scripts working where I used prepared statements and they are working fine.  So I am thinking that the structure is different for UPDATE, but I am not sure and could not find any help on-line.

I hope someone can help me.  Thanks in advance and kind regards,

Santilal

 

Link to comment
Share on other sites

Thanks.  Yes I knew I was mixing styles and I expected that it would crash, but it didn't.  I think I am only mixing styles to get the error.  I only added " if(!mysqli_stmt_bind_param($stmt, 'iss', $timestamp,$umpire,$venue));
    {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;// I found this error reporting on-line - not sure if I have used it correctly.
}"

when I found that the bind did not appear to be working.  I will change that to procedural and keep at it.  Thanks for your help.  

Santilal

Link to comment
Share on other sites

Hi

I found the problem.  I was updating a table where some values remained unchanged and flagging an error if (mysqli_stmt_affected_rows($stmt) ==1.  I did not realise that mysql did not update an identical row, so that is why I was triggering an error if one row was not affected.  So by removing this condition the script now works as intended.

My question now is what is the best way to detect that the query has failed when using prepared statements and you know some rows will be identical.

Thanks.

Santilal

Link to comment
Share on other sites

Ah, great! Glad you're making progress and thanks for letting us know! Just to be clear, you should never mix procedural and OOP approaches like that as you won't necessarily get the errror messages, let alone the results, you're looking for.

As for your question, if a query must affect one or more rows, than checking for that is perfect. But if a query won't necessarily affect multiple rows than you can confirm that it worked--or that it didn't fail--by either checking the truthiness of the query result or by checking for the presence of an error. 

Link to comment
Share on other sites

 Share

×
×
  • Create New...