Solved

MySql query not returning expected results

Posted on 2013-10-30
27
358 Views
Last Modified: 2013-10-30
I have a piece of code that queries a MySql db and return results based on the date. What is supposed to happen is that if there is a date in the destroy_date field that contains todays date - 1 month, there is another piece of code that should add 1 month to the displayed results to warn a user that this file must be destroyed on that date. For some reason this isn't happening and is displaying older dates and not displaying the expected results.

As an example, let us say that a file has a destroy date of 30/10/2013. I need to display 1 month advance notice that this file will be destroyed on that date. So I need to display on the 30/09/2013 that this file will be destroyed on the 30/10/2013.

I would be grateful if someone could point out my error or if there is more info needed, then I shall be happy to include such. Many thanks

MySql query

$query = "SELECT * FROM boxes WHERE customer = '$_SESSION[kt_idcode_usr]' AND destroy_date <= DATE(NOW() - INTERVAL 1 MONTH) AND destroy_date != '0000-00-00' AND status = 1 order by destroy_date desc"; 

Open in new window


php code to add 1month to date

$rowdate = date("d/m/Y", strtotime($row['destroy_date'] . ' + 1 MONTH'));

Open in new window

0
Comment
Question by:peter-cooper
  • 9
  • 9
  • 7
  • +1
27 Comments
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
This is confusing.  So the destroy_date field in the database isn't really the destroy date, it's the date one month prior to the actual destroy date?  Otherwise, wouldn't you be comparing today's date with the date one month earlier from that field?

Can it be assumed that the PHP code in your second code block is only executed when $row['destroy_date'] = today's date?

I notice that you are using the European date formatting (d/m/y).  Could it simply be that your server is expecting m/d/y?  For instance, when I run
php -r "echo date('d-m-Y', strtotime('30/10/2013 + 1 month'));"

Open in new window

on my server, it outputs 31-12-1969.  If you don't have command line access on your server, can you try the above code block in a PHP file and see what the output is?
0
 

Author Comment

by:peter-cooper
Comment Utility
Hi
It is confusing. The actual date in the db is 30/10/2013 which is today.  On 30/09/2013 it should displayed the file which has a date as above. It is just working either 1 month advance or arrears. Depends on your view.  It is outputting correct format. Thanks
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
In other words, today is October 30th, 2013.  Today you want to see all record which have a destroy date of November 30th, 2013.  Correct?
0
 

Author Comment

by:peter-cooper
Comment Utility
Nope, if a file had todays date, then it would have been displayed 30/09/2013 1 months notice.

Think of it like this. You have account that needs to renew on 30/10/2013 but you need 30 days notice to renew account. Does that make sense. Thanks
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
Right.  And if a file has the date 20/11/2013, it would be displayed today.  Right???
0
 

Author Comment

by:peter-cooper
Comment Utility
Correct :-)
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
That's what I said!

Okay, let's look at this.  Never mind what should have displayed on September 30th.  If you run the script today, what rows do you want to show?  Ones with a destroy date of one month from today, right?

Running this script right now, this part of your SQL code
destroy_date <= DATE(NOW() - INTERVAL 1 MONTH)
is going to return rows where the destroy date is 30/09/2013 or *earlier*.  Is that really what you want?
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Shouldn't your query be adding one month, not subtracting it. If you have a destroy date of 30 November 2013, then today you need to pull records today where destroy date is less than now + 1 month!
0
 

Author Comment

by:peter-cooper
Comment Utility
@Chris

This is getting confusing isn't it. So the query needs to be AND destroy_date <= DATE(NOW())? I have removed interval. My php code adds one month for display purposes. Do I need to add + 1 month to Mysql query?

@zappafan2k2

To be honest, I am not so sure anymore. All I am trying to do is, if a date is set of 30/11/2013 then display today (1month in advance) that it expires on that date.

Thanks
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
If your database stores a DestroyDate of 30 November, then you need to see this record on the 30 October, so your query needs to be (in laymans terms)

SELECT records FROM myTable WHERE DestroyDate IS LESS THAN THAN (Today + 1 Month).

Now if you run that query today, you will get:

SELECT records FROM myTable WHERE DestroyDate IS LESS THAN THAN (30 October +1 Month = 30 November).

Hence the need to ADD 1 month to the current date, not subtract it
0
 

Author Comment

by:peter-cooper
Comment Utility
@Chris

So the php code I supplied

$rowdate = date("d/m/Y", strtotime($row['destroy_date'] . ' + 1 MONTH'));

Open in new window


would become:

$rowdate = date("d/m/Y", strtotime($row['destroy_date']));

Open in new window


Is that correct. Thanks
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
Can we back up here?  If you load the page/script today, what do you want to see?
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Sorry Peter, not sure I understand the relevance of the php code - where does that fit in to things?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:peter-cooper
Comment Utility
@Chris In that piece of code I added +1 month to display advance date based on my old code. That is now redundant right? Thanks

