php form filter data between two dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Have you printed out the query? What are your inputs? What happens if you run the raw SQL?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

That's not the format the dates are stored in. They're probably stored as YYYY-MM-DD, so that's how you'll want to query against them.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

And if you move the ORDER BY to the end of the query?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Should also work fine in your code, then. SQL queries need to be structured a certain way.
https://dev.mysql.com/doc/refman/5.7/en/select.html
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Aside from the crazy indentation, what's wrong with it? You might want to parse and format input dates before passing them into the query to avoid unexpected results.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Also, you really want to be using prepared statements. At the very least, sanitize your inputs.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

What does your code look like now? The code posted above won't even run.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: php form filter data between two dates

Post by Christopher »

Code like this reminds me of why MVC is both useful and popular. This pages mixed the Model, View and Controller into one page. It causes problems because you can't validate any of them individually. It really becomes spaghetti code.

I'd recommend writing your Model class first. You can give it values and display the data it returns to verify it works. Then the Controller to wire up the page and verify that the correct values are coming from the Request. Finally the View, which should be a template separate from all the other code.

This is the Controller part of your page dealing with the Request and program flow:

Code: Select all

                        // connect to the database
include('connect-db.php');     // this should be injected into the Model as a dependency
						
$per_page=5;
if (isset($_GET["page"])) {
    $page = $_GET["page"];
} else {
    $page=1;
}

// Page will start from 0 and Multiple by Per Page
$start_from = ($page-1) * $per_page;

$fromdate = $_POST['exrdate'];
$todate = $_POST['exrdate'];
						
if(isset($submit)) {     // Where does this value come from ?!?!?!

Here is your Model code that provides the data for the page. This should really be a class to isolate the code. Then you can give the Model object to the View to use via its interface.

Code: Select all


                        $num_rows = 0;
                        $rows = array;

                        // get the records from the database
                        if ($result = $mysqli->query("SELECT id, customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, DATE_FORMAT(exrdate,'%d/%m/%Y') AS exrdate, exrtime, DATE_FORMAT(exstdate,'%d/%m/%Y') AS exstdate, exstime, deltype, comments, job_cost, part_cost, profit FROM repairs WHERE exrdate >= '$fromdate' and exrdate <= '$todate' ORDER BY id LIMIT $start_from, $per_page"))
						
                        {
                                $num_rows = $result->num_rows;
                                while ($row = $result->fetch_object()) {
                                     $rows[] = $row;
                                }
                       } else {
                                echo "Error: " . $mysqli->error;
                        }

$query = "select * from repairs";
$result = mysqli_query($mysqli, $query);
						
						// Count the total records
$total_records = mysqli_num_rows($result);

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);
This is the View code which is responsible for generating the Response back to the user. Notice no database or PHP system code here. It just works on data you give it. Better would be to give it the Model object and let it get what it needs via the Model's clean interface.

Code: Select all

                               // display records if there are records to display
                                if ($num_rows > 0)
                                {									
                                        // display records in a table
                                        echo "<table class='view-repairs'>";
                                        
                                        // set table headers
                                        echo "<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>";
                                        
                                        foreach ($rows as $row)
                                        {
											
                                                // set up a row for each record
                                                echo "<tr>";
                echo "<td><a href='view-specific-repair.php?id=" . $row->id . "'>".$row->id . "</a></td>";
                                                echo "<td>" . $row->customer_name . "</td>";
                                                echo "<td>" . $row->customer_email . "</td>";
												echo "<td>" . $row->customer_phone . "</td>";
												echo "<td>" . $row->computer_make . "</td>";
												echo "<td>" . $row->computer_model . "</td>";
												echo "<td>" . $row->technician . "</td>";
												echo "<td>" . $row->status . "</td>";
												echo "<td>" . $row->exstdate . "</td>";
												echo "<td>" . $row->exstime . "</td>";
												echo "<td>" . $row->exrdate . "</td>";
												echo "<td>" . $row->exrtime . "</td>";
												echo "<td>" . $row->deltype . "</td>";
												//echo "<td>" . $row->comments . "</td>";
												echo "<td>" . substr($row->comments, 0, 25) . "</td>";
												echo "<td>" . '&#163;' . $row->job_cost . "</td>";
												echo "<td>" . '&#163;' . $row->part_cost . "</td>";
												echo "<td>" . '&#163;' . $row->profit . "</td>";
                                                echo "<td><a href='repairs-tracking.php?id=" . $row->id . "'>Edit</a></td>";
												echo "<td><a href='delete-repair.php?id=" . $row->id . "'>Delete</a></td>";
                                                echo "</tr>";
                                        }
                                        
                                        echo "</table>";
                                }
                                // if there are no records in the database, display an alert message
                                else
                                {
                                        echo "No results to display!";
                                }
                        }
						
}
						
//Going to first page
echo "<center><a href='view-repairs-tracking.php?page=1'>".'First Page'."</a> ";

