Solved

MySql query not returning expected results

Posted on 2013-10-30
27
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 7
  • +1
27 Comments
 
LVL 12

Expert Comment

by:zappafan2k2
ID: 39611237
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
ID: 39611256
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
ID: 39611261
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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:peter-cooper
ID: 39611281
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
ID: 39611295
Right.  And if a file has the date 20/11/2013, it would be displayed today.  Right???
0
 

Author Comment

by:peter-cooper
ID: 39611308
Correct :-)
0
 
LVL 12

Expert Comment

by:zappafan2k2
ID: 39611334
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 43

Expert Comment

by:Chris Stanyon
ID: 39611337
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
ID: 39611370
@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 43

Expert Comment

by:Chris Stanyon
ID: 39611402
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
ID: 39611412
@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
ID: 39611422
Can we back up here?  If you load the page/script today, what do you want to see?
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39611435
Sorry Peter, not sure I understand the relevance of the php code - where does that fit in to things?
0
 

Author Comment

by:peter-cooper
ID: 39611442
@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 43

Expert Comment

by:Chris Stanyon
ID: 39611449
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
ID: 39611464
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 43

Expert Comment

by:Chris Stanyon
ID: 39611476
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
ID: 39611491
@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
ID: 39611498
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
ID: 39611520
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 43

Expert Comment

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

Expert Comment

by:zappafan2k2
ID: 39611539
@ChrisStanyon - umm, yes!

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

Expert Comment

by:Ray Paseur
ID: 39611563
0
 

Author Comment

by:peter-cooper
ID: 39611582
But would using between in my statement still enable me to display old records? Thanks
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39611610
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
ID: 39611636
Thanks very much Chris.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39611637
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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