1
\$\begingroup\$

I have the code below I probably did not need to post the entirety of it but you can see in the code that I make multiple data base queries to generate each of the multiple tables. I am curious if there is a way to make one query and yet get the same results, and possibly clean up the code.

<!DOCTYPE HTML PUBLIC "-//W3C/DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Lieber Construction Time Clock</title>
 <script src="http://code.jquery.com/jquery-1.9.0.js"></script>
 <script src="http://code.jquery.com/ui/1.10.0/jquery-ui.js"></script>
 <script src="jsFunctions.js" type="text/javascript"></script>
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" />
 <link media="Screen" href="timeCard.css" type="text/css" rel="stylesheet" />
 <link media="only screen and (max-device-width: 480px) and (min-device-width: 320px)" href="mobile.css" type="text/css" rel="stylesheet" />
 </head>
 <body>
 <select size="1" name="equipmentTable" id="equipmentTable">
 <option value="">Select Machine</option>
 <option value="Blade">Blade</option>
 <option value="Challenger">Challenger</option>
 <option value="Compactor">Compactor</option>
 <option value="Dozer">Dozer</option>
 <option value="Excavator">Excavator</option>
 <option value="Loader">Loader</option>
 <option value="Skid">Skid</option>
 <option value="Tractor">Tractor</option>
 <option value="Truck">Truck</option>
 </select>
 <div class="tables">
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Challenger">
<table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Challenger"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
</table>
</div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Dozer">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Dozer"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Truck">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Truck"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Excavator">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Excavator"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Skid">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Skid"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Compactor">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Compactor"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Loader">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Loader"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Tractor">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Tractor"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
<?php
require 'DB.php';
try {
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
 } catch(PDOException $e){
 echo'ERROR: ' . $e->getMessage();
 }
?>
<div class="Blade">
 <table class='tableTwo'>
<tr>
<th>Equipment Type</th>
<th>Unit Number</th>
<th>Last Updated</th>
<th>Current Hours</th>
<th>Last Service</th>
<th>Hours on motor oil</th>
<th>Last Service</th>
</tr>
<?php
while ($row=$stmt->fetch()){
$equipType = $row['equipType'];
if ($equipType == "Blade"){
echo "<tr>";
echo "<td>" . $equipType . "</td>";
echo "<td>" . $row['unitNumber'] . "</td>";
echo "<td>" . $row['lastUpdate'] . "</td>";
echo "<td>" . $row['currentHours'] . "</td>";
echo "<td>" . $row['oilChange'] . "</td>";
echo "</tr>";
}
}
?>
 </table>
 </div>
</div>
 </body>
</html>
asked Jan 26, 2013 at 20:55
\$\endgroup\$
3
  • 1
    \$\begingroup\$ I'm not sure whether this is offtopic. If you are asking a specific question, try stackoverflow. \$\endgroup\$ Commented Jan 26, 2013 at 21:35
  • 1
    \$\begingroup\$ Why are you fetching the same table again and again? Especially, without any limits. \$\endgroup\$ Commented Jan 26, 2013 at 22:09
  • \$\begingroup\$ Everything I need is stored in the same table I am new to programming so if you have any advice that would be awesome \$\endgroup\$ Commented Jan 26, 2013 at 23:33

1 Answer 1

4
\$\begingroup\$

Unless I'm missing something, you're SELECTing the entire table over an over, then do exactly the same thing with it every time (except with different HTML class names). If that's the case, you can replace the whole file with this:

<!DOCTYPE HTML PUBLIC "-//W3C/DTD HTML 4.01//EN"
 "http://www.w3.org/TR/html4/strict.dtd">
<html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Lieber Construction Time Clock</title>
 <script src="http://code.jquery.com/jquery-1.9.0.js"></script>
 <script src="http://code.jquery.com/ui/1.10.0/jquery-ui.js"></script>
 <script src="jsFunctions.js" type="text/javascript"></script>
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" />
 <link media="Screen" href="timeCard.css" type="text/css" rel="stylesheet" />
 <link media="only screen and (max-device-width: 480px) and (min-device-width: 320px)" href="mobile.css" type="text/css" rel="stylesheet" />
 </head>
 <body>
 <select size="1" name="equipmentTable" id="equipmentTable">
 <option value="">Select Machine</option>
 <option value="Blade">Blade</option>
 <option value="Challenger">Challenger</option>
 <option value="Compactor">Compactor</option>
 <option value="Dozer">Dozer</option>
 <option value="Excavator">Excavator</option>
 <option value="Loader">Loader</option>
 <option value="Skid">Skid</option>
 <option value="Tractor">Tractor</option>
 <option value="Truck">Truck</option>
 </select>
 <div class="tables">
<?php
require_once 'DB.php';
try
{
 $stmt = $conn->prepare('SELECT * FROM equipment ORDER BY equipType, unitNumber');
 $stmt->execute();
}
catch(PDOException $e)
{
 echo'ERROR: ' . $e->getMessage();
}
$lastType;
$num = 0;
while($row = $stmt->fetch())
{
 $equipType = $row['equipType'];
 if($equipType !== $lastType)
 {
 if($num !== 0)
 {
 echo "</table></div>";
 }
 echo "<div class=\"$equipType\">
 <table class=\"tableTwo\">
 <tr>
 <th>Equipment Type</th>
 <th>Unit Number</th>
 <th>Last Updated</th>
 <th>Current Hours</th>
 <th>Last Service</th>
 <th>Hours on motor oil</th>
 <th>Last Service</th>
 </tr>";
 }
 echo "<tr>";
 echo "<td>$equipType</td>";
 echo "<td>" . $row['unitNumber'] . "</td>";
 echo "<td>" . $row['lastUpdate'] . "</td>";
 echo "<td>" . $row['currentHours'] . "</td>";
 echo "<td>" . $row['oilChange'] . "</td>";
 echo "</tr>";
 $lastType = $equipType;
 $num++;
}
echo "</table></div>";
?>
 </div>
 </body>
</html>

Additionally, I'd suggest reading up on your SQL for a start, this whole thing screams out WHERE clause. In every case, your database will be more efficient at sorting the results than whatever programming language you're using them in (and certainly PHP). Such as:

SELECT * FROM equipment WHERE equipType = "Challenger" ORDER BY unitNumber;

Another thing you should be careful about is requireing the same thing over and over, as it's usually unnecessary and can be replaced with a require_once which will ensure that it's only loaded if it hasn't been in the current session.

answered Jan 27, 2013 at 5:09
\$\endgroup\$

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.