PHP and SQL Prepared Statements - Update not working - Hack The Tech - Latest News related to Computer and Technology

Hack The Tech - Latest News related to Computer and Technology

Get Daily Latest News related to Computer and Technology and hack the world.

Saturday, June 12, 2021

PHP and SQL Prepared Statements - Update not working

I am working on a project using a web user interface and Mariadb server. I am not an experienced programmer, but am loving the experience this project is giving me. I'm sure I will learn tons through your comments.

Problem: In this section of the project, if the user makes a change to one of the fields the change is applied to "update_contacts.php" using jQuery. "update_contacts.php" is then supposed to save the change to the database. But, it's not working.

I do (almost) the exact same thing in another part of the project and it works great! I just can't seem to figure out why it's not working here.

What I've Tried:

  1. Nothing shows up in the log when I make a change to one of the form field values.
  2. I removed the form filter to display, and make sure I'm passing, all fields.
  3. I compared the code to the other similar code that is working and I see no difference at all.
  4. I tried narrowing the culprit down by commenting out several sections and re-writing in other ways; ex comment out the jQuery, use a submit button, and move the code from "update_contacts.php" to above the form code.
  5. I stared at the code for hours looking for a typo, reserved words, or syntax issues and I'm not seeing anything at all.

I'm really stuck. Thank you for your time and help! Your input is greatly appreciated!

Web Form:

<?php

$contid = $row['contid'];
$result5 = mysqli_query($link,"SELECT * FROM contacts_contacts WHERE contid='$contid'") or die('cannot show columns');

if(mysqli_num_rows($result5))
{
    ?>
    <form name="edit_contacts" id="edit_contacts" action="">
    <div>
        <table>
            <thead>
                <tr>
                    <th>Name</th><th>Email</th><th>Phone</th><th>Ext</th><th>Title</th>
                </tr>
            </thead>
            <?php
            while($row7 = mysqli_fetch_assoc($result5))
            {
                ?>
                <tr>
                    <?php
                    foreach($row7 as $key=>$value)
                    {
                        // Filter to display only certain fields
                        if (($key != 'ccid') && ($key != 'contid'))
                        {
                            ?>
                            <td><input type="text" name="<?php echo $key;?>" value="<?php echo $value;?>" id="<?php echo $key;?>" placeholder=""></td>
                            <?php
                        } elseif ($key == 'ccid')
                        {
                            ?>
                            <input type="hidden" id="ccid" name="ccid" value="<?php echo $row7["ccid"];?>">
                            <?php
                        } else
                        {
                            ?>
                            <input type="hidden" id="contid" name="contid" value="<?php echo $row["contid"];?>">
                            <?php
                        }
                    }
                    ?>
                </tr>
                <?php
            }
            ?>
        </table>
    </div>

    <script>
        // Automatically save changes
        $('#edit_contacts').on('keyup change', function()
        {
            $.ajax({
                type: "POST",
                url: "assets/php/contacts/update_contacts.php",
                data: $(this).serialize(),
                cache: false,
                success: function(html)
                {
                    $('#record-saved').show();
                },
                error: function(html)
                {
                    $('#record-error').show();
                }
            });
        });
    </script>
    </form>
    <?php
}
?>

update_contacts.php:

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);

// Connect to database
include ($_SERVER['DOCUMENT_ROOT'] . '/config.php');

/***** UPDATE RECORD *****/
if (isset($_POST['name']) && $_POST['name'] != '')
{
    // define variables
    $ccid = $contid = $name = $tel = $email = $title = $tel_ext = "";

    // Sanitize data
    function test_input($data)
    {
        $data = trim($data);
        $data = stripslashes($data);
        $data = htmlspecialchars($data);
        return $data;
    }

    // Replace entered data with sanitized version
    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {
        $ccid = test_input($_POST["ccid"]);
        $contid = test_input($_POST["contid"]);
        $name = test_input($_POST["name"]);
        $tel = test_input($_POST["tel"]);
        $email = test_input($_POST["email"]);
        $title = test_input($_POST["title"]);
        $tel_ext = test_input($_POST["tel_ext"]);
    }

    // Check if record exists
    $check=mysqli_query($link,"SELECT * FROM contacts_contacts WHERE ccid = '$ccid'");
    $checkrows=mysqli_num_rows($check);

    if($checkrows>0 && $name != NULL)
    {
        // If record exists, update data
        $stmt = "UPDATE contacts_contacts SET name=?, tel=?, email=?, title=?, tel_ext=?, contid=? WHERE ccid=?";

        if($sqlin = $link->prepare($stmt))
        {
            // Bind variables to the prepared statement as parameters
            $sqlin->bind_param("ssssiii", $name, $tel, $email, $title, $tel_ext, $contid, $ccid);
            $status = $sqlin->execute();

            // Check if prepared statement executed
            if ($status === TRUE)
            {
                ?>
                <div class="alert alert-success" role="alert">Record updated successfully.</div>
                <?php
            }else
            {
                ?>
                <div class="alert alert-danger" role="alert">ERROR! Record not updated.</div>
                <?php
            }

            // Close statement
            $sqlin->close();
        }
    }
}
?>


source https://stackoverflow.com/questions/67941777/php-and-sql-prepared-statements-update-not-working

No comments:

Post a Comment