Count and query per date report

[abrahamgarcia27]

Hello i am working on an application where i want to count from a selected date to another date and query all entries my database right now is composed of the following

sales_id

barcode_id

student_id

type of lunch

date

 

i have created the count per day, but now dont know how to go about to query the results and the count for the month.

$query = 'SELECT COUNT(*) FROM `sales` WHERE `tlunch` = 1 AND DATE(date) = CURDATE()';
$result = mysql_query($query) or die('Sorry, we could not count the number of results: ' . mysql_error());
$free = mysql_result($result, 0);

 

has any one done something similar

[abrahamgarcia27]

i started something but i doesnt seem to be working

 

<?php
//Get required files 
require_once('auth.php');
include('header.php');
include('menu.php');
require("config.inc.php");
require("Database.class.php");

// create the $db object
$db = new Database(DB_HOST, DB_USER, DB_PASS, DB_DATABASE);

// connect to the server
$db->connect();

//dates posted from report
$todate = $_POST['todate'];
$fromdate = $_POST['fromdate'];

// using escape() and fetch_all_array()

// pullout the first 10 entries where url came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$sql = "SELECT barcode_id, student_id, tlunch, date FROM `".TABLE_SALES."`
          WHERE date = $fromdate AND $todate";
//`tlunch` = 1 AND DATE(date) = CURDATE(
// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_all_array($sql);

?>

<div class="content">
<div class="widget first">

        	<div class="table">
            <div class="head"><h5 class="iFrames">Students</h5></div>
            <table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
                <thead>
                    <tr>
                        <th>Student ID</th>
                        <th>Barcode ID</th>
                        <th>Type of Lunch</th>
                        <th>Date</th>
                       
                    </tr>
                </thead>
                <tbody>
                <?php foreach($rows as $record){
    							?>             
                    <tr class="gradeA even">
                        <td> <?php echo $record['student_id']; ?></td>
                        <td><?php echo $record['barcode_id']; ?></td>
                        <td><?php echo $record['tlunch']; ?></td>
                        <td><?php echo $record['date']; ?></td>
                                            </tr>
                    <?php }?>
                </tbody>
            </table>
        </div>
            
     </div> 
     </div>  

   <div class="fix"></div>
     </div>
     
    
<?php
//include the footer 
include('footer.php');
?>
              

[abrahamgarcia27]

i got it working with this code but now i cant query from a date to another is there a TO function in SQL

 

// pullout the first 10 entries where url came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$sql = "SELECT student_id, sales_id, barcode_id, tlunch, date FROM `".TABLE_SALES."`
          WHERE date = '$to' AND '$from'";
//`tlunch` = 1 AND DATE(date) = CURDATE(
// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_all_array($sql);

 

i tried using TO but i guess it doesnt exist 

[abrahamgarcia27]

thank i got it with this code

 

// using escape() and fetch_all_array()

// pullout the first 10 entries where url came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$sql = "SELECT student_id, sales_id, barcode_id, tlunch, date FROM `".TABLE_SALES."`
          WHERE date BETWEEN '$from' AND '$to'";
//`tlunch` = 1 AND DATE(date) = CURDATE(
// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_all_array($sql);

?>

[abrahamgarcia27]

now i am trying to run a count of those, but since i am very bad at syntax i wanted to see if anyone knows what is going wrong here

 

//Where clause and count
$query2 = 'SELECT COUNT(*) FROM `sales` WHERE `tlunch` = 2 AND (`date` BETWEEN $from AND $to)';
$result2 = mysql_query($query2) or die('Sorry, we could not count the number of results: ' . mysql_error());
$paid = mysql_result($result2, 0);

[SergeiSS]

You may use SQL-function MONTH() for it (example is for October) :

SELECT .... FROM ... WHERE .... and MONTH(`date`) = 10 

 

 

BTW why do you show your PHP code - your question is about SQL only. You give a lot of extra informaion.

 

PS. About your code:

`date` BETWEEN '$from' AND '$to'