Dropdown list have duplicate and form not updated in edit page


#1

Hi, I need some assistance with my coding. I have a page where user can edit the data that have been created. Currently, I am facing the following issues

  1. Form is updated but at the main page, it is not updated in the details page (refer to image 1)
  2. If I just want to edit the type, there will be duplicate (refer to image 2). But if I reselect the brand, the type will not have duplicate (refer to image 3). For example, initial brand is “Toyota”, I need to select “Honda” first then re-select back “Toyota” and it will not have duplicate.

Below is my code for the all car page

<?php
include('dbConfig.php');
?>

<h2>All Car</h2>
<br>
<center>
    <table border='1' width="50%">
        <tr>
            <th>Car ID</th>
            <th>Car Brand</th>
            <th>Type</th>
            <th>Edit</th>
        </tr>
        <?php
        $sql = "SELECT * FROM cardetails cd, brand b, type t where cd.brandName = b.brandID and cd.type = t.typeID";

        $result = mysqli_query($link, $sql) or die(mysqli_error($link));

        while ($row = mysqli_fetch_array($result)) {
            $arrProjects[] = $row;
        }

        for ($countProjects = 0; $countProjects < count($arrProjects); $countProjects++) {
            $carID = $arrProjects[$countProjects]['carID'];
            $brandName = $arrProjects[$countProjects]['brandName'];
            $type = $arrProjects[$countProjects]['type'];
            ?>
            <tr>
                <td align="center"><?php echo $carID ?></td>
                <td><?php echo $brandName; ?></td>
                <td><?php echo $type; ?></td>
                <td align="center"><a href=editCarDetails.php?id=<?php echo $carID; ?>><?php echo 'Edit Cars Details'; ?></a></td>

    <?php
}
?>
        </tr>
    </table>

Below is my code for the update detail page

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

<script type="text/javascript">
    $(document).ready(function () {
        $('#brand').on('change', function () {
            var brandID = $(this).val();
            if (brandID) {
                $.ajax({
                    type: 'GET',
                    url: 'getBrand.php',
                    data: 'brandID=' + brandID,
                    success: function (html) {
                        $('#type').html(html);
                        $('#model').html('<option value="">Select type first</option>');
                    }
                });
            } else {
                $('#brand').html('<option value="">Select type first</option>');
                $('#model').html('<option value="">Select brand first</option>');
            }
        });

        $('#type').on('change', function () {
            var typeID = $(this).val();
            if (typeID) {
                $.ajax({
                    type: 'GET',
                    url: 'getBrand.php',
                    data: 'typeID=' + typeID,
                    success: function (html) {
                        $('#model').html(html);
                    }
                });
            } else {
                $('#model').html('<option value="">Select type first</option>');
            }
        });
    });
</script>

<?php
include('dbConfig.php');
$id = $_GET["id"];

$query = ("SELECT * FROM cardetails cd, brand b, type t, model m where cd.brandName = b.brandID and cd.type = t.typeID and cd.model = m.modelID and cd.carID = $id");
$result = executeSelectQuery($query);

for ($i = 0; $i < count($result); $i++) {
    $carID = $result[$i]['carID'];
    $brandName = $result[$i]['brandName'];
    $type = $result[$i]['type'];
    $model = $result[$i]['model'];
    $price = $result[$i]['price'];
    $details = $result[$i]['details'];
}
?>

<h2>Update Car Details</h2>

<form name='updating' method='post' action='updateDetails.php' >
    <table style='width: 50%'>
        <tr><td colspan='2'>&nbsp;</td></tr>
        <tr>
            <td>Car ID</td>
            <td><input type='text' name="carID" style="width: 270px; height: 30px" readonly value="<?php echo $result[0]['carID']; ?>"></td>
        </tr>
        <tr><td colspan='2'>&nbsp;</td></tr>
        <tr>
            <td>Brand</td>
            <td>
                <?php
                
                  $query1 = "select distinct b.* from brand b, cardetails cd where b.brandID = cd.brandName";
                  $result1 = mysqli_query($link, $query1) or die(mysqli_error($link));

                  echo "<select name='brand' id='brand' style='width: 270px'>";
                  while ($row = mysqli_fetch_array($result1)) {
                  $isSelected = ($result1 === $brandName) ? " selected" : "";
                  echo "<option value='" . $row['brandID'] . " '>" . $row['brandName'] . "</option>";
                  }
                  echo "</select>";
                 
                ?>


        </tr>
        <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
        <tr>
            <td>Type</td>
            <td><?php
                $query2 = "select distinct t.* from type t, cardetails cd where t.typeID = cd.type";
                $result2 = mysqli_query($link, $query2) or die(mysqli_error($link));

                echo "<select name='type' id='type' style='width: 270px'>";
                while ($row = mysqli_fetch_array($result2)) {
                    $isSelected = ($result2 === $type) ? " selected" : "";
                    echo "<option value='" . $row['typeID'] . " '>" . $row['type'] . "</option>";
                }
                echo "</select>";
                ?>

            </td>
        </tr>
        <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
        <tr>
            <td>Model</td>
            <td><?php
                $query3 = "SELECT distinct * FROM  model";
                $result3 = mysqli_query($link, $query3) or die(mysqli_error($link));

                echo "<select name='model' id='model' style='width: 270px'>";
                while ($row = mysqli_fetch_array($result3)) {
                    $isSelected = ($result3 === $model) ? " selected" : "";
                    echo "<option value='" . $row['modelID'] . " '>" . $row['model'] . "</option>";
                }
                echo "</select>";
                ?></td>
        </tr>
        <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
        <tr>
            <td>Price</td>
            <td><input type='text' name="price" style="width: 270px; height: 30px" value="<?php echo $result[0]['price']; ?>"></td>
        </tr>
        <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
        <tr>
            <td>Details</td>
            <td><textarea name="details" rows='8' cols='50' style="width: 270px" ><?php echo $result[0]['details']; ?></textarea></td>
        </tr>

        <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
        <tr align='center'><td><input type='submit' value='Update Car Details'></form></td>

            </form>

        <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
    </table>

