Trying to add a where clause to my pdo select

[geatzo]

 $limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
  $page = (isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
  $paginationStart = ($page - 1) * $limit;
  $authors = $db->query("SELECT * FROM posts LIMIT $paginationStart, $limit")->fetchAll();
  // Get total records
  $sql = $db->query("SELECT count(id) AS id FROM posts")->fetchAll();
  $allRecrods = $sql[0]['id'];
  
  // Calculate total pages
  $totoalPages = ceil($allRecrods / $limit);
  // Prev + Next
  $prev = $page - 1;
  $next = $page + 1;

trying to add a where to the first select 

 

  $authors = $db->query("SELECT * FROM posts  WHERE username = ? LIMIT $paginationStart, $limit")->fetchAll();
$result = $pdo->prepare($authors);
$result->execute([$row2['username']]);

but im getting a error if anyone can help?

[Barand]

Use "prepare" as alternative to "query", not both.

$authors = $db->prepare("SELECT * FROM posts  WHERE username = ? LIMIT $paginationStart, $limit")->fetchAll();
$authors->execute([$row2['username']]);

to get your count of the records, you can

$sql = $db->query("select count(*) from posts");
$allRecords = $sql->fetchColumn();

 

[geatzo]

1 minute ago, Barand said:

Use "prepare" as alternative to "query", not both.

$authors = $db->prepare("SELECT * FROM posts  WHERE username = ? LIMIT $paginationStart, $limit")->fetchAll();
$authors->execute([$row2['username']]);

to get your count of the records, you can

$sql = $db->query("select count(*) from posts");
$allRecords = $sql->fetchColumn();

 

Im getting <b>Fatal error</b>: Uncaught Error: Call to a member function execute() on array in  with the first bit of code 

[Barand]

Sorry, I forgot to take the "->fetchAll()" from the end. Let's start again

$stmt = $db->prepare("SELECT * FROM posts  WHERE username = ? LIMIT $paginationStart, $limit");
$stmt->execute([$row2['username']]);
$authors = $stmt->fetchAll();

 

[Strider64]

Figuring out the offset  and using it in the query really helps -

        $sql = 'SELECT * FROM gallery WHERE page =:page AND category =:category ORDER BY id DESC, date_added DESC LIMIT :perPage OFFSET :blogOffset';
        $stmt = $pdo->prepare($sql); // Prepare the query:
        $stmt->execute(['page' => $page, 'perPage' => $perPage, 'category' => $category, 'blogOffset' => $offset]); // Execute the query with the supplied data:
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

and this might  help

// Grab the current page the user is on
if (isset($_GET['page']) && !empty($_GET['page'])) {
    $current_page = urldecode($_GET['page']);
} else {
    $current_page = 1;
}

$per_page = 1; // Total number of records to be displayed:


// Grab Total Pages
$total_pages = $gallery->total_pages($total_count, $per_page);


/* Grab the offset (page) location from using the offset method */
/* To figure out offset -> $offset = $per_page * ($current_page - 1) */
$offset = $gallery->offset($per_page, $current_page);

Just an example of pagination