Cannot edit database from PHP

Question

Here is what I try to use to edit entries in my database:

<?php
    $connect=mysqli_connect("localhost","root","");
    mysqli_select_db($connect,"bodylab");
    if (mysqli_connect_errno())
    {
        echo"Failed to connect to MySql: " .mysqli_connect_error();
    }


    $id=$_GET['idb'];
    $date=$_GET['date'];
    $usage=$_GET['usage'];
    $amount=$_GET['amount'];

    if (isset($_GET['edit']))
    {
        mysqli_query($connect,"UPDATE expand_p SET date='$date', usage='$usage', 
        amount=$amount WHERE id=$id");      
        header("location: admin_expand.php");
    }
    else if(isset($_GET['cancel']))
    {
        header("location: admin_expand.php");
    }
?>

I don't get an error message, it just doesn't work.

My table expand_p has those columns:

id(int), date(date), usage(varchar), amount(int)

What am I doing wrong here?

Can you be a little bit more specific about what is going wrong? — Derek Brown, Oct 12 '17 at 23:52
Also- this hopefully isn't an HTML file. It should be a `.php` file located on a PHP-enabled web server. — Derek Brown, Oct 12 '17 at 23:52
We are always glad to help and support coders, but this is not a free coding platform. You have to [do your research first](https://meta.stackoverflow.com/questions/261592/how). Try out how far you can get on your own. If you new here do the [tour](https://stackoverflow.com/tour), read [how to ask question](https://stackoverflow.com/help/how-to-ask) with [MVCE](https://stackoverflow.com/help/mcve) and post it here. Check also what kind of [question should be avoided](https://stackoverflow.com/help/dont-ask) and some of our [best practices](https://meta.stackoverflow.com/questions/347937/ask) — Webdesigner, Oct 12 '17 at 23:55
Also: [How to display errors for my mysqli query](https://stackoverflow.com/questions/17053466/how-to-display-errors-for-my-mysqli-query) — Ivar, Oct 12 '17 at 23:57
You won't get an error if you don't look for it, http://php.net/manual/en/mysqli.error.php. — chris85, Oct 13 '17 at 00:04

Sam · Answer 1 · 2017-10-13T12:10:01.710

First, let me address a few problem that you have in code, and I suggest really looking at them, and pondering upon how to solve them.

You're using procedural MySQL process (not really a problem, but I suggest using the OOP method as it's less wordy and easier to follow when debugging and passing your code to a different developer).
You are passing information through the URL... Extremely unsafe, it's easy to track user usage and steal information.
The code you have provided, can also be manipulated, I could go to the URL and add whatever parameters I want to...
I can also pass some code into your database; you are not filtering input. NEVER TRUST USER INPUT!
You are entering information directly into your query; it is unsafe and you are in danger of SQL injections.

Now let's get started with a piece of code that should fix your code; I will use the OOP method.

Create a new connection using our OOP method

$connect = new mysqli( 'localhost', 'root', '', 'bodylab' );
// check for an error
if ($this->_connection->connect_error)
{
    trigger_error("Connection Error: " . $this->_connection->connect_error(), E_USER_ERROR);
}

Once you have created the connection, now start with your code to update your database.

if ( isset( $_GET['edit'] ) && urlencode( $_GET['edit'] ) )
{
    // Encode the URL when creating the variables
    $id     = htmlentities( $_GET['idb'] );
    $date   = htmlentities( $_GET['date'] );
    $usage  = htmlentities( $_GET['usage'] );
    $amount = htmlentities( $_GET['amount'] );


    // create sql
    $sql = "UPDATE expand_p SET date = ?, usage = ?, amount = ?  WHERE id = ?";

    // prepare query
    if ($stmt = $connect->prepare( $sql ))
    {
        // bind the params
        $stmt->bind_param('sssi', $date, $usage, $amount, $id);
        // execute the query
        $stmt->execute();

        // check for errors
        if ($stmt->errno)
        {
            $message = array(
                'is_error' => 'danger',
                'message' => 'Error: ' . $stmt->error
            );
        }

        // make sure at least 1 or more rows were affected
        if ($stmt->affected_rows > 0)
        {
            $message = array(
                'is_error' => 'success',
                'message' => 'Success: ' . $stmt->affected_rows . ' rows were updated.'
            );
        }
        else
        {
            // if not, send warning to user
            $message = array(
                'is_error' => 'warning',
                'message' => 'Warning: ' . $stmt->affected_rows . ' rows were updated.'
            );
        }
        // close your connection
        $stmt->close();
    }
    else
    {
        $message = array(
            'is_error' => 'warning',
            'message' => 'There are no records in the `' . $table . '` table.'
        );
        exit;
    }
}

Please do not take the code as is, this is a quick solution which should solve your problem at hand; look at this as a learning script, and improve upon it.

To see any errors you can check for the $message variable and it will tell you where it has gone wrong.

// first check if variable exists
if ( isset( $message ) )
{
    // if it does print it
    print_r( $message );
}

Then your output should be an array with is_error and message keys, you will see the error and you can trace back to where it happened in the process.

Cannot edit database from PHP

1 Answers1