Cleaner more efficient way of multiple database queries

Question

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>

I'm not sure whether this is offtopic. If you are asking a specific question, try stackoverflow. — Rob Apodaca, Jan 26 '13 at 21:35
Why are you fetching the same table again and again? Especially, without any limits. — hjpotter92, Jan 26 '13 at 22:09
Everything I need is stored in the same table I am new to programming so if you have any advice that would be awesome — Yamaha32088, Jan 26 '13 at 23:33

MrLore · Accepted Answer · 2013-08-11 16:26:23Z

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.

Stack Exchange Network

current community

your communities

more stack exchange communities

Cleaner more efficient way of multiple database queries

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Hot Network Questions

Cleaner more efficient way of multiple database queries

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Related

Hot Network Questions