Re: php form filter data between two dates
Posted: Wed Feb 03, 2016 11:13 am
Have you printed out the query? What are your inputs? What happens if you run the raw SQL?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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 ?!?!?!
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);
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>" . '£' . $row->job_cost . "</td>";
echo "<td>" . '£' . $row->part_cost . "</td>";
echo "<td>" . '£' . $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> ";
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>" . '£' . $row->job_cost . "</td>";
echo "<td>" . '£' . $row->part_cost . "</td>";
echo "<td>" . '£' . $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();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;
}
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.ianhaney wrote:Sorry still can't see it
At the absolute minimum: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