Page 1 of 2

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 11:13 am
by Celauran
Have you printed out the query? What are your inputs? What happens if you run the raw SQL?

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 11:42 am
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.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 12:12 pm
by Celauran
And if you move the ORDER BY to the end of the query?

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 12:18 pm
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

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 12:27 pm
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.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 12:27 pm
by Celauran
Also, you really want to be using prepared statements. At the very least, sanitize your inputs.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 12:39 pm
by Celauran
What does your code look like now? The code posted above won't even run.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 1:07 pm
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> ";


Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 2:12 pm
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();

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 2:30 pm
by Celauran
Right, I didn't fix that. Should be pretty obvious why.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 2:45 pm
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;
}

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 3:21 pm
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.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 3:30 pm
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.

Re: php form filter data between two dates

Posted: Wed Feb 03, 2016 3:37 pm
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

Re: php form filter data between two dates

Posted: Thu Feb 04, 2016 7:53 am
by Celauran
It's definitely cleaner. Big step in the right direction. What are the values of $_GET['d1'] and $_GET['d2']?