Page 2 of 2

Re: php form filter data between two dates

Posted: Thu Feb 04, 2016 8:16 am
by Celauran
ianhaney wrote:would that be using print or echo?
Yes. Or var_dump. Or print_r.

Re: php form filter data between two dates

Posted: Thu Feb 04, 2016 8:18 am
by Celauran
A little further cleanup. The logic is all grouped together at the very top of the file and the view portion is all on its own. When the time comes to separate out business logic from display, you'll already be most of the way there.

Code: Select all

<?php

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

session_start();
if ($_SESSION['user']=='') {
    header("Location:../index.php");
} else {
    include("../config.php");
    $sql = $dbh->prepare("SELECT * FROM users WHERE id=?");
    $exec = $sql->execute(array($_SESSION['user']));
    $user = $exec ? $sql->fetch() : null;
}

include('connect.php');
$data = [];
if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    $result = $db->prepare("SELECT * FROM repairs WHERE exrdate BETWEEN :a AND :b");
    $result->bindParam(':a', date('Y-m-d', $d1));
    $result->bindParam(':b', date('Y-m-d', $d2));
    $exec = $result->execute();
    if ($exec) {
        $data = $result->fetchAll();
    }
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head> 
        <title>Search Data Between Two Dates</title>
        <link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" />
        <link rel="stylesheet" type="text/css" href="css/tcal.css" />
        <script type="text/javascript" src="js/tcal.js"></script>
    </head>  

    <body>
        <div id="logo">
            <img src="images/logo/it-done-right.jpg" alt="" title="">
        </div>

        <script>
            var t;
            window.onload=resetTimer;
            document.onkeypress=resetTimer;

            function logout()
            {
                alert("You are now logged out.");
                location.href='../logout.php';
            }
            function resetTimer()
            {
                clearTimeout(t);
                t=setTimeout(logout,1800000) //logs out in 30 minutes
            }
        </script>

        <?php if ($user): ?>
            <div class='home-content'>
                <center>
                    <h2>Hello, <?= $user['username']; ?></h2>
                    <a href='../logout.php'>Log Out</a>
                    <br><br>
                    <a href='../index.php'>Home</a>
                </center>
            </div>
            <br>
        <?php endif; ?>

        <?php include("nav-menu.php"); ?>

        <br>

        <form action="search-data.php" method="get">
            From : <input type="text" name="d1" class="tcal" value="" />
            To: <input type="text" name="d2" class="tcal" value="" />
            <input type="submit" value="Search">
        </form>

        <table id="resultTable" data-responsive="table" style="text-align: left; width: 400px;" border="1" cellspacing="0" cellpadding="4">
            <thead>
                <tr>
                    <th>Repair ID</th>
                    <th>Customer Name</th>
                    <th>Customer Email</th>
                    <th>Customer Phone</th>
                    <th>Computer Make</th>
                    <th>Computer Model</th>
                    <th>Technician</th>
                    <th>Status</th>
                    <th>Expected Start Date</th>
                    <th>Expected Start Time</th>
                    <th>Expected Repair Date</th>
                    <th>Expected Repair Time</th>
                    <th>Delivery Type</th>
                    <th>Comments</th>
                    <th>Job Repair Cost</th>
                    <th>Part(s) Cost</th>
                    <th>Profit</th>
                    <th colspan='2'>Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php foreach ($data as $row): ?>
                <tr class="record">
                   <td><a href="view-specific-repair.php?id=<?= $row['id']; ?>"><?php echo $row['id']; ?></a></td>
                   <td><?php echo $row['customer_name']; ?></td>
                   <td><?php echo $row['customer_email']; ?></td>
                   <td><?php echo $row['customer_phone']; ?></td>
                   <td><?php echo $row['computer_make']; ?></td>
                   <td><?php echo $row['computer_model']; ?></td>
                   <td><?php echo $row['customer_phone']; ?></td>
                   <td><?php echo $row['technician']; ?></td>
                   <td><?php echo $row['status']; ?></td>
                   <td><?php echo $row['exstdate']; ?></td>
                   <td><?php echo $row['exstime']; ?></td>
                   <td><?php echo $row['exrdate']; ?></td>
                   <td><?php echo $row['exrtime']; ?></td>
                   <td><?php echo $row['deltype']; ?></td>
                   <td><?php echo substr($row['comments'], 0, 25); ?></td>
                   <td><?php echo '&#163;' . $row['job_cost']; ?></td>
                   <td><?php echo '&#163;' . $row['profit_cost']; ?></td>
                   <td><?php echo '&#163;' . $row['profit']; ?></td>
                   <td><a href="repairs-tracking.php?id=<?= $row['id']; ?>">Edit</a></td>
                   <td><a href="delete-repair.php?id=<?= $row['id']; ?>">Delete</a></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </body>
</html>

Re: php form filter data between two dates

Posted: Thu Feb 04, 2016 9:03 am
by Celauran
Can move the date() calls out of the bindParam. Should get rid of that.

Re: php form filter data between two dates

Posted: Thu Feb 04, 2016 9:34 am
by Celauran

Code: Select all

if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    $result = $db->prepare("SELECT * FROM repairs WHERE exrdate BETWEEN :a AND :b");
    $result->bindParam(':a', date('Y-m-d', $d1));
    $result->bindParam(':b', date('Y-m-d', $d2));
    $exec = $result->execute();
    if ($exec) {
        $data = $result->fetchAll();
    }
}
would become

Code: Select all

if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    if ($d1 && $d2) {
        $start_date = date('Y-m-d', $d1);
        $end_date = date('Y-m-d', $d2);
        $result = $db->prepare("SELECT * FROM repairs WHERE exrdate BETWEEN :a AND :b");
        $result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
        }
    }
}
Added some additional checking there to make sure $_GET['d1'] and $_GET['d2'] could actually be parsed as dates. If they have, format them as MySQL expects then bind those values to your prepared statement. Do you see how that's all working?

Re: php form filter data between two dates

Posted: Thu Feb 04, 2016 10:08 am
by Celauran
Better?

Code: Select all

<?php
// Check if dates have been specified. If they haven't, there's no sense in querying
if (isset($_GET['d1']) && isset($_GET['d2'])) {
	// Date fields are free form. Let's try to parse those into timestamps
	$d1 = strtotime($_GET['d1']);
	$d2 = strtotime($_GET['d2']);
	// If the dates provided were valid, we'll proceed
	if ($d1 && $d2) {
		// Format those timestamps into YYYY-MM-DD for MySQL
		$start_date = date('Y-m-d', $d1);
		$end_date = date('Y-m-d', $d2);
		// Set up our query
		$result = $db->prepare("SELECT * FROM repairs WHERE exrdate BETWEEN :a AND :b");
		$result->bindParam(':a', $start_date);
		$result->bindParam(':b', $end_date);
		// Execute returns false if the query isn't valid
		$exec = $result->execute();
		// If the query executed successfully, we'll get all returned rows
		if ($exec) {
			$data = $result->fetchAll();
		}
	}
}