Page 1 of 1

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

Posted: Fri Nov 15, 2013 4:37 am
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.

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

Posted: Fri Nov 15, 2013 6:39 am
by Celauran
LIMIT. Use the offset to skip the first 12 entries.

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

Posted: Fri Nov 15, 2013 6:45 am
by simonmlewis
No I need to show ALL entries, but flag up if any of their entries are row 13 +.

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

Posted: Fri Nov 15, 2013 6:50 am
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.

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

Posted: Fri Nov 15, 2013 6:54 am
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.

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

Posted: Fri Nov 15, 2013 6:55 pm
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!";
  }

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

Posted: Fri Nov 15, 2013 7:34 pm
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...

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

Posted: Fri Nov 15, 2013 7:50 pm
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]