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?
Jon CAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Store date and time values in SQL columns defined as DATETIME.  This article shows the correct way to handle these things.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
Ray PaseurCommented:
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
0
Jon CAuthor Commented:
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?
0
Jon CAuthor Commented:
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)
0
Ray PaseurCommented:
Well, I hope you didn't pay someone to write the program that put a DATE value into the wrong format column!  I would go a little further than saying this is "not ideal."  It is not a  technically competent solution.  I would go back to the author and tell them to fix the programming, and they should do it for you at no charge.

You can make a copy of the data base table and you can use this copy for testing.  You can use ALTER TABLE on the test copy.  You may find that it works perfectly.  Since all the ALTER TABLE would do is add a column, the other columns would not be affected.  I think this strategy is worth testing.

I also think you might want to get professional help with this work.  You might use eLance or similar to hire a programmer who has knowledge of PHP and SQL.  A professional programmer would know, for example, that you need a WHERE clause to limit the results set from the query.  (I would help with that if I could, but without the CREATE TABLE statements, I would not know how to write the WHERE clause).  A professional programmer would know that you cannot depend on the MySQL extension, since PHP is doing away with MySQL support.  I think the fastest path to a solution would be to hire a professional, because the alternative -- doing it yourself -- will require you to take the time to learn all of the knowledge and develop the skills of a professional programmer.  That will take a while, trust me!

If you want to learn it yourself, this article will help you get started.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

If you expect the programming to survive for long, you will want to get off MySQL.  This article explains why you must do that and shows the path forward.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Best of luck with the project, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.