PHP 8.2 - MySQL query returns row count of 7 but only 6 are displayed

Beginners - Learning PHP
#1

Here’s my code -what am I doing wrong?

$result = mysqli_query($con, "SELECT * FROM comments WHERE comid = $id ");
 $rowcount=mysqli_num_rows($result);
echo "<h3>". $rowcount . " Comments</h3>";
$row = mysqli_fetch_array($result);
while($row = mysqli_fetch_array($result))
{
if ($rowcount <1)
{ echo "No comments posted yet"; }
else { echo $row['comment'];
echo "<div class='right my-2 ms-2' ><i>" . $row['name'] , " - " . date('d-M-Y',strtotime($row['date'])) . "</i></div>";
echo "<br>&nbsp;<hr >";
}}
CloseCon($con);

I was expecting to show all 7 comments, but it only shows 6.

#2

Personally I would use PDO over mysqli as it is easier to learn and more versatile along with prepared statements.

try {
$host = '127.0.0.1';     // The database host
$dbname = 'my_database'; // The name of the database
$user = 'my_username';   // The database username
$pass = 'my_password';   // The database password
$charset = 'utf8mb4';    // The character set. This is set to the utf8mb4 character set, which supports emoji and other symbols.

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset"; // The Data Source Name

$options = [ // PDO options
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,    // Turn on errors in the form of exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,         // Set the default fetch mode to associative array
    PDO::ATTR_EMULATE_PREPARES   => false,                    // Turn off emulation mode for "real" prepared statements
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
    $stmt = $pdo->prepare("SELECT * FROM comments WHERE comid = :id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);  // Assuming $id is an integer
    $stmt->execute();

    $rowcount = $stmt->rowCount();  // Get the number of rows

    echo $rowcount . " Comments<br>";

    if ($rowcount < 1) {
        echo "No comments posted yet";
    } else {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo $row['comment'] . "<br>" . $row['name'] . " - " . date('d-M-Y', strtotime($row['date'])) . "<br><br>";
        }
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage(); // Not for production use
}

BTW the original code had a redundant fetch array that is why the count is wrong.

#3

Thanks stride but I can’t get this to work at all
Parse error : Unmatched ‘}’ in /Users/michaelsharpe/Websites/auntymadge/comments.php on line 85

here >> } catch (PDOException $e) {
echo "Error: " . $e->getMessage(); // Not for production use
}

I can’t find the matching curly bracket

#4

@Strider64, commenting on your code.

One of the points of using the PDO extension is that it can be simpler to use than the mysqli extension.

The only database exceptions you should catch and handle in your code are for user recoverable errors, such as when inserting/updating user submitted values. All other database statement errors are due to programming mistakes or the database server not running, …, which the user (hacker) visiting a site doesn’t need to know anything about. If you do NOTHING in your code for these cases and simply let php catch and handle any database exception, php will ‘automatically’ display/log the raw database statement errors the same as php errors, via an uncaught exception error.

By catching and throwing your own exception, with only the message and code (this is the sql state code, which usually corresponds to several related errors), you loose the file name, line number, and error number information, making any debugging harder. If you do nothing in this case, the information php displays or logs will include all the available information.

By specifically binding inputs, in addition to unnecessary lines of code, you are limiting values to php’s maximums, which are much smaller than the values that databases support. While this won’t immediately cause problems in academic assignments, it can result in unexplained data issues in real applications. Since your code is using real prepared queries, if you simply supply an array of the input values to the ->execute([…]) call, this respects the data type of the value. This works correctly for strings, numbers, boolean, null, and blog/binary data.

RowCount() is not guaranteed to be supported for queries that return result sets. Just fetch all the data from a query and test (or count()) if the fetched data is a true/false value.

You have set the default fetch mode to assoc when you make the database connection. Why are you specifying it in the fetch() statement?

Sponsor our Newsletter | Privacy Policy | Terms of Service