PHP - How To Group My Query By Date?

Hi i have the following code

$sql= "SELECT * FROM income WHERE month(date) between '04' and '12' and year(date) between 2020 and 2020";
 

This obviously selects all the information from April 2020 to December 2020

How do i change this to add a date as well for example if i wanted to display all the information from April 6th 2020 to December 5th 2020 ?

I'm creating a line graph using ChartJs. I wanted to display my data according to the month selected by the user. For example, if the user select "2021-03" all the order that is made in the month of March will be plotted in the graph. It works well for the current month, but it does not work for the selected month. 

I use Ajax to get the data but i do not know why my graph does not shown when i click on my `loadchart();` button. Can anyone enlighten me how can i do it? Because i do not know what mistake did i made here. Any kind of explanation will be appreciated. Thanks!

This is the where i place my `loadchart()` button and also the javascript to plot the graph.

  <div class="col-md-12 form-group">
            <div class="card" style="box-shadow: 0 0 8px rgb(0,0,0,0.3)">
                <div class="row">
                   <div class="col col-lg-1"></div>
                  <div class="col-2 my-auto">
                    <input type="month" id="month" class="form-control" value="<?php echo date('Y-m'); ?>">
                  </div>

                   <div class="col-2 my-auto">
                    <button type="button" class="btn btn-info" onclick="loadchart();">
                      <i class="nc-icon nc-zoom-split"></i>
                    </button>
                  </div>
                </div>
                <div id="loadchart">
              <h6 class="text-muted text-center p-3"><i class="fa fa-line-chart"></i> Daily Gross Sales (Current Month)</h6>
              <div  class="card-body">
                <canvas id="attChart"></canvas>
              </div>
            </div>
            </div>
          </div>

        </div>
      </div>
      
    </div>
  </div>
  
  <!-- Chart JS -->
  <script src="./assets/js/plugins/chartjs.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();

  $type = CAL_GREGORIAN;
  $month = date('n'); // Month ID, 1 through to 12.
  $year = date('Y'); // Year in 4 digit 2009 format.
  $day_count = cal_days_in_month($type, $month, $year); // Get the amount of days

  //loop through all days
  for ($i = 1; $i <= $day_count; $i++) {
    $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH(NOW()) AND YEAR(order_datetime) = YEAR(NOW()) AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();

      
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; //format date
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);

?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        
        //labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
        }

        ]
      },

      options: {
        responsive: true,
        
        // Can't just just `stacked: true` like the docs say
        scales: {
          yAxes: [{
            stacked: false,
            gridLines: {
              display: true,
              color: "#dee2e6"
            },
            ticks: {
              stepSize: 100000,
              callback: function(tick) {
                return 'RM'+tick.toString();
              }
            }
          }],
          
          xAxes: [{
            stacked: false,
            gridLines: {
              display: false
            },
          }]
          
        },
        animation: {
          duration: 3000,
        },
        
        legend:{
          display: false,
        }
      }
    });
  </script>

This is my AJAX method 

   <script type="text/javascript">
    function loadchart()
    {
     $('#spin1').show();
     var month= $('#month').val();
     //alert(date);
      var data = {     // create object
        month: month,
        
      }
      $.ajax({
        method:"GET",
        data:data,
        url:"includes/loadchart.php",
        success:function(data)
        {
            $('#loadchart').html(data);
        }
      });

    }
  </script>

This is where i use to load the graph whenever a month is selected. (loadchart.php)

<?php
include '../session.php';
if(isset($_GET['month']))
{
	$months = $_GET['month'];
?>

 <h6 class="text-muted text-center p-3"><i class="fa fa-line-chart"></i> Daily Gross Sales (<?php echo $months ?>)</h6>
              <div id="loadchart" class="card-body">
                <canvas id="attChart"></canvas>
              </div>
            </div>
          </div>

        </div>
      </div>
      
    </div>
  </div>
  
  <!-- Chart JS -->
  <script src="./assets/js/plugins/chartjs.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

  <?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();

  $type = CAL_GREGORIAN; // Gregorian (localized) calendar
  $month = date('n'); // Month ID, 1 through to 12.
  $year = date('Y'); // Year in 4 digit 2009 format.
 
  $day_count = cal_days_in_month($type, $month, $year); // Get the amount of days

  //loop through all days
  for ($i = 1; $i <= $day_count; $i++) {
      $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();

      
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; //format date
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);

?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        
        //labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: '#FF0000',
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
        }

        ]
      },

      options: {
        responsive: true,
        
        // Can't just just `stacked: true` like the docs say
        scales: {
          yAxes: [{
            stacked: false,
            gridLines: {
              display: true,
              color: "#dee2e6"
            },
            ticks: {
              stepSize: 100000,
              callback: function(tick) {
                return 'RM'+tick.toString();
              }
            }
          }],
          
          xAxes: [{
            stacked: false,
            gridLines: {
              display: false
            },
          }]
          
        },
        animation: {
          duration: 3000,
        },
        
        legend:{
          display: false,
        }
      }
    });
  </script>
  
