CODEWITHSUNDEEP
phpmysqlisql-updateprepared-statement
Owen Rees
Owen Rees

Reputation: 49

MySQL & PHP Update issue, using prepared statements

I have been asked to work on a project with someone (no prizes for guessing the industry from the code). While I am fine with the HTML and CSS of the site, this is really the first time I have tried to create an admin page where I can view, delete and update records.

My issue has come from trying to update a specific record which is linked to by the id being passed through the URL. The current data displays fine. When I try to add new data to the fields I get 'Undefined index' for all variables declared. No data is updated at all.

Previously, I had an issue whereby it looked as though the data would be fine updating, but all the fields for that particular record were rendered blank.

The 'action' part of the form tag has no page attached, as I assume that it doesn't need to point anywhere if the script is taking place on the same page.

The delete feature has been disabled. I assume the same issue will persist there too until this is recitifed.

Please be gentle with me.

<html>
<?php
    // declare connection variables
    $host = "localhost";
    $username = "root";
    $password = "";
    $dbname = "tsdb";
    $socket = "/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock";

    // DB Connect
    $mysqli = new MySQLi($host, $username, $password, $dbname, ini_get('mysqli.default_port'), $socket) or die ('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);

    // get URL record 'id'
    $id = $_GET['id'];

    // get result of record from 'id'
    $query = "SELECT * FROM models WHERE id =$id";
    $result = $mysqli->query($query);
    $row = $result->fetch_array();
    $result->free();
    ?>

    <!-- vertical table for record edit -->

    <div class="container-fluid">
        <div class="row">
            <div class="col-md-6">
                <h3>Edit Details</h3>

                <form class="form-group" id="editRecord" action="#" method="post">
                    <table class="table table-striped">
                        <tr>
                            <th scope="row">Record ID:</th>

                            <td><?php echo $row['id']; ?></td>
                        </tr>

                        <tr>
                            <th scope="row">Display Name:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['name']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Featured:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['featured']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Sex:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['sex']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Age:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['age']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Ethnicity:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['ethnicity']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Contact Number:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['number']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Email:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['email']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Display Email ?:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['displayEmail']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Postcode:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['postcode']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Nearest Station:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['station']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Parking:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['parking']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Shower:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['shower']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Outcalls:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['outcalls']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Price:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['price']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Heading:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['heading']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Bio:</th>

                            <td><input type='text' class='form-control' value="<?php echo $row['bio']; ?>"></td>
                        </tr>

                        <tr>
                            <th scope="row">Created:</th>

                            <td><?php echo $row['created']; ?></td>
                        </tr>

                        <tr>
                            <th scope="row">Last Updated:</th>

                            <td><?php echo $row['updated']; ?></td>
                        </tr>
                    </table>

                    <div class="btn-group">
                        <button class="btn btn-default" name="update" type="submit">Update Record</button> 
                        <button class="btn btn-default" name="delete" type="submit">Delete Record</button>
                    </div>

                </form>
            </div>
        </div>
    </div>

    <?php   

    // button execute code
    if (isset($_POST['update'])) {

        $name = $_POST['name'];
        $featured = $_POST['featured'];
        $sex = $_POST['sex'];
        $age = $_POST['age'];
        $ethnicity = $_POST['ethnicity'];
        $number = $_POST['number'];
        $email = $_POST['email'];
        $displayEmail = $_POST['displayEmail'];
        $postcode = $_POST['postcode'];
        $station = $_POST['station'];
        $parking = $_POST['parking'];
        $shower = $_POST['shower'];
        $outcalls = $_POST['outcalls'];
        $price = $_POST['price'];
        $heading = $_POST['heading'];
        $bio = $_POST['bio'];

        $updateQuery = "UPDATE models SET name=?, featured=?, sex=?, age=?, ethnicity=?, number=?, email=?, displayEmail=?, postcode=?, station=?, parking=?, shower=?, outcalls=?, price=?, heading=?, bio=? WHERE id = 'id'"; 

    $updatestmt = $mysqli->prepare($updateQuery);                    
    $updatestmt->bind_param('sssissssssssssss', $name, $featured, $sex, $age, $ethnicity, $number, $email, $displayEmail, $postcode, $station, $parking, $shower, $outcalls, $price, $heading, $bio);

    $updatestmt->execute();
    $updatestmt->close();
    }
    elseif (isset($_POST['delete'])) {
        $deleteAlert = "Feature not available!";
        echo "<script type='text/javascript'>alert('$deleteAlert');</script>";  
    };
    $mysqli->close();
    ?>
</body>
</html>

Upvotes: 2

Views: 109

Answers (1)

Droid
Droid

Reputation: 569

You don't have specified name for input tag, it should be <input name="featured" as example

Upvotes: 1

Related Questions