So either I start a complete new thing, or we go through it and trim out whats not needed.I'd recommend the "starting fresh" route.
The pop-up date picker can be added next without much trouble; for now, it's not strictly necessary.
Let's start with just the essentials of submitting the form, processing it, querying the database, and displaying the results:
(untested; I don't have your DB)
<?php
// let's rearrange the general flow of your script:
// do all the PHP first; output the HTML when you're done
// (and the function definitions are at the end, out-of-the-way).
// I'm also using the mysqli class instead of the (outdated) mysql extension
// in this example, we'll handle everything from the same script.
## Basics ##
// where we are.
const this_script_URL = '/URL/to/this/page.php';
// database credentials.
const DBhost = 'your-database-hostname';
const DBuser = 'your-database-username';
const DBpass = 'your-database-password';
const DBname = 'database-we-should-use';
## Logic ##
// this is where we decide what to do and tell PHP to do it.
if( empty( $_POST['submit'] ) ){
// the form was not submitted. Display the form.
$OUTPUT = showForm();
}else{
// the form was submitted. get the dates requested.
if( ($dates = processForm()) && $dates !== false ){
// we got valid dates from the form submission.
// query the database (make sure we've connected okay first)
if( ($DB = connectToDB()) && !mysqli_connect_error() ){
$result = queryDatabase( $DB,$dates );
// and format the results as HTML to be output.
$OUTPUT = showResults( $result,$dates );
// close the database connection
$DB->close();
}else{
// could not connect to the database.
$OUTPUT = showError( '...could not connect to the database.<br>'.mysqli_connect_error() );
}
}else{
// the form submission was not valid.
$OUTPUT = showError( '...the form submission was not valid.' );
}
}
## All Done! ##
// not all servers specify UTF-8 by default (punk servers!)
// the implication here is that *you* should be taking it upon yourself to use UTF-8 *everywhere* :)
header( "Content-Type: text/html; charset=utf-8" );
// send the HTML page and end PHP execution
exit( showPage( $OUTPUT ) );
## Function Definitions ##
// $error is any error message you wish to show (leave it empty if there are no errors).
// returns the HTML markup for your form.
function showForm( $error=false ){
// the form submits to itself.
$action = this_script_URL;
// this is your form.
$_form = <<< HTML
<form action="$action" method="post">
<p>Start Date: <input type="datetime" name="startdate"></p>
<p>End Date: <input type="datetime" name="enddate"></p>
<p><input type="submit" value="Search Dates"></p>
</form>
HTML
;
// if there's an error message, add it.
if( $error ){
$_form = '<p class="error">'.$error.'</p>'.$_form;
}
// return it.
return $_form;
}
// validates and sanitizes the form submission.
// returns timestamps for start and end date/times if successful; false means that something is wrong.
// *NOTE: using strtotime() is quick and easy to demonstrate, but because of the way the function works,
// date/times *before* Jan 1., 1970 *might not work* (depending on your server),
// and date/times *before* Fri, 13 Dec 1901 20:45:54 OR *after* Tue, 19 Jan 2038 03:14:07 *DEFINITELY won't work.*
// Probably not a dealbreaker right now, but other methods (e.g., the DateTime class) are more [past|future]proof.
function processForm(){
// both startdate and enddate are required
if( !empty( $_POST['startdate'] ) && !empty( $_POST['enddate'] ) ){
// convert start and end to unix timestamps (this is just a quick way to validate them as dates)
$start = strtotime( $_POST['startdate'] );
$end = strtotime( $_POST['enddate'] );
// if $start or $end are false, that means they were not valid dates* (see note above!!!)
if( $start !== false && $end !== false ){
return array( $start,$end );
}
}
// if we get this far, something went wrong
return false;
}
// returns a connection to your DB, using mysqli
function connectToDB(){
return new mysqli( DBhost,DBuser,DBpass,DBname );
}
// $DB is the database connection object (we already made sure it was successful in the Logic portion of the code)
// $dates is an array holding the start and end dates
// returns the result of the database query
function queryDatabase( $DB,$dates ){
// get the start & end dates
list( $start,$end ) = $dates;
// prepare SQL query
// note I'm now using the mysql function FROM_UNIXTIME() because we're now passing a unix timestamp
// (they don't need to be escaped (sanitized) because unix timestamps have numeric characters only)
$SQL = "SELECT vtiger_account.accountid, vtiger_crmentity.crmid, vtiger_account.account_no, vtiger_crmentity.createdtime
FROM leonetcrm.vtiger_account
INNER JOIN leonetcrm.vtiger_crmentity
ON vtiger_account.accountid=vtiger_crmentity.crmid
WHERE vtiger_crmentity.createdtime >= FROM_UNIXTIME('$start', '%Y-%m-%d %H:%i:%s')
AND vtiger_crmentity.createdtime <= FROM_UNIXTIME('$end', '%Y-%m-%d %H:%i:%s')
ORDER BY vtiger_crmentity.createdtime";
// query the database
$result = $DB->query( $SQL );
// return the result
return $result;
}
// $result is the results of the database query
// returns the results formatted as HTML
function showResults( $result,$dates ){
// get the start and end dates
list( $start,$end ) = $dates;
// what if there was an error?
if( $result === false ){
return showError( '...the database query failed.<br>'.$DB->error() );
}
// what if there were no results?
if( $result->num_rows === 0 ){
return showError( '...no records found between '.date( 'Y-m-d H:i:s',$start ).' and '.date( 'Y-m-d H:i:s',$end ) );
}
// loop through results and build rows for your table.
while( $r = $result->fetch_row() ){
// get each field
list( $accountID,$accountNo,$crmID,$created ) = $r;
// use htmlspecialchars to make sure results are displayed as text
$accountID = htmlspecialchars( $accountID );
$accountNo = htmlspecialchars( $accountNo );
$crmID = htmlspecialchars( $crmID );
$created = htmlspecialchars( $created );
// assign the new table row to an array of rows
$row[] = "<tr><td>$accountID</td><td>$accountNo</td><td>$crmID</td><td>$created</td></tr>";
}
// make the array of rows into one long string
$rows = implode( "\n",$row );
// this is your table.
$table = <<< HTML
<table><tbody>
<tr><th>Account ID</th><th>Account Number</th><th>CRMID</th><th>Time Created</th></tr>
$rows
</tbody></table>
HTML
;
// return the table.
return $table;
}
// $message is the error message to show
// returns the error message, along with the form so the user can try again.
function showError( $message='' ){
// was there a message?
if( empty( $message ) || !is_string( $message ) ){
$message = '...something weird happened.';
}else{
// make safe for display
$message = htmlspecialchars( $message );
}
// return error message with form
return showForm( $message );
}
function showPage( $content ){
// this is your whole page.
$_html = <<< HTML
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Search By Dates</title>
</head>
<body>
<h1>Search by Dates</h1>
<div id="output">$content </div>
</body>
</html>
HTML
;
// all done
return $_html;
}
?>