Start Free TrialLog in
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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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
Avatar of Jon C
Jon C

ASKER

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?
Avatar of Jon C
Jon C

ASKER

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
PHP
PHP

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.

125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent