CODEWITHSUNDEEP
phpmysqlsqlfunctionprepared-statement
heron
heron

Reputation: 3659

Get total rows count of table

I want to get all rows count in my sql.

Table's first 2 columns look like that

enter image description here

My function looks like that

            $limit=2;
            $sql = "SELECT id,COUNT(*),dt,title,content FROM news ORDER BY dt DESC LIMIT " . $limit;
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $stmt->bind_result($id, $total, $datetime, $title, $content);
            $stmt->store_result();
            $count = $stmt->num_rows;
            if ($count > 0) {
                while ($stmt->fetch()) {

Inside loop, I'm getting exact value of $total, but MySQL selects only 1 row - row with id number 1. (and $count is 1 too)

Tried this sql

SELECT id,dt,title,content FROM news ORDER BY dt DESC LIMIT 2

All goes well.

Why in first case it selects only 1 row? How can I fix this issue?

for ex my table has 5 rows. I want to get 2 of them with all fields, and get all rows count (5 in this case) by one query.

Upvotes: 0

Views: 792

Answers (4)

Shomz
Shomz

Reputation: 37701

Try adding GROUP BY dt if you want to use COUNT(*) (not sure why you're using it though).

EDIT

Fine, if you insist on doing it in a single call, here:

$sql = "SELECT id,(SELECT COUNT(id) FROM news) as total,dt,title,content FROM news ORDER BY dt DESC LIMIT " . $limit;

Upvotes: 2

MicronXD
MicronXD

Reputation: 2220

This totally wreaks of a HW problem... why else besides a professor's retarded method to add complexity to a simple problem would you not want to run two queries?

anyways.... here:

 SELECT id, (SELECT COUNT(*) FROM news) AS row_count, dt, title, content FROM news ORDER BY dt DESC LIMIT

Upvotes: 0

Erik Kettenburg
Erik Kettenburg

Reputation: 134

This is likely cause by the variable $limit being set to 1, or not being set and mysql defaulting to 1. Try changing your first line to

$sql = "SELECT id,COUNT(*),dt,title,content FROM news ORDER BY dt DESC";

EDIT

Change to:

$sql = "SELECT SQL_CALC_FOUND_ROWS,id,dt,title,content FROM news ORDER BY dt DESC LIMIT " . $limit;

And then use a second query with 

SELECT FOUND_ROWS( )

to get the number of rows that match the query

Upvotes: 0

dopey
dopey

Reputation: 41

Remove COUNT(*). You will only ever get 1 row if you leave it in there.

Upvotes: 2

Related Questions