select records based on months

[ramiwahdan]

Hi,

I have db field with date. I want to make select statement with where clause when part of the date (month) is true. Example, i have records from 2020-2-1 until 2020-4-3, i want to select records for march only 2020-3-1 to 2020-3-31. how to do that? 

[ginerjm]

Read a SQL manual for the functions available to be used in a query.   Research!

 

Feeling magnanimous - here:

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_month

Edited April 3, 2020 by ginerjm

[ramiwahdan]

2 hours ago, ginerjm said:

Read a SQL manual for the functions available to be used in a query.   Research!

 

Feeling magnanimous - here:

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_month

I was able to do it, Thanks. Now i need to know how to count the records.

my code:

$res = $conn->query("SELECT count(*) as counter
						 ,oracleid
                         , name 
                         , clockingindate   as clockin
                    FROM attendance_records
					where isdone =-1 and Month(ClockingInDate) = $themonth and Year(ClockingInDate) = $theyear
                    ORDER BY oracleid
                    ");

			$previd = 0;	
			$thecounter = 0;
			
            foreach ($res as $row) {
				
				if($row['counter'] == 1)

it is not working, how to know how many records i have?

[Barand]

That query  should return a single record containing a count of all the records found

What does "not working" mean? Were you expecting more records?

[ramiwahdan]

8 minutes ago, Barand said:

That query  should return a single record containing a count of all the records found

What does "not working" mean? Were you expecting more records?

here is my code, I know the answer to the count(*) is 4 so it should go through the second if statement but its not.

foreach ($res as $row) {
				
				if($row['counter'] == 1)	
				{
					if ($row['oracleid'] != $previd) { 
						
						if ($previd != 0) {
							
							$thecounter = 1;
							$previd = $row['oracleid'];					
							
						}
						else{
							
							$thecounter = $thecounter + 1;
							$previd = $row['oracleid'];
							echo "<br>";
							echo $thecounter;
							
							}
					}
				}
				
				if ($row['counter'] > 1)
				{
					
						if ($row['oracleid'] != $previd) { 
							
							if ($previd != 0) {
								
								$thecounter = 1;
								$previd = $row['oracleid'];	
								echo "<br>";
								echo $thecounter;
								
							}
							else{
								
								$thecounter = $thecounter + 1;
								$previd = $row['oracleid'];
								
								}
						}
						else
						{
							$thecounter = $thecounter + 1;
							$previd = $row['oracleid'];
							
						}
				}
				
			}

if ($row['counter'] > 1) is not work, what is wrong?

i echo out the result for the count(*) and says 4.

 

[ramiwahdan]

Something was wrong with my logic. It's ok now