How do I update a row of data in mysql using PHP?

Question

I'm attempting to create a form where the user can update their profile details but it just doesn't seem to work.

I'm quite the beginner in server side programming so I'm piecing together code from different tutorials viz. from http://www.codingcage.com/2015/04/php-login-and-registration-script-with.html

The class.user.php file, which originally only had the code for login, and signup. I copied the signup function and changed some stuff to update instead:

public function update($id,$uname,$umob,$uaddr,$uacc,$upass) {
    try {
        $upass = password_hash($upass, PASSWORD_DEFAULT);

        $stmt = $this->conn->prepare(
            "UPDATE users
                SET
                    id       = :id, 
                    name     = :uname, 
                    mobile   = :umob,
                    address  = :uaddr,
                    accNo    = :uacc, 
                    password = :upass
              WHERE id = :id"
        );

        $stmt->bindParam(":id", $id);
        $stmt->bindParam(":upass", $upass);
        $stmt->bindParam(":uacc", $uacc);                                         
        $stmt->bindParam(":uname", $uname);
        $stmt->bindParam(":uaddr", $uaddr); 
        $stmt->bindParam(":umob", $umob);

        $stmt->execute();   

        return $stmt;   
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }               
}

and in view_account.php: (edit 3, whole file including code corrections by @e_i_pi):

<?php
ini_set("error_log", "/path/to/error.log");
    require_once("session.php");

    require_once("class.user.php");

    $auth_user = new USER();

    $stmt = $auth_user->runQuery("SELECT * FROM users WHERE consumer-no=:cno");

    $userRow = $stmt->fetch(PDO::FETCH_ASSOC);

    if(!$session->is_loggedin()){
        // session no set redirects to login page
        $session->redirect('index.php');
    }

    if(isset($_POST['submit']) && $_POST['submit'] === 'save') {
        $uname = strip_tags($_POST['full-name']);
        $umob = strip_tags($_POST['mobile']);
        $uaddr = strip_tags($_POST['addr']);
        $uacc = strip_tags($_POST['bank-acc']);
        $id = strip_tags($_POST['id']);
        $upass = strip_tags($_POST['password']);


        if($uname=="") {
            $signuperror[] = "Please Enter Your Full Name!";    
        }
        else if($umob=="")  {
            $signuperror[] = "Please Enter Your Mobile No.!";   
        }
        else if($uaddr=="") {
            $signuperror[] = 'Please Enter Your Address!';
        }
        else if($upass=="") {
            $signuperror[] = "Please Enter a Password!";
        }
        else if(strlen($upass) < 6) {
            $signuperror[] = "Password must be atleast 6 characters";   
        }
        else {
            try {
// I commented out these for some weird reason I can't even rememebr
//                $stmt = $auth_user->runQuery("SELECT id FROM users WHERE id=:id");
//                $stmt->execute(array(':id'=>$id));
//                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                $auth_user->update($id,$uname,$umob,$uaddr,$uacc,$upass);
            }
            catch(PDOException $e) {
                echo $e->getMessage();
            }
        }   
    }

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Gas Booking</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <header>
        <h1>gas booking</h1>
        <nav>
            <ul>
                <li><a href="index.php">home</a></li>
                <li><a href="booking.php">booking</a></li>
                <li><a href="payment.php">payment</a></li>
                <li><a href="ticket.php">ticket</a></li>
                <li><a href="view_account.php">view account</a></li>
                <li><a href="user-bank.php">bank</a></li>
                <li><a href="logout.php?logout=true">logout</a></li>
            </ul>
        </nav>
    </header>
    <div class="content">
        <h2>Edit Your Profile Details</h2>

        <form method="post" action="view_account.php">
            <input type="hidden" id="id" name="id" value="<?php echo $_SESSION['id']; ?>">
            <label for="full-name" class="input-info">
                <div class="label">full name</div>
                <input type="text" id="full-name" name="full-name" value="<?php echo $_SESSION['name']; ?>">
            </label>
            <label for="mobile" class="input-info">
                <div class="label">mobile number</div>
                <input type="text" id="mobile" name="mobile" value="<?php echo $_SESSION['mob']; ?>">
            </label>
            <label for="addr" class="input-info">
                <div class="label">address</div>
                <input id="addr" name="addr" value="<?php echo $_SESSION['addr']; ?>">
            </label>
            <label for="bank-acc" class="input-info">
                <div class="label">bank account number</div>
                <input type="text" id="bank-acc" name="bank-acc" value="<?php echo $_SESSION['accNo']; ?>">
            </label>
            <hr>
            <label for="password" class="input-info">
                <div class="label">password</div>
                <input type="password" id="password" name="password">
            </label>
            <button type="submit" name="submit" value="save">
                Save Changes
            </button>
        </form>     
    </div>
</body>
</html>