Below is my code for getBrand.php

<?php
include('dbConfig.php');

if(isset($_GET["brandID"]) && !empty($_GET["brandID"])){
    $query = $link->query("SELECT * FROM type WHERE brandID = ".$_GET['brandID']." ");
    $rowCount = $query->num_rows;
    if($rowCount > 0){
        echo '<option value="">Select Type</option>';
        while($row = $query->fetch_assoc()){ 
            echo '<option value="'.$row['typeID'].'">'.$row['type'].'</option>';
        }
    }else{
        echo '<option value="">Type not available</option>';
    }
}

if(isset($_GET["typeID"]) && !empty($_GET["typeID"])){
    $query = $link->query("SELECT * FROM model WHERE typeID = ".$_GET['typeID']." ");
    $rowCount = $query->num_rows;
    if($rowCount > 0){
        echo '<option value="">Select Model</option>';
        while($row = $query->fetch_assoc()){ 
            echo '<option value="'.$row['modelID'].'">'.$row['model'].'</option>';
        }
    }else{
        echo '<option value="">Model not available</option>';
    }
}
?>

image 1.png

image 2.png

image 3.png


#2

Trying to understand all the issues.

The main page. When you update a record and save it, are you going back or actually loading the main page again?

I don’t see any queries that update a record.


#3

My apologies. I forgotten to include my update page. After update, it will go to the main page. The main page and database is updated. But when I go into the details, the dropdown list is not the updated option

Below is the code for update

<?php
include('dbConfig.php');
?>

<?php
                            if (isset($_POST['carID'])) {
                                $updatecarID = $_POST['carID'];
                                $updatebrand = $_POST['brand'];
                                $updateModel = $_POST['model'];
                                $updateType = $_POST['type'];
                                $updatePrice  = $_POST['price'];
                                $updateDetails = $_POST['details'];

                                if (isset($_POST['brand'])) {
                                    $update = executeUpdateQuery("UPDATE cardetails SET brandName='" . $updatebrand . "' WHERE carID =$updatecarID");
                                }
                                if (isset($_POST['model'])) {
                                    $update = executeUpdateQuery("UPDATE cardetails SET model='" . $updateModel . "' WHERE carID =$updatecarID");
                                }
                                if (isset($_POST['type'])) {
                                    $update = executeUpdateQuery("UPDATE cardetails SET type='" . $updateType . "' WHERE carID =$updatecarID");
                                }
                                if (isset($_POST['price'])) {
                                    $update = executeUpdateQuery("UPDATE cardetails SET price='" . $updatePrice . "' WHERE carID =$updatecarID");
                                }
                                if (isset($_POST['details'])) {
                                    $update = executeUpdateQuery("UPDATE cardetails SET details='" . $updateDetails . "' WHERE carID =$updatecarID");
                                }
                                
                                echo '<script type="text/javascript">alert("Car details successfully updated");window.location="updateCarDetails.php";</script>';
                            } else {
                                echo '<script type="text/javascript">alert("Ca details is not successfully update");window.location="updateCarDetails.php";</script>';
                            }
                            ?>

#4

The 1st problem is you are trying to test if a mysqli result object is equal to a value (repeated in multiple places) - $isSelected = ($result1 === $brandName) ? " selected" : “”;

$result1 is a mysqli result object, not a value. You should be testing id’s anyways. On the very next line you are using $row[‘brandID’]. Shouldn’t the logic be - if the current $row[‘brandID’] is equal to the existing data’s brandID value, output the ‘selected’ attribute in the <option … > tag?

