Avatar of Jon C
Jon C asked on

Having trouble with Searching a MySQL double to date conversion

What I am trying to do is search a database  and display all the jobs that have been put on the system today.

I have an input page where you select the date:
<title>Jobs Today</title><center>
<h1>Jobs on the System Today</h1>
<p>Please select the date:</p>
</center>
<center>
<form action="jobs_today.php" method="get">
  <p>Day:
    <select name="day" id="day">
      <option selected="selected">01</option>
      <option>02</option>
      <option>03</option>
      <option>04</option>
      <option>05</option>
      <option>06</option>
      <option>07</option>
      <option>08</option>
      <option>09</option>
      <option>10</option>
      <option>11</option>
      <option>12</option>
      <option>13</option>
      <option>14</option>
      <option>15</option>
      <option>16</option>
      <option>17</option>
      <option>18</option>
      <option>19</option>
      <option>20</option>
      <option>21</option>
      <option>22</option>
      <option>23</option>
      <option>24</option>
      <option>25</option>
      <option>26</option>
      <option>27</option>
      <option>28</option>
      <option>29</option>
      <option>30</option>
      <option>31</option>
    </select>
  Month:
    <select name="month" id="month">
      <option selected="selected">01</option>
      <option>02</option>
      <option>03</option>
      <option>04</option>
      <option>05</option>
      <option>06</option>
      <option>07</option>
      <option>08</option>
      <option>09</option>
      <option>10</option>
      <option>11</option>
      <option>12</option>
    </select>
  Year:
    <select name="year" id="year">
      <option selected="selected">2013</option>
      <option>2014</option>
    </select>
  </p>
    <p><span class="cent">
    <input type="submit" name="submit" value="Submit" />
  </span></p>
</form>
</center>

Open in new window


When clicking submit I would like the page 'jobs_today.php' to display everything from the table 'JOBS' just from the date selected.

However the date is stored in the table 'orderno' in a the format double which would need to be converted.

So far my 'jobs_today.php' looks like this:

<?php 
$host_name = 'localhost'; 
$user_name = 'my_username'; 
$pass_word = 'password'; 
$database_name = 'my_database';  

$conn = mysql_connect($host_name, $user_name, $pass_word) or die ('Error connecting to mysql'); 
mysql_select_db($database_name); 

$query = "SELECT * FROM orderno WHERE date('1899-12-30 00:00:00'+ INTERVAL User5 * 24*3600 SECOND)";

$result=mysql_query($query);

$num=mysql_num_rows($result); 

?>

Open in new window


Any ideas how I can get the results to display from JOBS after selecting the day?
MySQL ServerPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Ray Paseur

Store date and time values in SQL columns defined as DATETIME.  This article shows the correct way to handle these things.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
Ray Paseur

You will want to correct the column definition in the table.  You can use ALTER TABLE to add a new column that will hold the correctly formatted DATETIME value.  Then you can run a one-time script that will read the existing values and convert them to the ISO-8601 format for dates, updating each row as it goes.

This script will show you how to handle the external input.
http://www.laprbass.com/RAY_temp_jonhsl.php

<?php // RAY_temp_jonhsl.php
error_reporting(E_ALL);

// IF THE FORM HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // MAKE A TIMESTAMP
    $ts = strtotime
    ( $_GET['year']
    . '-'
    . $_GET['month']
    . '-'
    . $_GET['day']
    )
    ;

    // IF THE TIMESTAMP IS USABLE
    if ($ts)
    {
        $iso = date('Y-m-d', $ts);
    }
    else
    {
        $iso = date('Y-m-d');
        echo 'DATE INPUT IS NOT USABLE - USING "TODAY" INSTEAD';
    }

    // SHOW THE DATA THAT WOULD BE USED IN THE QUERY STRING
    echo "<br/>USE <b>$iso</b> IN THE QUERY";
}

