-1

How can I get data from SQL using PHP of this month (not last 30 days). If this is January 10, I wan to collect data from January 1 to January 10.

I tried the following code, but it seems to be taking data from last month also!

$sql = "SELECT SUM(earnings) 
        FROM tablename 
        WHERE username='$membername' 
        AND date > DATE_SUB(NOW(), INTERVAL 1 MONTH)";

in database date in saved in following format 2020-12-26 10:02:41

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Shij Thach
  • 93
  • 7
  • 1
    You are still writing scripts that are open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. – RiggsFolly Dec 28 '20 at 12:12

2 Answers2

1
// set TimeZone, Local or UTC (can make a difference for edge cases)
$month = date('m');
$year = date('Y');
$sql = "SELECT SUM(earnings) FROM tablename
        WHERE username='$membername' AND MONTH(date) = $month
        AND YEAR(date) = $year";
UncaAlby
  • 5,146
  • 1
  • 16
  • 19
  • 1
    I have no idea why this answer is upvoted and accepted. It does not do what the question clearly asks for. `If this is January 10, I wan to collect data from January 1 to January 10.` Perhaps it is relying on business logic that `earnings` will not be stored with future dates, but this will be misleading to future researchers who may have future-stored values. This answer is also missing its educational explanation. – mickmackusa Dec 28 '20 at 05:34
  • @mickmackusa The OP asked for data from *"this month (not last 30 days)".* By *"this month"* I presume he means the *current calendar month,* and that "January 1 to January 10" is only an example for a "current date" of January 10. If the current date is February 25, this answer will return data from February 1 to February 25. I can not say whether this solution correctly solves the OP's business logic; only that it correctly answers the question. – UncaAlby Dec 28 '20 at 23:17
  • Furthermore, there is no need to pass php date-related variables into the sql when sql is perfectly capable of generating these values. Because the username variable is required in the sql, a prepared statement with a bound parameter is called for. – mickmackusa Dec 29 '20 at 00:21
  • True enough, but php is easier and allows easier control of the desired timezone. SQL injection is a strong topic on its own and certainly worth mentioning, but not part of the question. – UncaAlby Dec 29 '20 at 07:35
0
$sql = 'SELECT SUM(earnings)  FROM tablename WHERE username="$membername" and date LIKE 
 CONCAT(YEAR(CURDATE()),"-", MONTH(CURDATE()),"-%")'
Sato Takeru
  • 1,669
  • 4
  • 12
  • 27
  • 2
    While this code may help with the question, it is better to include some context, explaining how it works and when to use it. Code-only answers tend to be less useful in the long run. See [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) for some more info. – Klaus Gütter Dec 28 '20 at 07:34
  • Using `LIKE` on date-type columns is not considered best practice and probably performs worse than comparisons against dedicated date functions. A prepared statement is called for, as well. – mickmackusa Dec 29 '20 at 00:19