Choose mySQL for month and year only

Question

I have a column in my mySQL database with multiple rows. One of these lines is DATE, for example: 2012-02-01

What I want to achieve is to do SELECT with PHP based on only year and month.

The SELECT logic will be as follows:

 $q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH""; 

A specific month and year will be transferred from the $_POST variable, for example, $_POST['period']="2012-02";

How can i do this?

Answer 1

 SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5 

Answer 2

if you have

 $_POST['period'] = "2012-02"; 

First find the first day of the month:

 $first_day = $_POST['period'] . "-01" 

Then this query will use an index by Date if you have one:

 $q = " SELECT * FROM projects WHERE Date BETWEEN '$first_day' AND LAST_DAY( '$first_day' ) " ; 

You can also use inclusive-exclusive intervals, which work pretty well (you don’t have to worry about the DATE , DATETIME or TIMESTAMP column or about precision:

 $q = " SELECT * FROM projects WHERE Date >= '$first_day' AND Date < '$first_day' + INTERVAL 1 MONTH " ; 

---

Safety warning:

You must correctly avoid these values ​​or use prepared statements. In short, use any method recommended today in PHP to avoid problems with SQL injection.

Answer 3

Here you go. Leave the calculations in PHP and save your DB work. This way you can effectively use the index in the Date column.

 <?php $date = $_POST['period']; $start = strtotime($date); $end = strtotime($date . ' 1 month - 1 second'); $query = sprintf( 'SELECT * FROM projects WHERE Date BETWEEN FROM_UNIXTIME(%u) AND FROM_UNIXTIME(%u)', $start, $end ); 

EDIT
Forgot Unix timestamp conversion.

Answer 4

 $q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1; 

Answer 5

You can do the following:

 $q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'"; 

Answer 6

Here's a FIND entry for MONTH and DATE in mySQL

Here is your POST value $_POST['period']="2012-02";

Just explode the dash value $Period = explode('-',$_POST['period']);

Get array from gap value:

Array ( [0] => 2012 [1] => 02 )

Put a value in SQL Query:

SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';

Get the result of MONTH and YEAR.

Answer 7

The logic will be:

 SELECT * FROM objects WHERE Date LIKE '$_POST[period]-%'; 

The LIKE statement will select all lines starting with $_POST['period'] followed by a dash and day mont

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html - Additional Information

Answer 8

to get the month and year values ​​from the date column

 select year(Date) as "year", month(Date) as "month" from Projects 

Answer 9

Here is the query that I am using and it will return each record over a period as an amount.

Here is the code:

 $result = mysqli_query($conn,"SELECT emp_nr, SUM(az) FROM az_konto WHERE date BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59' GROUP BY emp_nr ASC"); echo "<table border='1'> <tr> <th>Mitarbeiter NR</th> <th>Stunden im Monat</th> </tr>"; while($row = mysqli_fetch_array($result)) { $emp_nr=$row['emp_nr']; $az=$row['SUM(az)']; echo "<tr>"; echo "<td>" . $emp_nr . "</td>"; echo "<td>" . $az . "</td>"; echo "</tr>"; } echo "</table>"; $conn->close(); ?> 

All emp_nr and the sum of the monthly hours they have accumulated are listed here.

Answer 10

Suppose you have a create_at database field where you take a value from a timestamp. You want to search by year and month from the creation date.

 YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2 

Answer 11

Yes, this works, but it only returns one row only if multiple rows are retrieved using only the selected columns. Like SELECT Title, Date FROM mytable WHERE YEAR (date) = 2017 AND MONTH (date) = 09 returns only one row.