Santilal Posted April 9, 2020 Share Posted April 9, 2020 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 More sharing options...
Larry Posted April 9, 2020 Share Posted April 9, 2020 It looks like you're mixing both procedural and OOP styles. You need to pick and use just one. I think that's why it's not working but the error results also aren't helpful. Link to comment Share on other sites More sharing options...
Santilal Posted April 9, 2020 Author Share Posted April 9, 2020 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 More sharing options...
Santilal Posted April 10, 2020 Author Share Posted April 10, 2020 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 More sharing options...
Larry Posted April 13, 2020 Share Posted April 13, 2020 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 More sharing options...
Recommended Posts