Menu
I am reading a tutorial on how to insert and update data into a MySQL table using PHP, the code is listed below. My problem is when i click update but I have not modified any data, rowCount() returns 0 and breaks the code.
My question is, If I am simply updating the database with the same values that are in the database, why does rowCount() return zero? My thoughts were that even though it was the same data it would be inserted anyway and return a count of the updated rows? I am guessing that it check the data before it try's the update? Can anyone shed some light on this for me and suggest a workaround? I have been starring at the code for hours and have been unable to come up with anything, thanks.
<?php require_once('../includes/connection.inc.php'); // initialize flags $OK = false; $done = false; // create database connection $conn = dbConnect('write', 'pdo'); if (isset($_GET['article_id']) && !$_POST) { // prepare sql query $sql = 'SELECT article_id, title, article FROM blog WHERE article_id = ?'; $stmt = $conn->prepare($sql); // bind the results $stmt->bindColumn(1, $article_id); $stmt->bindColumn(2, $title); $stmt->bindColumn(3, $article); // execute query by passing array of variables $OK = $stmt->execute(array($_GET['article_id'])); $stmt->fetch(); } // if form has been submitted, update record if (isset($_POST['update'])) { //prepare update query $sql = 'UPDATE blog SET title = ?, article = ? WHERE article_id = ?'; $stmt = $conn->prepare($sql); // execute query by passing array of variables $stmt->execute(array($_POST['title'], $_POST['article'], $_POST['article_id'])); $done = $stmt->rowCount(); } // redirect page on sucess or if $_GET['article_id'] not defined if ($done || !isset($_GET['article_id'])) { header('Location: http://localhost/PHP_Solutions/admin/blog_list_pdo.php'); exit(); } // store error message if query fails if (isset($stmt) && !$OK && !$done) { $error = $stmt->errorInfo(); if (isset($error[2])) { $error = $error[2]; } } ?> <!DOCTYPE HTML> <html> <head> <meta charset="utf-8"> <title>Update Blog Entry</title> <link href="../styles/admin.css" rel="stylesheet" type="text/css"> </head> <body> <h1>Update Blog Entry</h1> <p><a href="blog_list_pdo.php">List all entries </a></p> <?php if (isset($error[2])) { echo "<p class='warning'>Error: $error[2]</p>"; echo '<pre>'; print_r($_POST); print_r($error); echo '</pre>'; } if ($article_id == 0) { ?> <p class="warning">Invalid request: record does not exist.</p> <?php } else { ?> <form id="form1" method="post" action=""> <input name="article_id" type="hidden" value="<?php echo $article_id; ?>"> <p> <label for="title">Title:</label> <input name="title" type="text" class="widebox" id="title" value="<?php echo htmlentities($title, ENT_COMPAT, 'utf-8'); ?>"> </p> <p> <label for="article">Article:</label> <textarea name="article" cols="60" rows="8" class="widebox" id="article"><?php echo htmlentities($article, ENT_COMPAT, 'utf-8'); ?></textarea> </p> <p> <input type="submit" name="update" value="Update Entry" id="update"> </p> </form> <?php } ?> </body> </html> 
358
PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement. print("Return number of rows that were deleted:\n"); $count = $del->rowCount();
Use PDO's fetchAll() function to fetch all the rows into an array, then use count() on it.
PDO::query() returns a PDOStatement object, or FALSE on failure.
We can get the total number of rows in a table by using the MySQL mysqli_num_rows() function. Syntax: mysqli_num_rows( result ); The result is to specify the result set identifier returned by mysqli_query() function.
My question is, If I am simply updating the database with the same values that are in the database, why does rowCount() return zero?
rowCount is counting the affected rows by a query. As you haven't changed anything, there are zero affected rows.
PDOStatement->rowCount — Returns the number of rows affected by the last SQL statement
156
It has nothing to do with PHP - it's just how MySQL works.
MySQL documentations says:
For
UPDATEstatements, the affected-rows value by default is the number of rows actually changed. If you specify theCLIENT_FOUND_ROWSflag tomysql_real_connect()when connecting tomysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
