PHP/HTML Table and Modal Forms that display information

Question

Its been a while since I have written PHP or HTML for a project, so I could use some thoughts on improving the code below. I wrote all of this going off of old knowledge, so I imagine its really sloppy and can be written a lot cleaner. All of the code below works. Here is the link to the project on GitHub

<!DOCTYPE html>

<html lang="en">

    <head>

        <meta charset="UTF-8" name="viewport" content="width-device-width, initial-scale=1"/>
        <link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
        <link rel="stylesheet" type="text/css" href="css/mystyle.css"/>
        <title>Loan Pipeline</title>
    </head>

<body>

    <nav class="navbar navbar-default">

        <div class="container-fluid">

            <h2 class="text-primary">Loan Pipeline &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </h2>
            <input type="text" id="myInput" onKeyUp="searchNames()" placeholder="Search by Lender or Processor..." title="Type in a name"> &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;

            <?php

            include_once "conn.php";


            $queryRob = mysqli_query($conn, "SELECT pipeid, lender, SUM(loanamount) AS loanTotal FROM user WHERE lender='Rob'");
            $queryAmy = mysqli_query($conn, "SELECT pipeid, lender, SUM(loanamount) AS loanTotal FROM user WHERE lender='Amy'");
            $queryCassie = mysqli_query($conn, "SELECT pipeid, lender, SUM(loanamount) AS loanTotal FROM user WHERE lender='Cassie'");
            $queryLiz = mysqli_query($conn, "SELECT pipeid, lender, SUM(loanamount) AS loanTotal FROM user WHERE lender='Liz'");
            $queryNancy = mysqli_query($conn, "SELECT pipeid, lender, SUM(loanamount) AS loanTotal FROM user WHERE lender='Nancy'");

            while($fetch = mysqli_fetch_array($queryRob)){
                    $robTotal = "$".number_format($fetch['loanTotal'], 2);
            }

            while($fetch = mysqli_fetch_array($queryAmy)){
                    $amyTotal = "$".number_format($fetch['loanTotal'], 2);
            }

            while($fetch = mysqli_fetch_array($queryCassie)){
                    $cassieTotal = "$".number_format($fetch['loanTotal'], 2);
            }

            while($fetch = mysqli_fetch_array($queryLiz)){
                    $lizTotal = "$".number_format($fetch['loanTotal'], 2);
            }

            while($fetch = mysqli_fetch_array($queryNancy)){
                    $nancyTotal = "$".number_format($fetch['loanTotal'], 2);
            }

            ?>

            <h5 class="text-primary">Amy: <?php echo $amyTotal;?></h5> &nbsp;&nbsp;&nbsp;&nbsp;
            <h5 class="text-primary">Cassie: <?php echo $cassieTotal;?></h5> &nbsp;&nbsp;&nbsp;&nbsp;
            <h5 class="text-primary">Liz: <?php echo $lizTotal;?></h5> &nbsp;&nbsp;&nbsp;&nbsp;
            <h5 class="text-primary">Nancy: <?php echo $nancyTotal;?></h5> &nbsp;&nbsp;&nbsp;&nbsp;
            <h5 class="text-primary">Rob: <?php echo $robTotal;?></h5>  

        </div>



    </nav>

    <div class="col-md-3"></div>

    <div class="col-md-6 well">

        <button type="button" class="btn btn-success" data-toggle="modal" data-target="#form_modal"><span class="glyphicon glyphicon-plus"></span> Add New Loan</button>



        <br /><br />

        <table class="table table-bordered" id="allLoans">

            <thead class="alert-success">

                <tr>

                    <th>Additional Info</th>

                    <th>Loan Number</th>

                    <th>Customer Name</th>

                    <th>Processor</th>

                    <th>Lender</th>

                    <th>Title Company</th>

                    <th>Contract Price</th>

                    <th>Loan Amount</th>

                    <th>Closing Date</th>

                    <th>Closing Time</th>

                    <th>Product</th>

                    <th>Rate</th>


                </tr>

            </thead>

            <tbody style="background-color:#fff;">

                <?php

                    require "conn.php";

                    $query = mysqli_query($conn, "SELECT * FROM user ORDER BY customername");

                    while($fetch = mysqli_fetch_array($query))
                    {
                    if(is_null($fetch['closingdate'])) {
                        $closingDate = '';
                    } else {
                        $closingDate = date("m/d/Y", strtotime($fetch['closingdate']));
                    }

                    if(is_null($fetch['closingtime'])) {
                        $closingTime = '';
                    } else {
                        $closingTime = date("h:i A", strtotime($fetch['closingtime']));
                    }

                    if(is_null($fetch['contractprice'])) {
                        $contractPrice = '';
                    } else {
                        $contractPrice = "$".number_format($fetch['contractprice'], 2);
                    }

                    if(is_null($fetch['loanamount'])) {
                        $loanAmount = '';
                    } else {
                        $loanAmount = "$".number_format($fetch['loanamount'], 2);
                    }

                    $closingConf = $fetch['confirmed'];

                    if($closingConf == 0) {
                        $style = 'style="background-color:white;"';
                    } elseif($closingConf == 1) {
                        $style = 'style="background-color:rgba(91,244,91,1.00);"';
                    }
                ?>

                <tr>

                    <td><button class="btn btn-warning" data-toggle="modal" type="button" data-target="#update_modal<?php echo $fetch['pipeid'];?>"><span class="glyphicon glyphicon-edit"></span> Info</button></td>

                    <td><?php echo $fetch['loannumber'];?></td>

                    <td><?php echo $fetch['customername'];?></td>

                    <td><?php echo $fetch['processor'];?></td>

                    <td><?php echo $fetch['lender'];?></td>

                    <td><?php echo $fetch['titleco'];?></td>

                    <td><?php echo $contractPrice;?></td>

                    <td><?php echo $loanAmount;?></td>

                    <td <?php echo $style;?>><?php echo $closingDate;?></td>

                    <td <?php echo $style;?>><?php echo $closingTime;?></td>

                    <td><?php echo $fetch['product'];?></td>

                    <td><?php echo $fetch['notes'];?></td>

                </tr>

                <?php



                    include "update_user.php";



                    }

                ?>

            </tbody>

        </table>

    </div>

    <div class="footer">

        <p>&copy; <?php echo date("Y");?> Foresight Bank All Rights Reserved<br><br>

        Created by <a href="mailto:[email protected]">Zack Elcombe</a>

        </p>

    </div>      

    <div class="modal fade" id="form_modal" aria-hidden="true">

        <div class="modal-dialog">

            <div class="modal-content">

                <form method="POST" action="save_user.php">

                    <div class="modal-header">

                        <h3 class="modal-title">Add Loan Info</h3>

                    </div>

                    <div class="modal-body">

                        <div class="col-md-2"></div>

                        <div class="col-md-8">

                            <div class="form-group1">

                                <label >* Required Field</label>

                            </div>                          

                            <div class="form-group">

                                <label>Loan Number</label>

                                <input type="text" name="loannumber" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Customer Name *</label>

                                <input type="text" name="customername" class="form-control" required="required" />

                            </div>

                            <div class="form-group">

                                <label>Processor</label>

                                <input type="text" name="processor" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Lender *</label>

                                <input type="text" name="lender" class="form-control" required="required"/>

                            </div>

                            <div class="form-group">

                                <label>Title Company</label>

                                <input type="text" name="titleco" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Contract Price</label>

                                <input type="text" name="contractprice" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Loan Amount</label>

                                <input type="text" name="loanamount" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Closing Date</label>

                                <input type="date" name="closingdate" class="form-control"/> <br>

                                <input type="checkbox" name="clsdateconfirmed" class="forme-control"/>

                                <label>Closing Date Confirmed</label>                               

                            </div>

                            <div class="form-group">

                                <label>Closing Time</label>

                                <input type="time" name="closingtime" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Interest Rate</label>

                                <input type="text" name="interestrate" class="form-control"/>

                            </div>                          

                            <div class="form-group">

                                <label>Product</label>

                                <input type="text" name="product" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>App Docs Signed?</label>

                                <input type="text" name="appdocs" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Approval Required?</label>

                                <input type="text" name="approval" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Appraisal Ordered?</label>

                                <input type="text" name="appraisal" placeholder="Yes/No" class="form-control"/>

                            </div>                              

                            <div class="form-group">

                                <label>Closing Location</label>

                                <input type="text" name="closingloc" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Purchase Agreement</label>

                                <input type="text" name="purchagreement" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Rate Quote</label>

                                <input type="text" name="ratequote" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Risk Rating</label>

                                <input type="text" name="riskrating" class="form-control"/>

                            </div>

                            <div class="form-group">

                                <label>Title Ordered</label>

                                <input type="text" name="titleordered" class="form-control" placeholder="Yes/No"/>

                            </div>                              

                        </div>

                    </div>

                    <div style="clear:both;"></div>

                    <div class="modal-footer">

                        <button name="save" class="btn btn-primary"><span class="glyphicon glyphicon-save"></span> Save</button>

                        <button class="btn btn-danger" type="button" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Close</button>

                    </div>

                    </div>

                </form>

            </div>

        </div>

    </div>