@zappafan2k2

30/11/2013 Thanks
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Peter - the SQL Statement is doing the Date calculation directly, so I don't think there's a need for your PHP code:

$query = "
SELECT *
FROM boxes 
WHERE customer = '$_SESSION[kt_idcode_usr]' 
   AND destroy_date <= DATE(NOW() + INTERVAL 1 MONTH)
   AND destroy_date != '0000-00-00'
   AND status = 1
ORDER BY destroy_date DESC";

Open in new window

0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
If you ONLY want to see records that have the destroy date of EXACTLY one month from today, all you need is
WHERE customer = '$_SESSION[kt_idcode_usr]' 
   AND destroy_date = date(now() + interval 1 month)
   AND status = 1

Open in new window

You shouldn't need the other where clause.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
I think a better approach might be to select records that have a destroydate BETWEEN today and 1 month away. That way, whenever you run the query you will see records for the coming month.

If you only select based on the exact day, then tomorrow you may have missed a destroydate of 30 November. The other reason to use BETWEEN would be to prevent you pulling records from last year unless that's what you need or you delete records after the destroydate
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
@ChrisStanyon, that's why I asked what he wanted to see, and why I worded my last comment the way I did.  I agree with you, and I would do it differently.

Personally, I would have my where clause be something like
and destroy_date between date(now()) and date(now() + interval 1 month)

Open in new window

That would show everything that will expire between today and one month from today.
0
 

Author Comment

by:peter-cooper
Comment Utility
I need to keep old records there until they are destroyed.  For example, if a file should have been destroyed on the 20/08/2013 it is important for a number reasons why they cannot be removed until actioned. Is our code still correct for that scenario? Thanks
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
I guess that's the importance of the status = 1 clause?  Then I think your where clause should be
WHERE customer = '$_SESSION[kt_idcode_usr]'
    and destroy_date <= date(now() + interval 1 month)
    and destroy_date != '0000-00-00'
    and status = 1

Open in new window

Side question: does MySQL really allow != instead of <>?  I generally use PostgreSQL...
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
@ zappafan2k2 - isn't that exactly what I've already posted?
0
 
LVL 12

Expert Comment

by:zappafan2k2
Comment Utility
@ChrisStanyon - umm, yes!

Sorry.  I didn't look closely enough.  :-)
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
0
 

Author Comment

by:peter-cooper
Comment Utility
But would using between in my statement still enable me to display old records? Thanks
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
No. The idea of using BETWEEN was to get records between NOW and 1 Month away. If that's not what you need, and you need to include older (records with a DestroyDate prior to today) then use the query I posted earlier:

$query = "
SELECT *
FROM boxes 
WHERE customer = '$_SESSION[kt_idcode_usr]' 
   AND destroy_date <= DATE(NOW() + INTERVAL 1 MONTH)
   AND destroy_date != '0000-00-00'
   AND status = 1
ORDER BY destroy_date DESC";

Open in new window

0
 

Author Closing Comment

by:peter-cooper
Comment Utility
Thanks very much Chris.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
This should help you set up the SSCCE so you can experiment with your queries.

<?php // RAY_temp_peter_cooper.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES MANY OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF TEST DATA
$test_data_arrays = array
( array( "fname" => "Walter" , "fdate" => "Yesterday"          )
, array( "fname" => "Ray"    , "fdate" => "Today"              )
, array( "fname" => "Bill"   , "fdate" => "2 weeks ago"        )
, array( "fname" => "Ray"    , "fdate" => "Last Wednesday"     )
, array( "fname" => "John"   , "fdate" => "September 15, 2011" )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, fdate DATETIME    NOT NULL DEFAULT '0000-00-00 00:00:00'
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_data_arrays as $thing)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($thing['fname']);

    // CONVERT THE DATES TO ISO-8601 FORMAT
    $my_date = date('c', strtotime($thing['fdate']));

    // CONSTRUCT THE QUERY USING THESE VARIABLES
    $sql = "INSERT INTO my_table ( fname, fdate ) VALUES ( '$safe_fn', '$my_date' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
	{
	    $err
	    = 'QUERY FAILURE:'
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    . ' QUERY: '
	    . $sql
	    ;
	    trigger_error($err, E_USER_ERROR);
	}


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $my_date</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT fname, fdate FROM my_table ORDER BY fdate";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// COMPUTE NOTIFICATION DATES
$res->data_seek(0);
while ($row = $res->fetch_object())
{
    $noted = date('r', strtotime($row->fdate . ' - 1 Month'));
    echo PHP_EOL . $row->fname . " WILL BE NOTIFIED ON $noted";
}

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
The viewer will learn how to dynamically set the form action using jQuery.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now