MySQLI prepared statement and show results of query for a date range entered with a PHP Datepicker on Form -
i trying execute mysqli prepared statement query mysql database , return results depending on data requested in form. have been able prepared statement work , brings data correctly. want able add 'date range' query , can't work. added date picker top of request form making from , to fields input type="date". adds date fields in mm/dd/yyyy format. in database column i'm checking date type , it's in format yyyy-mm-dd. i've tried several different suggestions site , others i've come across searching errors. i've tried use strtotime , date , errors date in wrong format , fatal error can't construct dateperiod(). using php v5.5 read can use datetime::createfromformat along $string->format , use dateperiod , work fatal error call member function format() on non-object. please help.
here code 'search form'
<form action="../includes/test.inc.php" method="get"> <table border="0" cellspacing="1"> <tr> dates:<br> from: <input type="date" id="from" name="from"> to: <input type="date" id="to" name="to"> </br></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> here code 'form processing action'
<?php include_once 'db_connect.php'; include_once 'psl-config.php'; session_start(); $error_msg = ""; if (isset($_get['from'])) $date = $_get['from']; if (isset($_get['to'])) $date2 = $_get['to']; if (isset($_get['set_date'])) $set_date = $_get['set_date']; 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']; $query = $mysqli->prepare("select set_date, 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('%', ?, '%') order employee"); if (isset($_get['from'])) { $date = datetime::createfromformat('m/d/y', $_get['from']); $date2 = datetime::createfromformat('m/d/y', $_get['to']); $from = $date->format('y-m-d'); $to = $date2->format('y-m-d'); $daterange = new dateperiod($from, $to); foreach($daterange $dr) { $query->bind_param('sssssssssssssss', $dr, $_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($set_date, $result, $employee, $project, $source, $appt_date, $branch, $fname, $lname, $last_four, $phone, $city, $state, $zip); $rows = $query->num_rows; $results = array(); while($row = $query->fetch()) { $results[] = array( 'rows' => $rows, 'set_date' => $set_date, '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 ); } $_session['results'] = $results; if($results) { header('location: ../test_page.php'); }else{ header('location: ../test.php?error=1'); } $query->free_result(); $mysqli->close(); ?> update: have been able eliminate errors receiving still can't date range work. didn't change code on 'form page' here updated code 'processing page'. i'm getting sent 'error=1' page if there no results know there results , come correctly if remove date range portion.
<?php include_once 'db_connect.php'; include_once 'psl-config.php'; session_start(); $error_msg = ""; if (isset($_get['from'])) $date = $_get['from']; if (isset($_get['to'])) $date2 = $_get['to']; if (isset($_get['set_date'])) $set_date = $_get['set_date']; 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['from'])) { $from = new datetime($_get['from']); $to = new datetime($_get['to']); var_dump($from, $to); $interval = dateinterval::createfromdatestring('1 day'); $daterange = new dateperiod($from, $interval, $to); if (isset($daterange)) { $query = $mysqli->prepare("select set_date, result, employee, project, source, appt_date, branch, fname, lname, last_four, phone, city, state, zip 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('%', ?, '%') 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($set_date, $result, $employee, $project, $source, $appt_date, $branch, $fname, $lname, $last_four, $phone, $city, $state, $zip); $rows = $query->num_rows; $results = array(); while($row = $query->fetch()) { $results[] = array( 'rows' => $rows, 'set_date' => $set_date, '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 ); } $_session['results'] = $results; if($results) { header('location: ../test_page.php'); }else{ header('location: ../test.php?error=1'); } $query->free_result(); $mysqli->close(); ?>
i able figure out. seems trying making more complicated needed be. here code used query work date range input form
include_once 'db_connect.php'; include_once 'psl-config.php'; session_start(); $error_msg = ""; if (isset($_get['from'])) $from = $_get['from']; if (isset($_get['to'])) $to = $_get['to']; if (isset($_get['set_date'])) $set_date = $_get['set_date']; 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']; $from = date("y-m-d", strtotime($from)); $to = date("y-m-d", strtotime($to)); $query = $mysqli->prepare("select set_date, result, employee, project, source, appt_date, branch, fname, lname, last_four, phone, city, state, zip appointments set_date between '".$from."' , '".$to."' , 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('%', ?, '%') order set_date asc"); $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($set_date, $result, $employee, $project, $source, $appt_date, $branch, $fname, $lname, $last_four, $phone, $city, $state, $zip); $rows = $query->num_rows; $results = array(); while($row = $query->fetch()) { $results[] = array( 'rows' => $rows, 'set_date' => $set_date, '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 ); } $_session['results'] = $results; if($results) { header('location: ../appointments_page.php'); }else{ header('location: ../appointments.php?error=1'); } $query->free_result(); $mysqli->close(); ?> just needed $from = date("y-m-d", strtotime($from) , $to = date("y-m-d", strtotime($to) , where set_date between '".$from."' , '".$to."' portion in select statement. suggestions , hope answer can else
Comments
Post a Comment