<script src="js/jquery-3.2.1.min.js"></script>  

<script src="js/bootstrap.js"></script> 

<script>
function searchNames() {
  var input, filter, table, tr, td, i, td2, txtValue, txtValue2;
  input = document.getElementById("myInput");
  filter = input.value.toUpperCase();
  table = document.getElementById("allLoans");
  tr = table.getElementsByTagName("tr");
  for (i = 0; i < tr.length; i++) {
    td = tr[i].getElementsByTagName("td")[3];
    td2 = tr[i].getElementsByTagName("td")[4];
    if (td || td2) {
      txtValue = td.textContent || td.innerText;
      txtValue2 = td2.textContent || td2.innerText;
      if (txtValue.toUpperCase().indexOf(filter) > -1 || txtValue2.toUpperCase().indexOf(filter) > -1 ) {
        tr[i].style.display = "";
      } else {
        tr[i].style.display = "none";
      }
    }       
  }
}   
</script>

</body> 

</html>```

slepic · Accepted Answer · 2020-03-28 05:49:07Z

2

I dont have time for a full review. But at least one small point:

Dont mix PHP And HTML. Dont mix data loading And logic with the way data Is displayed. That alone Will make Things much cleaner.

answered Mar 28, 2020 at 5:49

slepic

5,6572 gold badges10 silver badges27 bronze badges

Add a comment |

mickmackusa · Accepted Answer · 2020-03-28 22:30:17Z

It looks like much of the logic is hardcoded -- until that changes, you don't necessarily need to use a prepared statement and bind parameters.

I do recommend that you minimize trips to the database by writing a single query which groups on lender and/or pipeid (assuming pipieid and lender are directly related to each other).

In simplest terms, you can start with a basic query like this:

SELECT
    lender,
    SUM(loanamount) AS loansum
FROM user
WHERE lender IN ('Rob', 'Amy', 'Cassie' 'Liz', 'Nancy')
GROUP BY lender;

The user table (probably better-named as lender) seems like an inappropriate place to store multiple rows of loan details. I get the impression that the user table has become the catch-all for lots of data that should be stored separately in a loan table -- this way an individual lender is never represented more than once in the lender table and can be freely represented a multitude of times in the loan table -- this would be where SUMming should take place.

Then leveraging the result set makes producing your content much more elegant (D.R.Y.), not to mention completely controlled by the query at a single point in your script.

$result = mysqli_query($conn, $query);
foreach ($result as $row) { ?>
    <h5 class="text-primary">
        <?php echo $row['lender'] , ": $" , number_format($row['loansum'], 2); ?>
    </h5>
<?php }

RoToRa · Accepted Answer · 2020-04-29 13:29:39Z

1

Aside from what the other two answers mention:

You have several simular repeating code blocks (such as the if(is_null($fetch[... blocks) that could be shortend/optimizied by using functions
Don't create spacing with chains of   and/or <br>s. That is what margins and paddings in CSS are for.
Don't use style attributes. Put all styles into an separate CSS file.

answered Apr 29, 2020 at 13:29

RoToRa

11.6k1 gold badge24 silver badges51 bronze badges

Add a comment |

Stack Exchange Network

PHP/HTML Table and Modal Forms that display information

3 Answers 3

You must log in to answer this question.

Hot Network Questions

PHP/HTML Table and Modal Forms that display information

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions