CODEWITHSUNDEEP
phpmysqlsqlpdo
Sam
Sam

Reputation: 1666

SQL limit returns all results

I'm trying to limit the number of results using variables I set them and its still returning all the results even though I'm setting a limit.

// Results Per page
$limit = 3;

// Page Number
$p=$_GET['p']=="" ? 1:$_GET['p'];
$start=($p-1)*$limit;

// Query
$sql=$conn->prepare("SELECT * FROM adverts WHERE status = 2 ORDER BY ref LIMIT :start, :limit");
$sql->bindValue(':limit', $limit, PDO::PARAM_INT);
$sql->bindValue(':start', $start, PDO::PARAM_INT);
$sql->execute();
$data=$sql->fetch();
?>

<section id="advert-listings">
    <div class="container page-wrapper">
        <div class="col-md-8 advert-listing">
            <?php
                print_r ($sql);
                echo $start; 
                echo $limit; 
            ?>
            <?php if ($sql->rowCount()!=0) : ?>
                <?php foreach ($adverts as $advert) : ?>
                <div class="caption">
                    <ul>
                        <li><i class="fa fa-moon-o"></i> Sleeps: <?= $advert['news']; ?></li>
                        <li><i class="fa fa-paw"></i> Pets: <?= $advert['header']; ?></li>
                        <li><i class="fa fa-key"></i> Ref: <?= $advert['ref']; ?></li>
                    </ul>
                </div>
                <?php endforeach; ?>
            <?php endif; ?>

As you can see I echo the $limit & $start which return 0, 3.

It should be limiting the results to 3 starting at 0.

Upvotes: 2

Views: 139

Answers (1)

Harry Beasant
Harry Beasant

Reputation: 1014

Heres what you need to do;

You have limit and offset the wrong way around, change them to LIMIT :limit OFFSET :offset

Next you need to cast your ints, add this;

$sql->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
$sql->bindValue(':start', (int)$start, PDO::PARAM_INT);

Next, change this;

$data=$sql->fetch()

To:

$adverts=$sql->fetchAll()

Upvotes: 3

Related Questions