PHP分页错误

Question

I'm trying to integrate pagination into my code and I am receiving an error of Fatal error: Call to undefined method mysqli_result::fetchColumn() corresponding to the line of code: $total = $connection->query('SELECT COUNT(*) FROM table_shift INNER JOIN table_staff ON table_shift.uniqueid = table_staff.uniqueid')->fetchColumn();. I'm running wampserver using PHP Version 5.4.12. I'm lost as to whether this is an issue with my PHP version as google has led me to believe, or whether I've made a syntax error.. So i've included the code in case an error exists in there.

try {

// Find out how many items are in the table
$total = $connection->query('SELECT COUNT(*) FROM table_shift INNER JOIN table_staff ON table_shift.uniqueid = table_staff.uniqueid')->fetchColumn();

// How many items to list per page
$limit = 15;

// How many pages will there be
$pages = ceil($total / $limit);

// What page are we currently on?
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
    'options' => array(
        'default'   => 1,
        'min_range' => 1,
    ),
)));

// Calculate the offset for the query
$offset = ($page - 1)  * $limit;

// Some information to display to the user
$start = $offset + 1;
$end = min(($offset + $limit), $total);

// The "back" link
$prevlink = ($page > 1) ? '<a href="?page=1" title="First page">&laquo;</a> <a href="?page=' . ($page - 1) . '" title="Previous page">&lsaquo;</a>' : '<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>';

// The "forward" link
$nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page">&rsaquo;</a> <a href="?page=' . $pages . '" title="Last page">&raquo;</a>' : '<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</span>';

// Display the paging information
echo '<div id="paging"><p>', $prevlink, ' Page ', $page, ' of ', $pages, ' pages, displaying ', $start, '-', $end, ' of ', $total, ' results ', $nextlink, ' </p></div>';

// Prepare the paged query
$stmt = $connection->prepare('SELECT * FROM table_shift INNER JOIN table_staff ON table_shift.uniqueid = table_staff.uniqueid ORDER BY shiftid DESC LIMIT :limit OFFSET :offset');

// Bind the query params
$stmt->bindParam(':limit', $limit, PDO:: PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO:: PARAM_INT);
$stmt->execute();

// Do we have any results?
if ($stmt->rowCount() > 0) {
    // Define how we want to fetch the results
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $iterator = new IteratorIterator($stmt);

    // Display the results
    foreach ($iterator as $row) {
     echo "<td>". htmlentities($row['shiftid'], ENT_QUOTES, 'UTF-8') . "</td> "; 
     echo "<td>". htmlentities($row['first_name'], ENT_QUOTES, 'UTF-8') . " " . htmlentities($row['surname'], ENT_QUOTES, 'UTF-8') . "</td> ";
     if($row['location']==0) 
         { echo "<td>Location 1</td> ";} 
     else 
         { echo "<td>Location 2</td> ";}
     echo "<td>". htmlentities(date('d-m-Y', strtotime($row['shift_date'])), ENT_QUOTES, 'UTF-8') . "</td> ";
     echo "<td>". htmlentities($row['start_time'], ENT_QUOTES, 'UTF-8') . "</td> ";
     echo "<td>". htmlentities($row['end_time'], ENT_QUOTES, 'UTF-8') . "</td> ";
     echo "<td>". htmlentities($row['total_hours'], ENT_QUOTES, 'UTF-8') . "</td> ";
     echo "<td>£". htmlentities(number_format($row['totalPaid'], 2, '.', ','), ENT_QUOTES, 'UTF-8') ."</td> ";
    }

} else {
    echo '<p>No results could be displayed.</p>';
}

    } catch (Exception $e) {
echo '<p>', $e->getMessage(), '</p>';
    }

Apart from changing $dbh to $connection, and updating the SQL queries for my purposes, the code remains largely unchanged.

Answer 1

mysqli_result has no fetchColumn method as the error message states. Check the docs for more help: http://uk1.php.net/mysqli-result

You could use fetch_row instead:

$total = $connection->query('SELECT COUNT(*) FROM table_shift INNER JOIN table_staff ON table_shift.uniqueid = table_staff.uniqueid')
->fetch_row()[0];