?>
<title>Jobs Today</title><center>
<h1>Jobs on the System Today</h1>
<p>Please select the date:</p>
</center>
<center>
<form>
  <p>Day:
    <select name="day" id="day">
      <option selected="selected">01</option>
      <option>02</option>
      <option>03</option>
      <option>04</option>
      <option>05</option>
      <option>06</option>
      <option>07</option>
      <option>08</option>
      <option>09</option>
      <option>10</option>
      <option>11</option>
      <option>12</option>
      <option>13</option>
      <option>14</option>
      <option>15</option>
      <option>16</option>
      <option>17</option>
      <option>18</option>
      <option>19</option>
      <option>20</option>
      <option>21</option>
      <option>22</option>
      <option>23</option>
      <option>24</option>
      <option>25</option>
      <option>26</option>
      <option>27</option>
      <option>28</option>
      <option>29</option>
      <option>30</option>
      <option>31</option>
    </select>
  Month:
    <select name="month" id="month">
      <option selected="selected">01</option>
      <option>02</option>
      <option>03</option>
      <option>04</option>
      <option>05</option>
      <option>06</option>
      <option>07</option>
      <option>08</option>
      <option>09</option>
      <option>10</option>
      <option>11</option>
      <option>12</option>
    </select>
  Year:
    <select name="year" id="year">
      <option selected="selected">2013</option>
      <option>2014</option>
    </select>
  </p>
    <p><span class="cent">
    <input type="submit" name="submit" value="Submit" />
  </span></p>
</form>
</center>

Open in new window

HTH, ~Ray
ASKER
Jon C

Thank you for your reply.

Unfortunately I can not change the table structure because the database is part of a program that has been written for us.

I know this is not ideal but is there a way I can keep the table structure and date format the same, input the date as on the first form and display all the jobs (from the JOBS table) that were put on the system that day in 'jobs_today.php'

Please Advise?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Jon C

This is the code for jobs_today.php:

<?php 
error_reporting(E_ALL);

ini_set('display_errors', 1);

$host_name = 'hostname'; 
$user_name = 'username'; 
$pass_word = 'password'; 
$database_name = 'Hire';  

$conn = mysql_connect($host_name, $user_name, $pass_word) or die ('Error connecting to mysql'); 
mysql_select_db($database_name); 


error_reporting(E_ALL);

// IF THE FORM HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // MAKE A TIMESTAMP
    $ts = strtotime
    ( $_GET['year']
    . '-'
    . $_GET['month']
    . '-'
    . $_GET['day']
    )
    ;

    // IF THE TIMESTAMP IS USABLE
    if ($ts)
    {
        $iso = date('Y-m-d', $ts);
    }
    else
    {
        $iso = date('Y-m-d');
        echo 'DATE INPUT IS NOT USABLE - USING "TODAY" INSTEAD';
    }

    // SHOW THE DATA THAT WOULD BE USED IN THE QUERY STRING
    echo "<br/>JOBS PUT ON THE SYSTEM <b>$iso</b>";
}


$query =  "SELECT `JOBS`.`JobNo`, `JOBS`.`Job_Title`, `JOBS`.`Handler`, `JOBS`.`Client`, `JOBS`.`Status`, `JOBS`.`Name`, `JOBS`.`Due Out`, `JOBS`.`Due Back`
FROM `JOBS`
LEFT JOIN `orderno` ON `orderno`.`JobNumber` = `JOBS`.`JobNo`";

$result=mysql_query($query);

$num=mysql_num_rows($result); 

?>

<table border="1" cellspacing="2" cellpadding="2">
<tr>
<td><b><font face="Arial, Helvetica, sans-serif">Job Number</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Job Title</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Handler</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Client</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Status</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Name</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Due Out</font></b></td>
<td><b><font face="Arial, Helvetica, sans-serif">Due Back</font></b></td>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"JobNo");
$f2=mysql_result($result,$i,"Job_Title");
$f3=mysql_result($result,$i,"Handler");
$f4=mysql_result($result,$i,"Client");
$f5=mysql_result($result,$i,"Status");
$f6=mysql_result($result,$i,"Name");
$f7=mysql_result($result,$i,"Due Out");
$f8=mysql_result($result,$i,"Due Back");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
</tr>


<?php



$i++;
}
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";
mysql_close();
?>

Open in new window


This now displays all jobs from the table JOBS.
I want to be able to filter the jobs by date - choose a date from the input page, this would then display just the jobs that were added on that day on jobs_today.php.

The date is stored as a double format in the 'orderno' table.(I cant change that the date is a double format because the database come from a program that was written for us)
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question