php - How Can I Get Query Results for a Date Range with a MySQLi prepared statement -
i trying data mysqli prepared statement data date range selected date picker on top of form. worked perfect until try , add date range part it. here code form page:
<?php include_once 'includes/db_connect.php'; include_once 'includes/functions.php'; sec_session_start(); ?> <!doctype html> <html class="no-js" lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="x-ua-compatible" content="ie=edge"> <title>welcome | mountain , alpine loan centers</title> <meta name="description" content="mountain , alpine loan centers"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="apple-touch-icon" href="apple-touch-icon.png"> <!-- place favicon.ico in root directory --> <link rel="stylesheet" href="css/normalize.css"> <link rel="stylesheet" href="css/main.css"> <script src="js/vendor/modernizr-2.8.3.min.js"></script> <script type="text/javascript" src="js/sha512.js"></script> <script type="text/javascript" src="js/forms.js"></script> <style type="text/css"> cal { display: none; } cal.main { width: 100%; } cal.main td { border-width: 1px 1px 1px 1px; padding: 1px 1px 1px 1px; font-family: verdana,arial, helvetica, sans-serif; font-size: 11px; } cal.main th { border-width: 1px 1px 1px 1px; padding: 0px 0px 0px 0px; } cal.main a{text-decoration: none;} </style> <script language="javascript" src="js/date-picker6.js"></script> </head> <body> <!--[if lt ie 8]> <p class="browserupgrade">you using <strong>outdated</strong> browser. please <a href="http://browsehappy.com/">upgrade browser</a> improve experience.</p> <![endif]--> <!-- add site or application content here --> <div align="center"> <img src="../img/logo4_black_red.jpg" height="126" width="266"> <nav id="nav01"></nav></div> <div align="center"><br><br><br> <form action="../includes/test.inc.php" method="get"> <table border="0" cellspacing="1"> <tr> <table id="cal.main"> <div id="cal"> dates:<br> from: <input type="text" name="t1" id="t1"> <img src="calendar.jpg" onclick="start_cal('t1','calendar1');";> <div id="calendar1" style="position: relative;top: 40px;left: -220px;z-index:1;"></div> to: <input type="text" id="t2" name="t2"> <img src="calendar.jpg" onclick="start_cal('t2','calendar2');";> <div id="calendar2" style="position: relative; top: 40px;left: 220px;z-index:1;"></div> </div></table> </br></br> date set: <input type="text" name="set_date" id="set_date" /><br> result: <input type="text" name="result" id="result" /><br> employee: <input type="text" name="employee" id="employee" /><br> project: <input type="text" name="project" id="project" /><br> source: <input type="text" name="source" id="source" /><br> appointment date: <input type="text" name="appt_date" id="appt_date" /><br> branch: <input type="text" name="branch" id="branch" /><br> first name: <input type="text" name="fname" id="fname" /><br> last name: <input type="text" name="lname" id="lname" /><br> last four: <input type="text" name="last_four" id="last_four" /><br> phone: <input type="text" name="phone" id="phone" /><br> city: <input type="text" name="city" id="city" /><br> state: <input type="text" name="state" id="state" /><br> zip: <input type="text" name="zip" id="zip" /><br> <input type="submit" value="submit" /> </tr> </table> </form> </div> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script> <script>window.jquery || document.write('<script src="js/vendor/jquery-1.11.3.min.js"><\/script>')</script> <script src="js/plugins.js"></script> <script src="js/main.js"></script> <script src="../js/script.js"></script> <!-- google analytics: change ua-xxxxx-x site's id. --> <script> (function(b,o,i,l,e,r){b.googleanalyticsobject=l;b[l]||(b[l]= function(){(b[l].q=b[l].q||[]).push(arguments)});b[l].l=+new date; e=o.createelement(i);r=o.getelementsbytagname(i)[0]; e.src='https://www.google-analytics.com/analytics.js'; r.parentnode.insertbefore(e,r)}(window,document,'script','ga')); ga('create','ua-xxxxx-x','auto');ga('send','pageview'); </script> </body> </html> and here code form processing page:
<?php include_once 'db_connect.php'; include_once 'psl-config.php'; session_start(); $error_msg = ""; if (isset($_post['t1'])) $from = $_post['t1']; if (isset($_post['t2'])) $to = $_post['t2']; if (isset($_get['subject'])) $subject = $_get['subject']; if (isset($_get['set_date'])) $set_date = $_get['set_date']; if (isset($_get['set_time'])) $set_time = $_get['set_time']; if (isset($_get['result'])) $result = $_get['result']; if (isset($_get['employee'])) $employee = $_get['employee']; if (isset($_get['project'])) $employee = $_get['project']; if (isset($_get['source'])) $source = $_get['source']; if (isset($_get['appt_date'])) $appt_date = $_get['appt_date']; if (isset($_get['branch'])) $branch = $_get['branch']; if (isset($_get['fname'])) $fname = $_get['fname']; if (isset($_get['lname'])) $lname = $_get['lname']; if (isset($_get['last_four'])) $last_four = $_get['last_four']; if (isset($_get['phone'])) $phone = $_get['phone']; if (isset($_get['city'])) $city = $_get['city']; if (isset($_get['state'])) $state = $_get['state']; if (isset($_get['zip'])) $zip = $_get['zip']; if (isset($_get['monthly_net'])) $monthly_net = $_get['monthly_net']; if (isset($_get['job_time'])) $job_time = $_get['job_time']; $query = $mysqli->prepare(" select subject, set_date, set_time, result, employee, project, source, appt_date, branch, fname, lname, last_four, phone, city, state, zip, monthly_net, job_time appointments set_date concat('%', ?, '%') , result concat('%', ?, '%') , employee concat('%', ?, '%') , project concat('%', ?, '%') , source concat('%', ?, '%') , appt_date concat('%', ?, '%') , branch concat('%', ?, '%') , fname concat('%', ?, '%') , lname concat('%', ?, '%') , last_four concat('%', ?, '%') , phone concat('%', ?, '%') , city concat('%', ?, '%') , state concat('%', ?, '%') , zip concat('%', ?, '%') , set_date between '".$from."' , '".$to ."' order employee"); $query->bind_param('ssssssssssssss', $_get['set_date'], $_get['result'], $_get['employee'], $_get['project'], $_get['source'], $_get['appt_date'], $_get['branch'], $_get['fname'], $_get['lname'], $_get['last_four'], $_get['phone'], $_get['city'], $_get['state'], $_get['zip']); $query->execute(); $query->store_result(); $query->bind_result($subject, $set_date, $set_time, $result, $employee, $project, $source, $appt_date, $branch, $fname, $lname, $last_four, $phone, $city, $state, $zip, $monthly_net, $job_time); $rows = $query->num_rows; $results = array(); while($row = $query->fetch()) { $results[] = array( 'rows' => $rows, 'subject' => $subject, 'set_date' => $set_date, 'set_time' => $set_time, 'result' => $result, 'employee' => $employee, 'project' => $project, 'source' => $source, 'appt_date' => $appt_date, 'branch' => $branch, 'fname' => $fname, 'lname' => $lname, 'last_four' => $last_four, 'phone' => $phone, 'city' => $city, 'state' => $state, 'zip' => $zip, 'monthly_net' => $monthly_net, 'job_time' => $job_time ); } $_session['results'] = $results; if($results) { header('location: ../test_page.php'); }else{ header('location: ../test.php?error=1'); } $query->free_result(); $mysqli->close(); ?>
your form uses method="get" retrieve date values post data, unavailable:
if (isset($_post['t1'])) $from = $_post['t1']; if (isset($_post['t2'])) $to = $_post['t2']; because of that, date condition of query has empty values , fails:
and set_date between '' , '' you should using:
if (isset($_get['t1'])) $from = $_get['t1']; if (isset($_get['t2'])) $to = $_get['t2']; which returns:
and set_date between '2015-7-2' , '2015-7-15' so obvious missed it. , don't forget bind values in prepared statement avoid sql injections.
Comments
Post a Comment