and my table is as follows:

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `consumerNo` varchar(15) NOT NULL,
  `password` varchar(255) NOT NULL,
  `accNo` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `mobile` bigint(10) NOT NULL,
  `balance` bigint(10) NOT NULL,
  `joining_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I'm sure I've done something stupid. I'd really appreciate pointing me in the right direction, I sat with it till 5:00am and am feeling frustrated with myself.

The connection with the db is working, classes are properly included. Let me know if you need more information. Thank you!

The project can be downloaded here: https://www.dropbox.com/s/9v69m18l82n1t46/gas.zip?dl=0. Warning the code's kind of a mess.

Darren · Accepted Answer

Update

You seem to be doing the following in view-account.php:

try {
    $auth_user->update($id,$uname,$umob,$uaddr,$uacc,$upass);
} catch(PDOException $e) {
    echo $e->getMessage();
}

Yet you're already try/catch'ing within your update() method. I assume it never gets to this as an error in your if/elseif/elseif/else/etc checks is picked up. Could you modify it to look like this for testing purposes:

$errors = [];
if ($uname == "") {
    $errors[] = "Please Enter Your Full Name!";
}
if ($umob == "") {
    $errors[] = "Please Enter Your Mobile No.!";
}
if ($uaddr == "") {
    $errors[] = 'Please Enter Your Address!';
}
if ($upass == "") {
    $errors[] = "Please Enter a Password!";
}
if (strlen($upass) < 6) {
    $errors[] = "Password must be atleast 6 characters";
}
// check errors
if (!empty($errors)) {
    print_r($errors);
    return false;
}
// otherwise try the query:
$obj = $auth_user->update($id, $uname, $umob, $uaddr, $uacc, $upass);

and let us know what comes up!

I assume you'd have an error thrown, something along the lines of;

SQLSTATE[HY093]: Invalid parameter number

This is because you're trying to bind on :id twice. You have to remember that a users ID is unique and should never change (right?).

Modify your query to look like this:

$stmt = $this->conn->prepare(
    "UPDATE users
     SET
         name = :uname, 
         mobile = :umob,
         address = :uaddr,
         accNo = :uacc, 
         password = :upass
      WHERE id = :id"
);

Notes

You're best to modify your "password change" functionality to have the user confirm their password (if(PASSWORD == PASSWORD_REPEAT) { .... SET PASSWORD...)
Don't pass the user ID inside the form. It's insecure. Since it's in $_SESSION already, simply access it like that within your view-account.php file!
- Why the above you ask? Simple. If I inspect your <form... element, I could easily modify that hidden input to be some other users ID, allowing me to change their passwords/information/etc.
- And since it looks like you're dealing with "Bank" related information, I'd suggest doing this asap! Imagine If I could change "Barack Obama's" bank password and access his account.
Your form also doesn't have any action attribute...so it does nothing.. Best change that to your view-account.php page
I suggest removing your use of strip_tags(). It could ruin some fields (i.e. passwords). You're also already binding/preparing your statements (Props to you on that, good work!)
While we're at it, you might want to look at your view-account.php file, It could be modified to stop the use of all those if / elseif / elseif / else statements. You're essentially checking all your fields and if it fails, you're adding an error message to an array, but if it passes you're running the query, this is bad practice. You should look at doing something similar to (pseudo code):

$errors = [];
if (!check_fields()) {
    $errors[] = THE FIELD ERROR MESSAGE;
}

// now check if your errors are empty or not
if(!empty($errors)) {
    // this means we have errors in the form.
    // return the errors array to the front end and handle it appropriately.
    return $errors;
}

// otherwise we can try the query here now!
try {
    // YOUR SQL UPDATE QUERY
} .....`

e_i_pi · Answer

Righto, you have a few problems with things not matching up etc., which is to be expected if you are starting out.

Let's start with the HTML form. There are two issues here:

The form has no action property, so it doesn't get submitted anywhere
The submit button is given a specific name and no value. (While some will consider this okay, maybe we can try a different approach which is a little more sensible)

I would suggest your HTML form be changed to this:

<form method="post" action="view-account.php">
    <input type="hidden" id="id" name="id" value="<?php echo $_SESSION['id']; ?>">
    <label for="full-name" class="input-info">
        <div class="label">full name</div>
        <input type="text" id="full-name" name="full-name" value="<?php echo $_SESSION['name']; ?>">
    </label>
    <label for="mobile" class="input-info">
        <div class="label">mobile number</div>
        <input type="text" id="mobile" name="mobile" value="<?php echo $_SESSION['mob']; ?>">
    </label>
    <label for="addr" class="input-info">
        <div class="label">address</div>
        <input id="addr" name="addr" value="<?php echo $_SESSION['addr']; ?>">
    </label>
    <label for="bank-acc" class="input-info">
        <div class="label">bank account number</div>
        <input type="text" id="bank-acc" name="bank-acc" value="<?php echo $_SESSION['accNo']; ?>">
    </label>
    <hr>
    <label for="password" class="input-info">
        <div class="label">password</div>
        <input type="password" id="password" name="password">
    </label>
    <button type="submit" name="submit" value="save">
        Save Changes
    </button>
</form>

Now, once this form is submitted to view-account.php, we want to make sure that the submit button is "save" mode, so we change the first line of view-account.php to this:

if(isset($_POST['submit']) && $_POST['submit'] === 'save') {

This approach means we can have different submit buttons on the same form - we may in future want actions for save, delete, archive, etc.

Lastly, I notice that the id field in your database table is declared AUTOINCREMENT. Great, exactly what you want, database id fields are internal unique identifiers that we let the database determine (in 99% of cases - there are edge cases where we like to define our own UIDs). This means that there is a problem with your UPDATE statement. You cannot update an auto-incremented field. In your class.user.php file, change the declaration of $stmt to this:

$stmt = $this->conn->prepare(
    "UPDATE users
    SET
        name = :uname, 
        mobile = :umob,
        address = :uaddr,
        accNo = :uacc, 
        password = :upass
    WHERE id = :id"
);

This should fix your code issues, I think I got everything. BUT, there may be other problems. If your code still does not work, I would suggest checking your error logs. If you're not sure where they are, either check your php.ini file to see what the error log location is, or override the default location by putting this at the top of the page you're trying to debug:

ini_set("error_log", "/path/to/error.log");

How do I update a row of data in mysql using PHP?

Tags:

php

mysql

pdo

carpenumidium

2 Answers

Update

Notes

Darren

e_i_pi

Recent Activity

Donate For Us

How do I update a row of data in mysql using PHP?

Tags:

php

mysql

pdo

carpenumidium

2 Answers

Update

Notes

Darren

e_i_pi

Related questions

Recent Activity

Donate For Us