Joining two tables - php form to display results based on input date.

We have two tables in a database-

1) 'orderno'
    with the columns - 'JobNumber' and 'date_col'
2) 'JOBS'
    with the colums - 'JobNo' , 'Job_Title' , 'Handler'

JobNumber == JobNo
and the 'date_col' shows when the jobs were added on the system.

I want to find the code to create a PHP form where you can input a date, click submit and this would display all the jobs added onto the system that day including the Job_Title and Handler.

Here is the code for the input script:

<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


I am looking for the code that is needed for 'jobs_today.php' to display all the jobs added onto the system on the chosen date including the Job_Title and Handler?

Thank you!
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.

ArgentiCommented:
Here is the SQL query

select o.JobNumber, o.date_col, Job_Title, Handler
from orderno o
inner join jobs j on (o.JobNumber = j.JobNo)
where date_col = ?

Open in new window


now I'm working on the PHP code...
0
Ray PaseurCommented:
Please, please please read this article!  
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

If you take an hour to understand the principles, you'll be on firm ground and you will not create something like this enormously complicated HTML form.  PHP strtotime() and date() are your friends, I promise!

This was the code that answered a previous question along these lines.  I will try to show you a simpler version in a moment.

<?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

0
Ray PaseurCommented:
Please see http://www.laprbass.com/RAY_temp_jonhsl.php

It really is this simple!  You don't need the annoying dropdowns, just ask the client to provide a human-readable English-language date representation.  Like "tomorrow" or "now + 3 weeks" or "3 weeks ago."  Then once you have the date value in the ISO-8601 standard format, just put it right into your query.

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

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

    // 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 enter the date:</p>
</center>
<center>
<form>
  <input name="d" />
  </p>
    <p><span class="cent">
    <input type="submit" value="Submit" />
  </span></p>
</form>
</center>

Open in new window

Best regards, ~Ray
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

ArgentiCommented:
and here is your jobs_today.php file called by your form

(Edited to use the code snippet for the code block - makes it a little easier to see and discuss the code. ~Ray)

<?php

// PUT your connection data HERE !

$DB_SERVER = 'localhost';
$DB_NAME = 'test';
$DB_USER = 'root';
$DB_PASS = '******'; 

// opening a connection to the database
try
{
  $db = new PDO("mysql:host=".$DB_SERVER.";dbname=".$DB_NAME.";charset=utf8", $DB_USER, $DB_PASS, array(PDO::ERRMODE_EXCEPTION, PDO::FETCH_ASSOC) );
}
catch(PDOException $e)
{
    echo $e->getMessage();
}
 
$sql = 
    "select o.JobNumber, o.date_col, Job_Title, Handler
    from orderno o
    inner join jobs j on (o.JobNumber = j.JobNo)
    where date_col = ?";
  
  // preparing the query
  $stmt = $db->prepare($sql);
  
  // assembling the requested date
  $date = $_GET['year'] . '-' . $_GET['month'] . '-' . $_GET['day'];

// setting the parameter value  
$stmt->bindParam(1, $date, PDO::PARAM_STR);

// executing the query 
$stmt->execute();

// returning the resultset
$resultSet = $stmt->fetchAll();

if ( count($resultSet) == 0 ) {
    echo "No records found for date ".$date;
}
else {
    echo '<table border=1>';
    // writing the table header
    echo '<tr>';
    foreach($resultSet[0] as $key => $value) {        
        if (!is_numeric($key)) {
        echo '<TH>'.$key.'</TH>';
        }
    }
    echo '</tr>';
    // writing the rows...
    foreach($resultSet as $row) {
        echo '<tr>';
        // each field...
        for($i = 0; $i < $stmt->columnCount(); $i++) {
            echo '<td>'.$row[$i].'</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}
?>

Open in new window

jobs-today.php
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
Jon CAuthor Commented:
Perfect! Thank you!!
0
ArgentiCommented:
Thank you. But you should split the points. Ray has also contributed to the answer.
0
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
PHP

From novice to tech pro — start learning today.