Updating a MySQL table with MySQLi

Below code sample is posted as an answer to this question posted on Facebook groups.

Create a new database with phpMyAdmin and execute below SQL query in its SQL tab to create a new table called ‘notes’. It will have three fields. id, title and contents.

CREATE TABLE `notes` ( `nid` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `contents` TEXT NOT NULL, PRIMARY KEY (`nid`)) ENGINE = InnoDB;

Then’ll lets populate it by adding a single record. Again copy and paste below code in the SQL tab:

INSERT INTO `notes` (`nid`, `title`, `content`) VALUES (NULL, 'Dummy Title', 'Dummy Description');

Once you got your database table ready, create a new PHP script in your web server’s document root and add below code:

<?php

	$con = mysqli_connect('localhost', 'yourusername', 'yourpassword', 'yourdatabasename');

	if(!$con)
	{
		echo 'Error: Unable to connect to MySQL.' . PHP_EOL;
		die();
	}


	if($_SERVER['REQUEST_METHOD'] == 'POST')
	{
		$nid = (int)$_POST['nid'];

		$title = filter_var($_POST['title'], FILTER_SANITIZE_STRING);

		$content = filter_var($_POST['content'], FILTER_SANITIZE_STRING);

		$error = 0;
		$message = '';

		if(empty($title) || empty($content))
		{
			$error = 1;
			$message = '<span style="color: red;">Please fill both fields!</span>';
		}

		if(!$error)
		{
			// update

			if(mysqli_query($con, "UPDATE notes SET content = '$content', title = '$title' WHERE nid = $nid"))
			{
				$message = '<span style="color: green;">Notes updated successfully!</span>';
			}
			else
			{
				$message = '<span style="color: red;">Unable to update!</span>';
			}
		}
	}
	else
	{
		// get post

		$data = mysqli_fetch_object(mysqli_query($con, 'SELECT * FROM notes WHERE nid = 1'));


		$title = $data->title;
		$content = $data->content;
		$nid = $data->nid;

		//print_r($data);
	}

	

?>
<html>
<head>
	<title>MySQLi test</title>
	<style>
		.wrapper{
			width: 500px;
			margin: 0 auto;
			background: antiquewhite;
			padding: 10px;
			padding-left: 121px;
		}
	</style>
</head>
<body>
	<div class="wrapper">
		<h4>Please fill and submit</h4>
		<p><?php echo (isset($message) ? $message : '') ?></p>
		<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
			<label>Title<br><input type="text" name="title" value="<?=(isset($title) ? htmlspecialchars($title) : '')?>"></label>
			<br>
			<br>
			<label>Description<br>
				<textarea name="content" rows="5" cols="50"><?=(isset($content) ? htmlspecialchars($content) : '')?></textarea>
			</label>
			<br>
			<br>
			<input type="hidden" name="nid" value="1">
			<input type="submit" name="submit" value="Submit">
		</form>
	</div>
</body>
</html>

Explanation

In line 3 we have mysqli_connect() function that tries to establish a connection to our database.

In line 5 we check to see if the connection has failed. If it is, then we’ll display an error message and stop the execution of the script with die() function.

If the connection was successful then the failure check will be skipped and the execution will jump into line 12 where we check if there is a form submission. If it is a post submission we take the form input into three variables. $nid, $title and $content. You can see we are already sanitizing the input to filter out some unwanted input. Then the code proceed to validate the input. It checks to see if $title and/or $content variables are empty. If it is so, it will set the $error variable to 1. Also sets a message to display to the user.

On line 29, the script checks if the $error is not true. That indicates there was no validation errors. If it does, we have asked the script to update the database record with the newly submitted data by using mysqli_query() function. If the update was successful, we set a message saying it was successful. If it has failed, again we’ve set a relevant message indicating there was a failure.

In line 43, the else clause is defines what actions to take if the form was not submitted. Here we’ve used mysqli_fetch_object() function to query the notes table for the record which has nid = 1 and assign the resulting information to three variables.

php processing ends on line 59. Then starts the html contents where we’ll show the form to the visitors. on line 76 the script check to see if $message variable is set. If it does shows it with echo statement. Form’s action is set to $_SERVER[‘PHP_SELF’] which means the form will be submitted to same script. The title input field’s value is set dynamically by checking if $title is set. If it does, echo out the $title variable through htmlspecialchars() function that removes any special characters for security reasons. Pretty much the
same with textarea that shows the contents. Finally we’ve added the record id as a hidden variable named ‘nid’ that will be submitted to indicate which record to update.

Be sure to replace ‘yourusername’, ‘yourpassword’ and ‘yourdatabasename’ with relevant credentials. Run this script in your browser and see your record getting updated every time you submit the form.

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar



Do NOT follow this link or you will be banned from the site!