Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post

    PHP query database based on date criteria

    Hello:

    I have the following challenge I need assistance with. I have a mysql table
    Code:
    -- 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:

    Code:
    <?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
    Last edited by mbarandao; 12-01-2010 at 06:34 PM. Reason: typo

  • #2
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    I think the main problems are the varchar format of the date_of_insert field, and the general date-time format you're using. I always use unix timestamps and int fields to save this aggravation. With that in mind and that I could be wrong, I think something like this is close to your solution:

    PHP Code:
    $date1 '2010-05-30';
    $date2 '2010-06-15';
    $res mysql_query('SELECT * FROM `billofservice` WHERE STR_TO_DATE('.$date1.', "%Y-%m-%d") > STR_TO_DATE(`date_of_insert`, "%Y-%m-%d") AND STR_TO_DATE('.$date2.', "%Y-%m-%d") < STR_DATE(`date_of_insert`, "%Y-%m-%d")');
    $total 0;
    while (
    $row mysql_fetch_assoc($res)) {
        
    $total += $row['tax'];

    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #3
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    Thanks Lamped for the response. I have modified your suggestion to include variable being passed through url. However, I'm getting a "Couldn't execute query" error. Specifically: "Error: FUNCTION shop.STR_DATE does not exist"

    here is the code:
    Code:
    <?php
    //connect to your database ** EDIT REQUIRED HERE **
    mysql_connect("localhost","xxxxxxxx","xxxxxxxxxxxx"); //(host, username, password)
    
    mysql_select_db("shop") or die("Unable to select database"); //select which database we're using
    $date1 = $_REQUEST['theDate'];
    $date2 = $_REQUEST['currentdate'];
    //echo "$date1"; echo" $date2";
    
    $query= 'SELECT * FROM `billofservice` WHERE STR_TO_DATE('.$date1.', "%Y-%m-%d") > STR_TO_DATE(`date_of_insert`, "%Y-%m-%d") AND STR_TO_DATE('.$date2.', "%Y-%m-%d") < STR_DATE(`date_of_insert`, "%Y-%m-%d")';
    $result = mysql_query($query) or die("Couldn't execute query");
    $total = 0;
    while($row = mysql_fetch_assoc($result)) {
        $total += $row['tax'];
    }  
    ?>

    Any ideas, what I'm overlooking?
    Last edited by mbarandao; 12-01-2010 at 07:50 PM. Reason: correction

  • #4
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    The last STR_TO_DATE in the query is actually STR_DATE, change that and see what happens. Yes, that was my fault

    I take no responsibility if your PC blows up.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #5
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    Thanks for the humor!

    The Last STR_TO_DATE being
    Code:
    AND STR_DATE('.$date2.', "%Y-%m-%d")
    I've changed to STR_DATE with no change in the outcome.

  • #6
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    No, I mean it should be STR_TO_DATE, not STR_DATE. Sorry, I worded it badly.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #7
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    That was it! Error disappeared, but the total is not echo'ed.

  • #8
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    Did you put echo($total); at the bottom?
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #9
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    this is the full code:
    Code:
    <?php
    //connect to your database ** EDIT REQUIRED HERE **
    mysql_connect("localhost","XXXX","XXXXX"); //(host, username, password)
    
    mysql_select_db("shop") or die("Unable to select database"); //select which database we're using
    $date1 = $_REQUEST['theDate'];
    $date2 = $_REQUEST['currentdate'];
    echo"<br/>";
    echo "Total Sales Taxes Collected For the Following Period";
    echo "<br/>";
    echo "From $date1"; echo" To $date2";
    echo"<br/>";
    echo"<br/>";
    
    $query= 'SELECT * FROM `billofservice` WHERE STR_TO_DATE('.$date1.', "%Y-%m-%d") > STR_TO_DATE(`date_of_insert`, "%Y-%m-%d") AND STR_TO_DATE('.$date2.', "%Y-%m-%d") < STR_TO_DATE(`date_of_insert`, "%Y-%m-%d")';
    $result=mysql_query($query);
    if(!$result){die("Error: ".mysql_error());
    }
    $total = 0;
    while($row = mysql_fetch_assoc($result)) {
        $total += $row['tax'];
    }  
    ?>
    Additionally, you earlier mentioned that it is best to use UNIX_TIMESTAMP for date insertion. I added the following to my record insert statement:
    Code:
    UNIX_TIMESTAMP(now()
    but I'm getting this error:
    Code:
    Error: Incorrect parameter count in the call to native function 'UNIX_TIMESTAMP'

  • #10
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    I didn't say it's best to use unix timestamps, but I do. I don't use a special database field for them either, I just use int and do date arithmetic in partly in PHP. I'm not suggesting you should do this. A datetime field instead of varchar might be better for date_of_insert though.

    At the bottom, your code isn't showing an echo, so do this:
    PHP Code:
    while($row mysql_fetch_assoc($result)) {
        
    $total += $row['tax'];
    }
    echo(
    $total); 
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #11
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    Code:
    echo($total);
    simply prints the value of $total=0. presumably my datetime field might be the reason...I'm debugging!

    Thanks for the elab on the unix timestamps

  • #12
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    If you changed it to a datetime field, the code will probably need changing, as the STR_TO_DATE shouldn't be necessary on the date_of_insert fields. You'd also need to ensure it's converted the dates correctly.

    If you haven't changed the date_of_insert field type, I tend to pop the query into phpmyadmin and debug it directly in there.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #13
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    341
    Thanks
    22
    Thanked 1 Time in 1 Post
    Yes, I did make the change --except to "date" instead of datetime, as time is not necessary for the task.

    I also went into phpmyadmin and modified the date manually...and still, no change. I'm still at it!

    For the code modification --with the date field set to "date" would I simply remove the STR_TO_DATE from the statement--or something else?

    Using the UNIX_TIMESTAMP(now() is still dishing out an error:
    Code:
    Error: Incorrect parameter count in the call to native function 'UNIX_TIMESTAMP'
    Any idea as to why?

    Here is my new record statement:
    PHP Code:
    $mysql_query=("insert billofservice (date_of_insert,clientID,invoicenum,date,servicearea, servicedesc, cost, qty, price,tax, total,paid, balancedue,laborcost,paymenttype) VALUES (UNIX_TIMESTAMP(now(),'$clientID2','$invoicenum','$date','$servicearea', '$servicedesc', '$cost', '$qty', '$price','$tax','$total','$paid', '$due','$laborcost', '$paymenttype'))"); 

  • #14
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    Just use NOW(), forget about UNIX_TIMESTAMP(). The reason for the error, btw, is you didn't close the parenthesis properly.

    If you're storing it as a date field, you don't need to use STR_TO_DATE(`date_of_insert`...) because it's already a date. You need to use STR_TO_DATE(yourphpvar...) to convert it into the same date format.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #15
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    If you still can't get it working, I'll stop giving you vague and poor advice. Give me a dump of the table from phpmyadmin, the 2 dates you're checking between and I'll debug it.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •