How to update multiple data in one row in php?

Question

I have worked some code where I need a user who is logged in to update his details. When the use clicks the update button it only one field which is updating data and the rest are not. I have 6 fields to update, one is doing well and the other 5 are not. I am not sure where I am wrong with my code below:

<?php

//all errors
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);



// Initialize the session
session_start();
 
// Check if the user is logged in, otherwise redirect to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}
 
// Include config file
require_once "config.php";
 
// Define variables and initialize with empty values
$investment = $cleared = $balance = $refnumber = $refbalance = $monthgain = "";
$investment_err = $cleared_err = $balance_err = $refnumber_err = $refbalance_err = $monthgain_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Validate fields
    if(empty(trim($_POST["investment"]))){
        $investment_err = "Iv error";     
    }if(empty(trim($_POST["cleared"]))){
        $cleared_err = "Cleared error";     
    }if(empty(trim($_POST["balance"]))){
        $balance_err = "Balance error";     
    }if(empty(trim($_POST["refnumber"]))){
        $refnumber_err = "Ref num error";     
    }if(empty(trim($_POST["refbalance"]))){
        $refbalance_err = "Ref bal error";     
    }if(empty(trim($_POST["monthgain"]))){
        $monthgain_err = "Monthgain error";     
    } else{
        $investment = trim($_POST["investment"]);
        $cleared = trim($_POST["cleared"]);
        $balance = trim($_POST["balance"]);
        $refnumber = trim($_POST["refnumber"]);
        $refbalance = trim($_POST["refbalance"]);
        $monthgain = trim($_POST["monthgain"]);
    }
        
    // Check input errors before updating the database
    if(empty($investment_err) && empty($cleared_err) && empty($balance_err) && empty($refnumber_err) && empty($refbalance_err) && empty($monthgain_err)){
        // Prepare an update statement
        $sql = "UPDATE users SET investment = ?  WHERE id = ?";
        
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "si", $param_investment, $param_id);
            
            // Set parameters
            $param_investment = $investment;
            $param_cleared = $cleared;
            $param_balance = $balance;
            $param_refnumber = $refnumber;
            $param_refbalance = $refbalance;
            $param_monthgain = $monthgain;
            $param_id = $_SESSION["id"];
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // data updated successfully. Destroy the close page, and redirect to dashboard page
                header("location: ../dashboard.php");
                exit();
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
    }
    
    // Close connection
    mysqli_close($link);
}
?>

A field with name investment is the only one updating. see screenshot here

The fields marked with X are my problem! what is wrong with my code?

In your SQL string you only setting the investment with information none of the other fields are being populated — James Wright, Aug 23 '20 at 15:56

score -1 · Answer 1 · answered Aug 23 '20 at 16:01

-1

Try doing this

    UPDATE users SET 
        investment =  '".$investment."',
        cleared = '".$cleared."',
        balance = '".$balance."',
        refnumber = '".$refnumber."',
        refbalance = '".$refbalance."',
        monthgain = '".$monthgain."'
        WHERE id = ?

answered Aug 23 '20 at 16:01

James Wright

143
10

The cure is worse than the disease – Strawberry Aug 23 '20 at 18:09

score -2 · Answer 2 · answered Aug 23 '20 at 14:06

-2

You'll need SQL that looks something like this to update multiple columns with a single query.

UPDATE users 
   SET investment = ?,
       cleared = ?,
       whatever = ?,
       whatelse = ?
  WHERE id = ?";

Of course, use the actual column names from your table.

answered Aug 23 '20 at 14:06

O. Jones

103,626
17
118
172

I have done it and now all fields including the one that was working is no longer updating data – binoe Aug 23 '20 at 14:25
i think this is a good answer. @binoe did you change your code mysqli_stmt_bind_param to mysqli_stmt_bind_param($stmt, "si", $param_investment,$param_cleared, $param_balance ,(include other paramater) , $param_id); example https://stackoverflow.com/questions/6514649/how-to-prepare-statement-for-update-query – Ronny Sulistio Aug 24 '20 at 06:02

How to update multiple data in one row in php?

2 Answers2