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.
How do I count the 13th entry (and those after) in the DB?
Moderator: General Moderators
-
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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I count the 13th entry (and those after) in the D
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
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.
All the best from the United Kingdom.
Re: How do I count the 13th entry (and those after) in the D
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
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.
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.
All the best from the United Kingdom.
Re: How do I count the 13th entry (and those after) in the D
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:
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
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
@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]
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]