A 2nd problem is you are using a JOIN query when getting the data for the lists for the brands and types (but not the models.) All you should be doing is single table query against first the brand table, then the type table, to get the data to produce the option lists.

You also have some mixed up table definitions and/or data. The brand, type, and model tables should each have id and name columns. The car details table should store the id’s, not the names. So, either you have misnamed some of the columns, such as cd.brandName (it’s actually holding the brandID) or you have names in the id columns (b.brandID is actually holding the brand name.)

You should specifically list the column names or table.column names (for JOINed queries) in the SELECT list in the queries and not use *. This will do two things - 1) Only retrieve the data you need, and 2) serve to document what the query it trying to do.

Also, throughout the code you have unnecessary/overly complicated code, variables, and php syntax, there’s no validation of any of the input data, and the sql query statements are not secure. Some specific things -

In the 1st piece of code -

  1. $arrProjects is an array of data. Why not use - (foreach$arrProjects as $row){ … } to loop over that data? This will simplify all the rest of the code.

  2. Why are you echoing a static string using php in the edit links - <?php echo 'Edit Cars Details'; ?>. Just put that string in the html markup.

In the 2nd piece of code -

  1. You need to validate $_GET[‘id’] before using it. If it’s not present at all or is empty, you shouldn’t run any code that’s dependent on it and should instead output a user error message.

  2. Why do you have () around the sql query string?

  3. When supplying data to an sql query statement, you need to use prepared queries, with place-holders in the sql query for the values, then supply the actual value when you execute the query. This will prevent sql special characters in the data from breaking the sql query syntax, which is how sql injection is accomplished.

  4. Why have you executed other queries directly using mysqli_query(), but also have an executeSelectQuery() function? Consistency counts in programming. You should use the same method throughout your code. This will reduce the amount of code you have to re-write when making changes or corrections, such as switching to using prepared queries or changing the error handling for the queries so that it doesn’t output the error information on a live/public site (which is another problem the current code has.)

  5. The 1st SELECT query will at most match one row. You should detect that it did match a row before using the data, output a user error message if it didn’t, and to simplify all the rest of the code, just fetch that one row into a php variable and reference the elements in that variable in the rest of the code. There’s no need for a loop and there’s no need to copy variables to other variables, then to sometimes use those variables and sometimes reference elements in the original data. Use the simplest logic that accomplishes a task and be constant in how you do things.

  6. I’m guessing using DISTINCT in the sql queries was an attempt to remove the duplicate output. When you change the queries as suggested, there will be no need for this and it should be removed.

In the 3rd (update) piece of code -

  1. Don’t copy variable to other variables without any reason. This just wastes your time typing and then fixing typo errors. The original $_POST variables are perfectly okay to use. If you do have a need to copy variables to other variables, such as if you need to trim all the data in order to detect if all white-spaces where entered, you can do this using a single statement. No need to write out line after line of code for each possible form field.

  2. You need to validate all the input data before using it. What happens if the default empty select/option value was submitted? Do you want to store that back into the database table? If you use an array to hold the validation error messages, the array is also an error flag. If the array is empty, there are no errors. If the array is not empty, there are errors.

  3. When the form has been submitted, all the form fields will be set (except for un-checked checkbox and radio buttons, which you don’t have any of.) Your current logic using isset() isn’t doing anything useful and all the queries are being executed. After validating the data, you should produce and execute one UPDATE query. The brand, type, and model values being submitted should be (probably are) the ids. You should use a prepared query to supply the values to the sql query statement.

Next, you need some consistent error handling for all the database statements (connection, queries), that doesn’t output the raw error messages on a live/public server. The easiest way of doing this is to use exceptions and let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. Error_reporting should always be set to E_ALL. Display_errors should be set to ON, when learning, developing, and debugging code/queries. Display_errors should be OFF and log_errors should be ON, when on a live/public server.

Te enable exceptions for the mysqli extension, add the following line before where you make the connection and remove any or die(…) statements there may be now -

[php]mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);[/php]

Lastly, any data values that you output on a web page, that could contain any html entities (html/css/javascript), needs to have htmlentities() applies to it when it is output on the page in order to help prevent cross site scripting. What would happen if entries got inserted/updated in your database tables that contain html/css/javascript? You wouldn’t want that html/css/javascrpt to be rendered/executed when someone views any of the information.


#5

As to why the UPDATE query is not working, I suspect it’s due to the mixing of values, ids v.s. names. You are likely getting query errors, which enabling exceptions will tell you and which correcting what the code and queries are using for values will fix.Refer to the 4th and 5th paragraphs in the above reply.

You can use var_dump($_POST); to see what data is being submitted.


#6

I’ll toss in the required, use prepared statements token.

https://www.w3schools.com/php/php_mysql_prepared_statements.asp


#7

Wow, thanks for the reply. I somehow lost with all the tips provided… Will try to explore one at a time…