<?php	
}
?>

Hi

I'm having a problem getting a query to work.

I have a simple form with user input for start and end date with format: 2009-03-19 (todays date):

$Startdate = $_POST['date'];

This works well when something is entered into the form, and afterwards using my query:

SELECT COUNT(*) as total FROM mydb WHERE Date BETWEEN '$Startdate' AND '$EndDate' ........

Problem is if user submits the form without entering anything in the date input fields, which makes sense.

I want to check if inputs has been made, and if not set af default date, but can't make it work:

if (isset($_POST['date']) && $_POST['date'] !='') {
$Startdate = $_POST['date'];}
else { $Startdate = '1980-01-01';}

How can I set $Startdate to something that can be used in the query as below doesn't work?

$criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' );
    $likes = "";
    $url_criteria = '';
    foreach ( $criteria AS $criterion ) {
            if ( ! empty($_POST[$criterion]) ) {
                    $value = ($_POST[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
            } elseif ( ! empty($_GET[$criterion]) ) {
                    $value = mysql_real_escape_string($_GET[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
            } //var_dump($likes);
    }
    $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";

Hi,

I have database, screenshot attached. I have to print all records of attendance sort by ID and have sum group by ID. How to know or figure out when the ID changes so that i can show the total for that ID. Bare with me please and try to show me where is my mistake!

Code:

<?Php

	include('session.php');
	include('dbcon.php');
	
?>

<html>
<head>
<link rel="stylesheet" type="text/css" href="bootstrap.css">
</head>

<body>
	
	<div class="container">
		<div class="row">
			<div class="col m-auto">
				<div class="card mt-5">
					<table class="table table-bordered">
					
						<tr>

							<input type="button" onClick="window.print()" value="Print The Report"/>
							<td>OracleID</td>
							<td>Name</td>
							<td>Designation</td>
							<td>Clocking In Time</td>
							<td>Clocking Out Time</td>
							<td>Duration</td>
						
						</tr>
						
						<?php
						
							$isdone = -1;
							
							$query = "select * from attendance_records where isdone= '".$isdone."' order by OracleID";
							$result = mysqli_query($con, $query);
																			
							$query2 = "SELECT sec_to_time(SUM(timestampdiff(SECOND, ClockingInDate, ClockingOutDate))) as total from attendance_records group by OracleID";
							$result2 = mysqli_query($con, $query2);
							
							while ($row = mysqli_fetch_assoc($result))
							{
								
								$userid = $row['OracleID'];
								$name = $row['Name'];
								$des = $row['Des'];
								$clockingindate = $row['ClockingInDate'];
								$clockingoutdate = $row['ClockingOutDate'];
								$duration = $row['Duration'];
								$t=time();
								$curdate = date("d-m-Y",$t);
								$curTime = date("g:i:s A",$t);
																		
						?>
						
						<tr>
						
							<td><?php echo $userid ?></td>
							<td><?php echo $name ?></td>
							<td><?php echo $des ?></td>
							<td><?php echo $clockingindate ?></td>
							<td><?php echo $clockingoutdate ?></td>
							<td><?php echo $duration ?></td>
		
						</tr>
						
						<?php									
							
							}	
								while($row2 = mysqli_fetch_assoc($result2))
									{
										$totals= $row2['total'];
									
										echo '<td><h5>Total Attendance Time:</h5></td>';
										echo '<td>';
										echo $totals; 
										echo '</td>';
									}						
										
							
								echo '<tr>';
								echo 'Report created by ', $_SESSION['user_id2'], ' on ', $curdate, ' at ', $curTime;
								
								echo '</tr>';
							
						?>
						
					</table>
					
					<a href="home.php" width="100%">Click here to go back to Main Menu</a>
			
				</div>
			</div>
		</div>
	</div>

</body>

</html>

and screenshot of result is attached. I have the totals but not at the end of each OracleID, it is at the end of the report.

Thanks.