Page 1 of 2

date range selecting incorrect dates

Posted: Fri Aug 08, 2014 8:26 am
by jonnyfortis
i have a datepicker on a form and what to select dates in a range but it is returning back random dates. what am i doing wrong

Code: Select all

$from = $_POST['from'];
$to = $_POST['to'];

mysql_select_db($database_hostprop, $hostprop);
$query_rsPayments = "SELECT * FROM host_payments WHERE host_payments.payment_paid_timestamp >= '$from' AND host_payments.payment_paid_timestamp <= '$to' ORDER BY host_payments.payment_paid_timestamp";
$query_limit_rsPayments = sprintf("%s LIMIT %d, %d", $query_rsPayments, $startRow_rsPayments, $maxRows_rsPayments);
$rsPayments = mysql_query($query_limit_rsPayments, $hostprop) or die(mysql_error());
$row_rsPayments = mysql_fetch_assoc($rsPayments);
form

Code: Select all

<form id="form1" name="form1" method="post" action="search.php">
<label for="from">From</label>
<input name="from" type="text" id="from" size="10" value="<?php echo $_POST['from']; ?>" />
<label for="to">to</label>
<input name="to" type="text" id="to" size="10" value="<?php echo $_POST['to']; ?>"/>
<input type="submit" name="button" id="button" value="Filter" />
  </label>
  <a href="search.php"> 
  reset</a>
</form>
results

Code: Select all

<table width="1000" border="1" cellspacing="0" cellpadding="5">
  <tr>
    <td>Payment ID</td>
    <td>Date Paid</td>
    <td>User Name</td>
    <td>Paid</td>
    <td>&nbsp;</td>
  </tr>
  <?php do { ?>
  <tr>
    <td><?php echo $row_rsPayments['payment_id']; ?></td>
    <td><?php echo date('D, d/m/Y',strtotime($row_rsPayments['payment_paid_timestamp'])); ?></td>
    <td><?php echo $row_rsPayments['payment_userid']; ?></td>
    <td><?php echo $row_rsPayments['payment_confirm']; ?></td>
    <td>&nbsp;</td>
  </tr>
  <?php } while ($row_rsPayments = mysql_fetch_assoc($rsPayments)); ?>
</table>

Re: date range selecting incorrect dates

Posted: Fri Aug 08, 2014 8:30 am
by Celauran
What is the column type of host_payments.payment_paid_timestamp? What values to $to and $from contain?

Re: date range selecting incorrect dates

Posted: Fri Aug 08, 2014 12:16 pm
by jonnyfortis
Celauran wrote:What is the column type of host_payments.payment_paid_timestamp? What values to $to and $from contain?
the column varchar (255)

the values $to and $from will contain the input value if
<input name="from" type="text" id="from" size="10" value="<?php echo $_POST['from']; ?>" />
<input name="to" type="text" id="to" size="10" value="<?php echo $_POST['to']; ?>"/>

is that you mean?

Re: date range selecting incorrect dates

Posted: Fri Aug 08, 2014 12:40 pm
by Celauran
Oh my. Don't store dates as VARCHAR, store them as DATE or DATETIME. I don't know what the stored dates look like or if they're even uniform, but I'd recommend taking a good look and seeing if you can't convert them. If you're trying to compare dates stored as, say, "Fri, Aug. 8, 2014", you're going to have a bad time.

Re: date range selecting incorrect dates

Posted: Fri Aug 08, 2014 3:22 pm
by jonnyfortis
Celauran wrote:Oh my. Don't store dates as VARCHAR, store them as DATE or DATETIME. I don't know what the stored dates look like or if they're even uniform, but I'd recommend taking a good look and seeing if you can't convert them. If you're trying to compare dates stored as, say, "Fri, Aug. 8, 2014", you're going to have a bad time.
yes unfortunatly this database was done by someone else, the trouble is they have been stored in a couple of different formats

11/22/13
Dec 23 2013 14:35:17

so i think this will have to be converted.

if they are all the correct format should my search script work?

Re: date range selecting incorrect dates

Posted: Fri Aug 08, 2014 3:25 pm
by Celauran
If it's a mix of formats, you'll definitely need to normalize it and convert to DATETIME. The query itself isn't the problem here. Once you get the data sorted, your query should work fine.

Re: date range selecting incorrect dates

Posted: Mon Aug 11, 2014 4:37 am
by jonnyfortis
Celauran wrote:If it's a mix of formats, you'll definitely need to normalize it and convert to DATETIME. The query itself isn't the problem here. Once you get the data sorted, your query should work fine.
ok before i do this i do have a column in the database that is varchar but has a set formate,
payment_created_timestamp
the format is

Apr 25 2014 16:09:20

