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]