Any Idea how can I identify if there is new client added on my database.
I was thinking about identifying it thru date_added field.
id client_name date_added
---------------------------------
1 ABC 2013-01-02
2 XYZ 2013-01-03
3 EFG 2013-01-02
4 HIJ 2013-01-05
as you can see a new client added HIJ on 2013-01-05.
I was looking with this kind of result:
Client List
Total NO: 4
New Client
Total No: 1
Client Name: HIJ
add a field new to the table, default it to 1, on page load use that for the select and set it to 0 to indicate its not longer new.
It's hard to tell but based on your comment ...my reference date is 1 month interval... you might be looking for something like this
SELECT id, client_name, new_count, total_count
FROM
(
SELECT id, client_name
FROM clients
WHERE date_added BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
) c CROSS JOIN
(
SELECT
(
SELECT COUNT(*) new_count
FROM clients
WHERE date_added BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
) new_count,
(
SELECT COUNT(*) total_count
FROM clients
) total_count
) t
Obviously you can easily change CURDATE() with any other reference date in the past in this query and you get results for that date.
Lets assume that you have following sample data
+------+-------------+------------+ | id | client_name | date_added | +------+-------------+------------+ | 1 | ABC | 2013-05-13 | | 2 | XYZ | 2013-06-13 | | 3 | EFG | 2013-06-13 | | 4 | HIJ | 2013-08-11 | +------+-------------+------------+
and today is 2013-08-13 then the output from the query will be
+------+-------------+-----------+-------------+ | id | client_name | new_count | total_count | +------+-------------+-----------+-------------+ | 4 | HIJ | 1 | 4 | +------+-------------+-----------+-------------+
Rows,group_id, date_created FROM client_list where date_created BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY) GROUP BY group_id ORDER BY date_created using date with interval of 7 days.. all new added client.. after 7 days they will not considered as new client.You could remember, in your webpage or PHP script, the highest ID value previously seen. Or the highest timestamp (better than a date) previously seen.
I prefer ID or Version numbers for concurrency-related stuff (locking, finding the latest etc) -- since they should be defined to be ascending, can't suffer "same millisecond" collisions, and are more efficient.
I assume you're going to hold the "state" of your application (as to what the user has seen) in hidden fields in the form, or somesuch. This would then track the "last seen" and allow you to identify "newly added" since the last pageview.
If you expect to identify newly added when coming from a different page or logging onto the application, you'll need to store the "state" in the database instead.
That depends on what you consider NEW. You have to define what you're going to compare the records against (reference date). Once you define it, you could use a query like the following:
SELECT * FROM client WHERE date_added >= '$date'
where $date is the reference date.
