Need help with Database editing

General PHP Help
#1

I am doing a project for school and got the job to make an admin page I followed this tutorial “How to update/edit data into database using PHP MySql - YouTube” but when I update the page I keep getting a message saying “Data not updated” I don’t know what I did wrong but can anyone maybe help me resolve the problem that would be great

Ps ignore the Database test at the end I was trying to see if it worked

<!doctype html>
<html lang="en">
<head>
    <style>
        ul {
            list-style-type: none;
            margin: 0;
            padding: 0;
            width: 100px;
            background-color: black;
        }

        li a {
            display: block;
            color: white;
            padding: 8px 16px;
            text-decoration: none;
        }

        li a:hover {
            background-color: blue;
            color: white;
        }

        h1 {
            color: black;
        }

    </style>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

</head>
    <title>admin</title>
</head>
<script>
    function createItem(name, description) {
        console.log()
    }
</script>
<body style="background-color:white">
        <h1>Admin Page</h1>

        <ul>
            <li><a href="adminpage.php">Home</a></li>
            <li><a href="edit_item.php">Edit</a></li>
            <li><a href="https://mail.google.com/mail/u/0/#inbox" target="_blank">E-mails</a></li>
        </ul>

        <div class="container">
        <div class="col-lg-4">
            <h2>Updating Database by PHP</h2>
            <form action="" name="form1" method="post">
                <div class="form-group">
                    <label for="Product ID">Product ID</label>
                    <input type="text" class="form-control" id="product_id" placeholder="Enter product id" name="product_id">
                </div>
                <div class="form-group">
                    <label for="Product Type">Product Type</label>
                    <input type="text" class="form-control" id="product_type" placeholder="Enter product type" name="product_type">
                </div>
                <div class="form-group">
                    <label for="Item Name">Item Name</label>
                    <input type="text" class="form-control" id="name" placeholder="Enter product name" name="name">
                </div>
                <div class="form-group">
                    <label for="pwd">Item description</label>
                    <input type="text" class="form-control" id="Description" placeholder="Enter item description" name="Description">
                </div>
                <div class="form-group">
                    <label for="Image URL">Image URL</label>
                    <input type="text" class="form-control" id="img_url" placeholder="Enter Image URL" name="img_url">
                </div>
                <div class="form-group">
                    <label for="Price">Price</label>
                    <input type="text" class="form-control" id="price" placeholder="Enter item price" name="price">
                </div>
                <div class="checkbox">
                    <label><input type="checkbox" name="remember"> Remember me</label>
                </div>
                <button type="submit" name="update" class="btn btn-default">Update</button>
                <button type="submit" name="create" class="btn btn-default">Create</button>
                <button type="submit" name="delete" class="btn btn-default">Delete</button>
            </form>
        </div>
        </div>

        <div class="col-lg-12">

        </div>

        <?php
        $connection = mysqli_connect("localhost","root","");
        $db = mysqli_select_db($connection,'bit_academy');

        if(isset($_POST['update']))
        {
            $product_id = $_POST['product_id'];

            $query = "UPDATE products SET product_id='$_POST[product_id]',product_type='$_POST[product_type],name='$_POST[name]',Description='$_POST[Description]',img_url='$_POST[img_url]',price='$_POST[price]',' WHERE id='$_POST[product_id]' ";
            $query_run = mysqli_query($connection, $query);
            if ($query_run)
            {
                 echo '<script type="text/javascript"> alert("Data Updated")</script>';
            }
            else
            {
                echo '<script type="text/javascript"> alert("Data Not Updated")</script>';
            }
        }
        //Database Test
        #require_once "../php/Database.php";
        #    $db = new Database("localhost", "bit_academy", "3306", "root", "");
        #    $db->checkConnectionToDatabase();
        #    echo "<pre>";
        #    print_r($db->getTableByName("products"));
        #    $db->c("cap", "t", "t", "t", "10.00");
        #    echo "</pre>";
?>
</body>
</html>
#2

Well, do you want to learn how to do it correctly? Or just to get thru the class.
To get thru the class, you have quotes around the WHERE clause. Therefore it will not work. Remove the comma and quotes on the WHERE clause…

