Updating the table in MySQL using PHP.

[ak47]

Hi. I wish to update table "users" in database(MySQL) "videos". I have posted my code below. Here I am not getting any errors but the updation is not haapenning. So, please help me to figure it out.

Thank You,

<?php
			$conn = mysqli_connect("localhost", "root", "", "videos") or die(mysqli_error());
			$id = (isset( $_GET["Id"]) ? $_GET["Id"] : '');
			$_SESSION['Id'] = $id;
			$id_exists = true;
			$query = mysqli_query($conn, "SELECT * FROM users WHERE Id='$id'"); //Query for the users table
				if($id_exists)
				{
					while($row = mysqli_fetch_array($query))
						{
		?>
				<form action="edit.php" method="POST">
			   
					<div>
						<div class="form-group">
							<label>Company</label>
							<input type="text" style="width:60%;" class="form-control" disabled placeholder="Company" value="CSD Technologies Pvt Ltd" />
						</div>
					</div>
					<div>
						<div class="form-group">
							<label>Username</label>
							<input required="required" style="width:60%;" name="Username" type="text" value="<?php echo $row['Username']; ?>" class="form-control" />
						</div>
					</div>
					<div>
						<div class="form-group">
							<label for="exampleInputEmail1">Email address</label>
							<input type="email" style="width:60%;" name="Email" class="form-control" value="<?php echo $row['Email']; ?>" required="required"/>
						</div>
					</div>
				
				
					<div>
						<div class="form-group">
							<label>Employee ID</label>
							<input type="text" style="width:60%;" name="EmployeeID" class="form-control" value="<?php echo $row['EmployeeID']; ?>" required="required"/>
						</div>
					</div>
					<div>
						<div class="form-group">
							<label>Designation</label>
							<input type="text" style="width:60%;" name="Designation"  required="required" class="form-control" value="<?php echo $row['Designation']; ?>"/>
						</div>
					</div>
				
				
					<div>
						<div class="form-group">
							<label>Password</label>
							<input type="text" style="width:60%;" name="Password" class="form-control" value="<?php echo $row['Password']; ?>" required="required"/>
						</div>
					</div>
					
					<input type="submit" value="Update" class="btn btn-info btn-fill pull-right"/>
					<div class="clearfix"></div>
				</form>
				
		<?php
			$username = (isset($_POST['Username']) ? $_POST['Username'] : '');
			$email = (isset($_POST['Email']) ? $_POST['Email'] : '');
			$employee = (isset($_POST['EmployeeID']) ? $_POST['EmployeeID'] : '');
			$designation = (isset($_POST['Designation']) ? $_POST['Designation'] : '');
			$password = (isset($_POST['Password']) ? $_POST['Password'] : '');
			}
			}
			else
			{
				Print '<h4 align="center">There is no data to be edited.</h4>';
			}
		
		?>



<?php
	if($_SERVER['REQUEST_METHOD'] == "POST")
	{
		$conn = mysqli_connect("localhost", "root", "", "videos") or die("Connection Server" . mysqli_error());
		$username = $_POST['Username'];
		$email = $_POST['Email'];
		$employee = $_POST['EmployeeID'];
		$designation = $_POST['Designation'];
		$password = $_POST['Password'];
		$id = $_SESSION['Id'];
		$query = mysqli_query($conn, "UPDATE users SET Username='$username', Email='$email', EmployeeID='$employee', Designation='$designation', Password='$password' WHERE Id='$id'");
		echo "Records were updated successfully.";
		header("location:settings.php");
	}
?>

[DyDr]

You need to use some basic troubleshooting to find out what is actually occurring -

1) Do you have php's error_reporting set to E_ALL and display_errors set to ON, so that php will help you by reporting and displaying all the errors it detects? If the form and form processing code are in two separate files (there's no way to tell based on what you have posted), you would be getting some php error(s) that would help you find what is causing the problem. If all the code is in one file, you won't get any php errors related to the most likely cause of the problem.

2) Temporarily comment out the header(...) redirect. Php, unfortunately, has a php.ini setting (output_buffering) that will allow a header() to work, even though you have output something to the browser before the header() statement, and if the header() redirect is working, you will never see any messages or php errors from your code. You should also set the output_buffering setting to OFF.