i am trying

dateFormat: 'M dd yy'

and searching

Code: Select all

SELECT * FROM host_payments WHERE host_payments.payment_created_timestamp >= '$from%' AND host_payments.payment_created_timestamp <= '$to%' ORDER BY host_payments.payment_created_timestamp
would this work or do i really need to adjust the DB. its just there are over 44 pages of payments

Re: date range selecting incorrect dates

Posted: Mon Aug 11, 2014 5:46 am
by Celauran
It's going to do a string compare, so Feb > Apr.

Re: date range selecting incorrect dates

Posted: Mon Aug 11, 2014 5:49 am
by Celauran
You could try doing something with STR_TO_DATE

Re: date range selecting incorrect dates

Posted: Tue Aug 26, 2014 4:17 am
by jonnyfortis
i am having issues again with the search range. it is working for some dates for example search between 08/01/14 (1st Aug 2014) - 08/09/14(9th August 2014) but any other dates for example July 01, 2014(07/01/14) and July 31, 2014(07/31/14) is is showing the correct dates but including 2013 dates aswell

all the dates are stored in the correct format month then date then year example July 03, 2013(07/03/13)

the form is

Code: Select all

      <form id="form1" name="form1" method="post" action="payments-list-search.php">
<label for="from">From</label>
<input name="from" type="text" id="from" size="10" value="<?php echo $_POST['from']; ?>" />
<label for="to">to</label>
<input name="to" type="text" id="to" size="10" value="<?php echo $_POST['to']; ?>"/>
<input type="submit" name="button" id="button" value="Filter" />
  </label>
  <a href="payments-list-search.php"> 
  reset</a>
      </form>
and the mysql is

Code: Select all

$from = $_POST['from'];
$to = $_POST['to'];

mysql_select_db($database_hostprop, $hostprop);
$query_rsPayment = "SELECT * FROM host_payments, plus_signup, host_editprop WHERE plus_signup.userid = host_payments.payment_userid AND host_editprop.prop_id = plus_signup.prop_id AND plus_signup.userid !='page1' AND plus_signup.userid !='page2' AND plus_signup.userid !='page3' AND host_payments.payment_transaction_status !='Awaiting Payment' AND host_payments.payment_transaction_status !='CANCELLED' AND host_payments.payment_type !='deposit' AND host_payments.payment_paid_timestamp NOT LIKE '%2012%' AND host_payments.payment_paid_timestamp >= '$from' AND host_payments.payment_paid_timestamp <= '$to' ORDER BY host_payments.payment_id ASC";
$rsPayment = mysql_query($query_rsPayment, $hostprop) or die(mysql_error());
$row_rsPayment = mysql_fetch_assoc($rsPayment);
$totalRows_rsPayment = mysql_num_rows($rsPayment);

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE host_payments SET payment_weeks=%s WHERE payment_id=%s",
                       GetSQLValueString($_POST['payment_weeks'], "text"),
                       GetSQLValueString($_POST['payment_id'], "int"));

  mysql_select_db($database_hostprop, $hostprop);
  $Result1 = mysql_query($updateSQL, $hostprop) or die(mysql_error());
}
i am asking to ignore 2012 as this is not required bu in the DB and some userID as these are test

thanks in advance

Re: date range selecting incorrect dates

Posted: Tue Aug 26, 2014 6:20 am
by Celauran
Have the fields been converted to DATETIME yet?

Re: date range selecting incorrect dates

Posted: Tue Aug 26, 2014 6:44 am
by jonnyfortis
Celauran wrote:Have the fields been converted to DATETIME yet?
no. i didnt know if they was i would loose the format that i have inputted

Re: date range selecting incorrect dates

Posted: Tue Aug 26, 2014 6:47 am
by Celauran
Then you're going to keep having this problem. It's comparing the dates as strings because they're stored as strings. 72113 is greater than 70114 and less than 73114. MySQL is returning the results you're asking for.

Re: date range selecting incorrect dates

Posted: Tue Aug 26, 2014 6:58 am
by jonnyfortis
Celauran wrote:Then you're going to keep having this problem. It's comparing the dates as strings because they're stored as strings. 72113 is greater than 70114 and less than 73114. MySQL is returning the results you're asking for.
oh that makes sense.
if i change to column to DATETIME will this effect the content of the column (dates)

Re: date range selecting incorrect dates

Posted: Tue Aug 26, 2014 7:07 am
by Celauran
It might. I'd definitely make a backup first. Maybe add a second column and run an update across the table using STR_TO_DATE or something to convert them to valid DATETIME values (or just DATE if that's all you need). You could then compare a sample of rows to make sure the values aligned. If they do, delete the old column and rename the new.