Now, to do it correctly, you should not just take the data as-is from the posted values. Hackers can put code into your inputs to delete your database tables or other such horrible code. How to fix that is to filter the inputs. Here is an example… ( Oh, usually you put your php code at the beginning of the page so it

 <?PHP
 if($_SERVER['REQUEST_METHOD'] == 'POST') {
     //  User posted the page
     if(isset($_POST['update']) {
        //  User pressed the 'update' button
        $product_id = filter_input(INPUT_POST, 'product_id');
        $product_type = filter_input(INPUT_POST, 'product_type');
        etc...
    }
}

Handling the data in this manner protects the server from bad inputs. The first line checks to see if the user posted the page. No sense checking if the ‘update’ button was pressed if the user did not press any button. This would all be placed at the top of the page so that it is run before the user sees the page. That way the server has the new data already in place and you can display the updated data later in the page. If you put it at the end of the page, the user sees the old data displayed first and not the new version.

Hope this helps and makes sense to you… Good luck!

#3

Sorry when I click on update it clears the form and thats good but it doesnt edit database dont i need to call the products table maybe anywhere? I got this right now in the php

<?PHP
$connection = mysqli_connect("localhost","root","");
$db = mysqli_select_db($connection,'bit_academy');

if($_SERVER['REQUEST_METHOD'] == 'POST') {
    //  User posted the page
    if(isset($_POST['update'])) {
        //  User pressed the 'update' button
        $product_id = filter_input(INPUT_POST, 'product_id');
        $product_type = filter_input(INPUT_POST, 'product_type');
        $name = filter_input(INPUT_POST,'name');
        $Description = filter_input(INPUT_POST,'Description');
        $img_url = filter_input(INPUT_POST,'img_url');
        $price = filter_input(INPUT_POST,'price');
    }
}
?>
#4

@DarkIcey, please use either bbcode [code][/code] tags or markdown three back-ticks ``` before and after your code to cause it to be formatted and color highlighted. I have kindly added these to your posts above.

Sadly, using filter_input() without specifying a filter does nothing, is unsafe, and returns the raw data. From the php documentation -

If omitted, FILTER_DEFAULT will be used, which is equivalent to FILTER_UNSAFE_RAW. This will result in no filtering taking place by default.

You should in fact (other than trimming data, so that you can detect if all white-space characters were entered), not alter user input. You should instead validate user input, to make sure it satisfies the needs of your application (not empty, has the correct format, …), and only use it if it is valid. If it is not valid, you should set up and display a message telling the user exactly what was wrong with it. A legitimate user entered something they wanted to use for one of the fields. By altering it, without telling the user what you changed in it, changes the meaning of the data from what the user intended.

The work-flow for editing data involves listing the existing records, each with an ‘edit’ button that when clicked causes the corresponding row of data to be retrieved and used to populate the form field values. You would then be able to change the data fields (not the product_id, which should be put into a hidden field) and submit the form to cause the new values to be updated into the correct row in the database.

If the product_type input is selecting from existing choices, the form field should be a select/option menu, with the current choice pre-selected, rather than a text input.

If you are just starting out, you should start with one single data field (plus the product_id auto-increment field.) Once you get your program logic to work for inserting, updating, deleting, and retrieving/displaying data (for one specific row and for all the rows), for this single data field, you can worry about the code needed for the rest of the fields.

You should also be using a prepared query when supplying external, unknown, dynamic data values to the query when it gets executed, to protect against sql special characters in the data from breaking the sql query syntax, which is how sql injection is accomplished. This actually simplifies the sql query syntax, making it easier to write error free queries (which would help with the current problem in the update query in your code.)

#5

Well, actually, FILTER_UNSAFE_RAW does do some simple protection. These flags are in use for it:
FILTER_FLAG_STRIP_LOW,
FILTER_FLAG_STRIP_HIGH,
FILTER_FLAG_STRIP_BACKTICK,
FILTER_FLAG_ENCODE_LOW,
FILTER_FLAG_ENCODE_HIGH,
FILTER_FLAG_ENCODE_AMP

But, this does not matter at all if you are using PREPARED-STATEMENTS.

The problem, DarkIcey, is that you used my example exactly. You did not do the ETC part. Once you acquire your inputs, you need to update the database. You are missing that in your last post.

    <?PHP
    $connection = mysqli_connect("localhost","root","");
    $db = mysqli_select_db($connection,'bit_academy');

    if($_SERVER['REQUEST_METHOD'] == 'POST') {
        //  User posted the page
        if(isset($_POST['update'])) {
            //  User pressed the 'update' button
            $product_id = filter_input(INPUT_POST, 'product_id');
            $product_type = filter_input(INPUT_POST, 'product_type');
            $name = filter_input(INPUT_POST,'name');
            $Description = filter_input(INPUT_POST,'Description');
            $img_url = filter_input(INPUT_POST,'img_url');
            $price = filter_input(INPUT_POST,'price');
            //  All data is acquired, now update the database
            $query = "UPDATE products SET product_id=$product_id, product_type=$product_type, name=$name,Description='$Description]',img_url='$img_url',price='$price' WHERE id='$product_id';
            $query_run = mysqli_query($connection, $query);
        }
    }
    ?>

Of course, this does not protect your database from hackers. You need to learn the basics of PREPARED STATEMENTS. To change your query in this example to use prepared statements, it would look something like this:
// Prepare the query and Bind the data and execute it
$query = $connection->prepare("UPDATE products SET product_type=?, name=?, description=?,img_url=?,price=? WHERE product_id=?;
$query->bind_param(“sssssi”, $product_type, $name, $description, $img_url, $price, product_id);
$query->execute();
**** The update that you first posted is odd. If you already have a product_id in the table, then you do not need to update it. But, you do need to select the row of data to be updated by using an id. This id would be set when you create a new product and deleted when you delete it. Make sense? For learning, you do not need prepared statements. But, you must use them in live sites, so why not learn them now…

Sponsor our Newsletter | Privacy Policy | Terms of Service