for ($i=1; $i<=$total_pages; $i++) {

echo "<a href='view-repairs-tracking.php?page=".$i."'>".$i."</a> ";
};
// Going to last page
echo "<a href='view-repairs-tracking.php?page=$total_pages'>".'Last Page'."</a></center> ";

(#10850)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Perfect example of why good indentation is important. I've cleaned up the indentation. See the error now?

Code: Select all

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php
// connect to the database
include('connect-db.php');

$per_page=5;
if (isset($_GET["page"])) {
    $page = $_GET["page"];
} else {
    $page=1;
}

// Page will start from 0 and Multiple by Per Page
$start_from = ($page-1) * $per_page;

$fromdate = $_POST['exrdate'];
$todate = $_POST['exrdate'];

if (isset($_POST['submit']))
{
    // get the records from the database
    if ($result = $mysqli->query("SELECT id, customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, DATE_FORMAT(exrdate,'%d/%m/%Y') AS exrdate, exrtime, DATE_FORMAT(exstdate,'%d/%m/%Y') AS exstdate, exstime, deltype, comments, job_cost, part_cost, profit FROM repairs WHERE exrdate >= '$fromdate' and exrdate <= '$todate' ORDER BY id LIMIT $start_from, $per_page")) 
    {
        // display records if there are records to display
        if ($result->num_rows > 0)
        {                                                                      
            // display records in a table
            echo "<table class='view-repairs'>";

            // set table headers
            echo "<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>";

            while ($row = $result->fetch_object())
            {
                // set up a row for each record
                echo "<tr>";
                echo "<td><a href='view-specific-repair.php?id=" . $row->id . "'>".$row->id . "</a></td>";
                echo "<td>" . $row->customer_name . "</td>";
                echo "<td>" . $row->customer_email . "</td>";
                echo "<td>" . $row->customer_phone . "</td>";
                echo "<td>" . $row->computer_make . "</td>";
                echo "<td>" . $row->computer_model . "</td>";
                echo "<td>" . $row->technician . "</td>";
                echo "<td>" . $row->status . "</td>";
                echo "<td>" . $row->exstdate . "</td>";
                echo "<td>" . $row->exstime . "</td>";
                echo "<td>" . $row->exrdate . "</td>";
                echo "<td>" . $row->exrtime . "</td>";
                echo "<td>" . $row->deltype . "</td>";
                //echo "<td>" . $row->comments . "</td>";
                echo "<td>" . substr($row->comments, 0, 25) . "</td>";
                echo "<td>" . '&#163;' . $row->job_cost . "</td>";
                echo "<td>" . '&#163;' . $row->part_cost . "</td>";
                echo "<td>" . '&#163;' . $row->profit . "</td>";
                echo "<td><a href='repairs-tracking.php?id=" . $row->id . "'>Edit</a></td>";
                echo "<td><a href='delete-repair.php?id=" . $row->id . "'>Delete</a></td>";
                echo "</tr>";
            }

            echo "</table>";
        }
        // if there are no records in the database, display an alert message
        else
        {
            echo "No results to display!";
        }
    }
}
// show an error if there is an issue with the database query
else
{
    echo "Error: " . $mysqli->error;
}

$query = "select * from repairs";
$result = mysqli_query($mysqli, $query);

// Count the total records
$total_records = mysqli_num_rows($result);

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);

//Going to first page
echo "<center><a href='view-repairs-tracking.php?page=1'>".'First Page'."</a> ";

for ($i=1; $i<=$total_pages; $i++) {

    echo "<a href='view-repairs-tracking.php?page=".$i."'>".$i."</a> ";
};
// Going to last page
echo "<a href='view-repairs-tracking.php?page=$total_pages'>".'Last Page'."</a></center> ";


// close database connection
$mysqli->close();
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Right, I didn't fix that. Should be pretty obvious why.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

Code: Select all

if (isset($_POST['submit']))
{
    // Lots of stuff
}
// show an error if there is an issue with the database query
else
{
    echo "Error: " . $mysqli->error;
}
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: php form filter data between two dates

Post by Christopher »

ianhaney wrote:Sorry still can't see it
As I said above, you are making developing this code very difficult for yourself because of the structure of the code. If even an experienced developer like Celauran has difficulty finding the problems, it's going to be miserable for you to find them. Software development is made much easier with: 1) Consistent formatting, 2) Modularization of code using classes and functions. We could walk you through some very simple steps to refactor this code so it is less painful.
(#10850)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

First things first, you're getting the error because your if blocks are mangled and you're displaying an error if $_POST['submit'] isn't set. That's what I was trying to point out above.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

ianhaney wrote:Only if is ok to walk me through some simple steps to refactor the code and make it easier to understand and follow
At the absolute minimum:
  • Move all your logic to the top
  • Stop echoing out HTML
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php form filter data between two dates

Post by Celauran »

It's definitely cleaner. Big step in the right direction. What are the values of $_GET['d1'] and $_GET['d2']?
Post Reply