3) Have you checked if the form processing code is running? Do you see the "Records were updated successfully." message that's being echoed by the code?

4) You need to have error handling logic for all your database statements. The sql query statement could be failing due to an error, especially since the data being put into the query isn't having anything done to prevent any sql special characters in it from breaking the sql query syntax. The easiest way of adding error handling for all your database statements is to use exceptions. In fact, the error handling you have for the connection is not correct now (the msyqli_error() statement requires the database connection as a parameter, so, both the use of mysqli_error() is wrong and it cannot be used to report connection errors) and needs to be fixed, or just removed once you enable exceptions.

To enable exceptions for the php mysqli extension, simply add the following two lines of code before the point where you make the database connection -

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // MYSQLI_REPORT_ALL <- w/index checking; w/o index checking -> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; 

Once you enable exceptions for the php msyqli extension, any database statement errors will throw an exception. If you simply let php catch the exception, combined with the suggested error_reporting and display_errors settings, php will report and display the actual database statement errors for you.

5) Your code needs to validate the input data before using it and produce validation error messages for any data that isn't of an expected value, format, or type. If your code was already doing this, your code would be likely telling you why it isn't working.

6) You need to protect against sql special characters in the data (quotes, new-lines, nulls) from breaking the sql syntax (which is also how sql injection is done.) There are two ways of doing this - 1) use the escape string function for the php database extension you are using, or 2) use prepared queries.

Unfortunately, the escape string functions have had a history of not working correctly and they also require that you set the character set being used by php properly. Prepared queries, with place-holders in the sql query statement for data values, and then supplying the data when you execute the query are the best way of providing this protection. While this sounds like an advanced subject, it is not really, and in fact, it simplifies the sql query syntax, making it easier to write error free sql query statements.

Unfortunately again, the php msyqli extension's prepared query implementation is not very well designed. If you can, switch to use the php PDO extension. If is better designed and easier to use then the php msyqli extension. Enabling exceptions for the php PDO extension is done differently then for the php msyqli extension.


Also, your program logic is doing some unnecessary things, that don't make any sense or are just cluttering up your code -

1) There's no point is copying the id to the session variable.
2) The $id_exists variable and logic using it isn't doing anything useful.
3) There's no point in loop over a query result that will at most match one row. Just fetch the row.
4) All the code with the isset() statements isn't being used. You actually do need some logic to supply values to populate the form fields, first with the data from the SELECT query, then with the form data after the form has been submitted and there are validation errors, but worry about this after you fix all the other problems.
5) If all this code is in one file, you should only make one database connection. Don't repeat code.
6) Copying $_POST variables to other variables is a waste of time. Just use the $_POST variables in your code.
7) The "Records were updated successfully." message should only be output if the update query actually does update the row. You can get the number of rows updated (it should be 1 for what you are doing) and use that to condition the output of the success message.
8) Your code needs some access logic, so that only a logged in user or an administrator can edit the selected user's data.
9) If the form and form processing code is in the same file, the form processing code needs to be above the form, so that you can re-display the form if there are validation errors.

[rubyOnPails]

First of all, you are VERY susceptible to MySQL injection. Please read this article on MySQLi Prepared Statements. As for figuring out why your update is working, you need to do some error handling, as DyDr stated. Here's an example how to error handling without prepared statements. Since, mysqli_query returns true or false, you can simple wrap around an if like this.

$query = mysqli_query($conn, "UPDATE users SET Username='$username', Email='$email', EmployeeID='$employee', Designation='$designation', Password='$password' WHERE Id='$id'");
if(!$query) $con->errno;

Now the prepared statements way

if ( !$stmt = $con->prepare("SELECT * FROM myTable WHERE name=?") ) 
 echo "Prepare Error: ($con->errno) $con->error";
if ( !$stmt->bind_param("s", $_POST['name']) )
  echo "Binding Parameter Error: ($con->errno) $con->error";
if ( !$stmt->execute() ) 
 echo "Execute Error: ($stmt->errno)  $stmt->error";

That said, please read the article and switch to prepared statements. You'll thank me for it .

[ak47]

@DyDr, Thank you. It really worked very well. And once again thanks a lot for valuable information.