PHP query database based on date criteria

[mbarandao]

Hello:

I have the following challenge I need assistance with. I have a mysql table

-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 01, 2010 at 01:22 PM
-- Server version: 5.1.48
-- PHP Version: 5.2.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `shop`
--

-- --------------------------------------------------------

--
-- Table structure for table `billofservice`
--

CREATE TABLE IF NOT EXISTS `billofservice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dateofinsert` varchar(10) NOT NULL,
  `invoicenum` varchar(15) NOT NULL DEFAULT '',
  `servicedesc` varchar(255) NOT NULL DEFAULT '',
  `clientID` varchar(15) NOT NULL,
  `date` varchar(25) NOT NULL,
  `servicearea` varchar(255) NOT NULL DEFAULT '',
  `cost` varchar(15) NOT NULL,
  `qty` varchar(3) NOT NULL DEFAULT '',
  `price` varchar(3) NOT NULL DEFAULT '',
  `tax` varchar(10) NOT NULL,
  `total` varchar(10) NOT NULL,
  `laborcost` varchar(10) NOT NULL,
  `paid` varchar(10) NOT NULL,
  `paymenttype` varchar(20) NOT NULL,
  `Balancedue` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

--
-- Dumping data for table `billofservice`
--

INSERT INTO `billofservice` (`id`, `dateofinsert`, `invoicenum`, `servicedesc`, `clientID`, `date`, `servicearea`, `cost`, `qty`, `price`, `tax`, `total`, `laborcost`, `paid`, `paymenttype`, `Balancedue`) VALUES
(14, '2010-11-23', '16253', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', '1234dc', 'November 18, 2010', 'Suspension', '$234', '1', '', '$42.30', '$747.30', '', '$500', '', '$247.30'),
(15, '2010-11-22', '18719', 'Information updated\r\nOil changed\r\nbrakes installed', 'dg9642', 'November 18, 2010', 'Transmission', '$123', '3', '', '$31.14', '$550.14', '', '$232', '', '$318.14'),
(16, '2010-11-20', '12451', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 18, 2010', 'Transmission', '$324', '1', '', '$43.20', '$763.20', '', '$213', '', '$550.20'),
(17, '2010-10-12', '1347', 'New brakes installed\r\nOil Change Performed', 'mossa01', 'November 19, 2010', 'Other', '$234', '1', '', '$18.54', '$327.54', '', '$327.54', '', '$0.00'),
(18, '2010-09-13', '25486', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'md66894', 'November 19, 2010', 'Steering', '$123', '3', '', '$0.00', '$519.00', '', '$0.00', '', '$519.00'),
(19, '2010-07-12', '25194', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'md66894', 'November 19, 2010', 'Steering', '$234', '1', '', '$30.30', '$535.30', '$225.00', '$535.30', '', '$0.00'),
(20, '2010-11-01', '22442', 'Oil change', 'mossa01', 'November 19, 2010', 'Suspension', '$34', '1', '', '$3.84', '$67.84', '$30.00', '$67.84', 'Cash', '$0.00'),
(21, '0', '16726', 'test', 'md66894', 'November 22, 2010', 'Transmission', '$455', '4', '', '$118.20', '$2088.20', '$150.00', '$0.00', 'Select One', '$2088.20'),
(22, '0', '27128', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 22, 2010', 'Suspension', '$32', '3', '', '$10.26', '$181.26', '$75.00', '$100', 'Select One', '$81.26'),
(23, '0', '28389', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', 'mossa01', 'November 22, 2010', 'Transmission', '$32', '1', '', '$0.00', '$182.00', '$150.00', '$0.00', 'Select One', '$182.00'),
(24, '0', '4993', 'Click here to add details of today\\\\\\\\\\\\\\''s serviced', '1111va', 'November 23, 2010', 'Transmission', '$343', '1', '', '$38.58', '$681.58', '$300.00', '$324', 'Select One', '$357.58'),
(25, '0', '20821', 'information update', 'mossa01', 'November 23, 2010', 'Steering', '$435', '3', '', '$91.80', '$1621.80', '$225.00', '$0.00', 'Select One', '$1621.80'),
(26, '0', '24231', '4Click here to add details of today\\\\\\\\\\\\\\''s service4', 'mossa01', 'November 22, 2010', 'Suspension', '$0.00', '1', '', '$0.00', '$0.00', '$0.00', '$0.00', 'Select One', '$0.00'),
(27, '0', '2396', 'Click here to add details of today\\\\\\\\\\\\\\''s servicet', 'mossa01', 'November 22, 2010', 'Suspension', '$0.00', '1', '', '$0.00', '$0.00', '$0.00', '$0.00', 'Select One', '$0.00'),
(28, '0', '9535', 'Information update', 'dg9642', 'November 24, 2010', 'Steering', '$324', '2', '', '$56.88', '$1004.88', '$300.00', '$1234', 'Discover', '$-229.12'),
(29, '0', '30526', 'Information update\r\nOil change\r\nNew brakes', '7436gd', 'November 24, 2010', 'Other', '$34', '2', '', '$8.58', '$151.58', '$75.00', '$151.58', 'Cash', '$0.00'),
(30, '0', '25553', 'Oil change\r\nBrakes\r\nNew Transmission', 'mossa01', 'November 24, 2010', 'Multiple', '$4526', '1', '', '$304.32', '$5376.32', '$300.00', '$3049', 'Check', '$2327.32'),
(31, '2010-11-10', '18266', 'We are performing an oil change\r\nChanging new brakes\r\nNew tires', 'md66894', 'November 25, 2010', 'Brake', '$232', '3', '', '$50.76', '$896.76', '$150.00', '$0.00', 'Cash', '$896.76'),
(32, '2010-11-27', '15818', 'information update', 'md66894', 'November 29, 2010', 'Engine Mechanical', '$223', '3', '', '$53.64', '$947.64', '$225.00', '$32', 'AMEX', '$915.64'),
(33, '2010-11-29', '23986', 'Information update', 'md66894', 'November 29, 2010', 'Engine Mechanical', '$352', '3', '', '$81.36', '$1437.36', '$300.00', '$0.00', 'Cash', '$1437.36'),
(34, '2010-11-30', '22756', 'oil change\r\nnew brakes', 'mossa01', 'November 30, 2010', 'Air Intake', '$26', '1', '', '$3.66', '$64.66', '$35.00', '$0.00', 'Cash', '$64.66');

I want to be able to perform a sql statment from the website that takes the value of those two dates (ie: November 29, 2010 and November 01, 2010) and performs a sum calculation of column within the table called "tax". The trick is that the value of those two dates are passed to the php script via url and processed partial in the following way:

<?php
  $var = $_REQUEST['theDate'];//@$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

Any thoughts on how I can I achieve this task!
Mossa

[diafol]

I find using unix-based timestamps easier. You can store the dates as integers. Using comparison operators (=, <, > ...) is then very easy.

[quasipickle]

Or even the datetime field type. Basically how you're storing dates is the worst possible way, because you can't do simple calculations on it.

[mbarandao]

Issue resolved! Thank you for the reply. I completely restructured my code using date field and now() date stamp. My final code for the task is as follows:

mysql_select_db("shop") or die("Unable to select database"); 

$from = $_REQUEST['theDate'];
$to = $_REQUEST['currentdate'];
echo("
    <br/>
    Total Sale Taxes Collected For the Following Period
    <br/>
    From $from To $to
    <br/>
    <br/>
");

$res=mysql_query('SELECT SUM(SUBSTRING(`tax`,2)) AS `total` FROM `billofservice` WHERE STR_TO_DATE("'.mysql_real_escape_string($from).'", "%Y/%m/%d") < `date_of_insert` AND STR_TO_DATE("'.mysql_real_escape_string($to).'", "%Y/%m/%d") > `date_of_insert`');

if(!$res){
    die("Error: ".mysql_error());
}

$row = mysql_fetch_assoc($res);
echo($row['total']);

All is well!
Thanks,
Mossa