How do I count the 13th entry (and those after) in the DB?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do I count the 13th entry (and those after) in the DB?

Post by simonmlewis »

I need to show a user if the products they have promoted on a homepage, are no longer showing.

On the homepage, it orders by dateadded DESC and limits it to 12. So I need to tell a user if any of their products are the 13th + (ie, not being shown on homepage).

So if it could query the DB, order by dateadded DESC, and skip the first 12. And for those after, it then shows a warning on screen.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I count the 13th entry (and those after) in the D

Post by Celauran »

LIMIT. Use the offset to skip the first 12 entries.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I count the 13th entry (and those after) in the D

Post by simonmlewis »

No I need to show ALL entries, but flag up if any of their entries are row 13 +.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I count the 13th entry (and those after) in the D

Post by Celauran »

In that case, it's not even an SQL issue. Do that in PHP. Set a counter and check it while you iterate over the result set.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I count the 13th entry (and those after) in the D

Post by simonmlewis »

yes i think it has to be an embedded query.
So the first query is for only *their* rows, and the second query, is for ALL rows, and runs a count. And if any of their IDs are counted at 13 or more, then flag it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: How do I count the 13th entry (and those after) in the D

Post by Eric! »

Using the table ID's might not work long term as your data gets deleted/added. Instead just check the FOUND_ROWS() after the SELECT query. List out the 12 rows, then check the found_rows if >12 and warn them (X-12) rows were not displayed....

From the php manual here's a class extension for row counts:

Code: Select all

class db extends PDO {
  public function last_row_count() {
    return $this->query("SELECT FOUND_ROWS()")->fetchColumn();
  }
}

$myDb = new db('mysql:host=myhost;dbname=mydb', 'login', 'password' );

Then, after running your query:

if ( $myDb->last_row_count() > 12 ) {
  echo "Do something!";
  }
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I count the 13th entry (and those after) in the D

Post by requinix »

Note that for FOUND_ROWS() to show the total count you need the SQL_CALC_FOUND_ROWS option:

Code: Select all

SELECT SQL_CALC_FOUND_ROWS rest of the query...
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: How do I count the 13th entry (and those after) in the D

Post by Eric! »

@requinix thanks forgot to mention that.

Sometimes just doing a repeat query with COUNT is faster than FOUND_ROWS with large tables too...
[text]SELECT COUNT(*) FROM table WHERE use previous query condition(s)[/text]
Post Reply