updating a record

ianhaney · December 2, 2015

Hi

Sorry just need little bit of help, I think I know the issue but just need help on how to solve it

I have a form that does insert and update in one go and I am able to pull the data from the database all ok for the correct id for the record but when I change the amount earned amount, it is not updating within the database

I think it is because in my database table, I have the following columns

id, name, username, password, psalt, amount_earned

and I am only setting not all of all the columns if that makes sense as I don't want the password and psalt columns updating, I only want the name, username and amount_earned columns updating

Below is the code I have

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php
	/*
		Allows the user to both create new records and edit existing records
	*/

	// connect to the database
	include("connect-db.php");

	// creates the new/edit record form
 	// since this form is used multiple times in this file, I have made it a function that is easily reusable
	function renderForm($name = '', $username = '', $amount_earned = '', $error = '', $id = '')
	{ ?>
		<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
		<html>
			<head>	
				<title>
					<?php if ($id != '') { echo "Edit Affiliate"; } else { echo "New Affiliate"; } ?>
				</title>
				<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
                
                <link rel="stylesheet"href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.11.2/themes/blitzer/jquery-ui.css"/>
				<script src="//code.jquery.com/jquery-1.10.2.js"></script>
                <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
                
                <link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" />

<script src="js/jquery.ui.timepicker.js"></script>
<link rel="stylesheet" type="text/css" media="screen" href="css/jquery.ui.timepicker.css" />

<!--<script src="//cdn.ckeditor.com/4.5.5/full/ckeditor.js"></script>-->

			</head>
			<body>
            
            <div id="logo">
<img src="images/logo/it-done-right.jpg" alt="" title="">
</div>

<?
session_start();
if($_SESSION['user']==''){
 header("Location:../index.php");
}else{
 include("../config.php");
 $sql=$dbh->prepare("SELECT * FROM users WHERE id=?");
 $sql->execute(array($_SESSION['user']));
 while($r=$sql->fetch()){
  echo "<div class='home-content'>";
  echo "<center><h2>Hello, ".$r['username']."</h2>";
  echo "<a href='../logout.php'>Log Out</a>
  <br><br>
  <a href='../index.php'>Home</a></center>";
  echo "</div>";
  echo "<br>";
 }
}
?>

<?php include("nav-menu.php"); ?>
            
				<h1><?php if ($id != '') { echo "Edit Affiliate"; } else { echo "New Affiliate"; } ?></h1>
				<?php if ($error != '') {
					echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
						. "</div>";
				} ?>
				
				<form action="" method="post" class="basic-grey">
				<div>
					<?php if ($id != '') { ?>
						<input type="hidden" name="id" value="<?php echo $id; ?>" />
						<p>Affiliate ID: <?php echo $id; ?></p>
					<?php } ?>
                    
					<br>
					<strong>Customer Name:</strong> <input type="text" name="name"
						value="<?php echo $name; ?>"/>
                        <br/>
					<strong>Customer Email:</strong> <input type="text" name="username"
						value="<?php echo $username; ?>"/>
                        <br>
                        <strong>Amount Earned:</strong> <input type="text" name="amount_earned"
						value="<?php echo $amount_earned; ?>"/>        
                        <br>
					<input type="submit" name="submit" value="Add/Update Affiliate" />
				</div>
				</form>
			</body>
		</html>
		
	<?php }

        /*

           EDIT RECORD

        */
	// if the 'id' variable is set in the URL, we know that we need to edit a record
	if (isset($_GET['id']))
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			// make sure the 'id' in the URL is valid
			if (is_numeric($_POST['id']))
			{
				// get variables from the URL/form
				$id = $_POST['id'];
				$name = htmlentities($_POST['name'], ENT_QUOTES);
				$username = htmlentities($_POST['username'], ENT_QUOTES);
				$amount_earned = htmlentities($_POST['amount_earned'], ENT_QUOTES);
				
				// check that firstname and lastname are both not empty
				if ($name == '' || $username == '' || $amount_earned == '')
				{
					// if they are empty, show an error message and display the form
					$error = 'ERROR: Please fill in all required fields!';
renderForm($name, $username, $amount_earned, $error, $id);
				}
				else
				{
					// if everything is fine, update the record in the database
	if ($stmt = $mysqli->prepare("UPDATE affiliates SET name = ?, username = ?, amount_earned = ?,
						WHERE id=?"))
					{
	$stmt->bind_param("sssi", $name, $username, $amount_earned, $id);
						$stmt->execute();
						$stmt->close();
					}
					// show an error message if the query has an error
					else
					{
						echo "ERROR: could not prepare SQL statement.";
					}
					
					// redirect the user once the form is updated
					header("Location: view-affiliates.php");
				}
			}
			// if the 'id' variable is not valid, show an error message
			else
			{
				echo "Error!";
			}
		}
		// if the form hasn't been submitted yet, get the info from the database and show the form
		else
		{
			// make sure the 'id' value is valid
			if (is_numeric($_GET['id']) && $_GET['id'] > 0)
			{
				// get 'id' from URL
				$id = $_GET['id'];
				
				// get the recod from the database
				if($stmt = $mysqli->prepare("SELECT id, name, username, amount_earned FROM affiliates WHERE id=?"))
				{
					$stmt->bind_param("i", $id);
					$stmt->execute();
					
					$stmt->bind_result($id, $name, $username, $amount_earned);
					$stmt->fetch();
					
					// show the form
					renderForm($name, $username, $amount_earned, NULL, $id);
					
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "Error: could not prepare SQL statement";
				}
			}
			// if the 'id' value is not valid, redirect the user back to the view.php page
			else
			{
				header("Location: view-affiliates.php");
			}
		}
	}
	
        /*

           NEW RECORD

        */
		
	// if the 'id' variable is not set in the URL, we must be creating a new record
	else
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			// get the form data
			$name = htmlentities($_POST['name'], ENT_QUOTES);
			$username = htmlentities($_POST['username'], ENT_QUOTES);
			$amount_earned = htmlentities($_POST['amount_earned'], ENT_QUOTES);
			
			// check that firstname and lastname are both not empty
			if ($name == '' || $username == '' || $amount_earned == '')
			{
				// if they are empty, show an error message and display the form
				$error = 'ERROR: Please fill in all required fields!';
	renderForm($name, $username, $amount_earned, $error);
			}
			else
			{
				// insert the new record into the database

				if ($stmt = $mysqli->prepare("INSERT affiliates (name, username, amount_earned) VALUES (?, ?, ?)"))
				{
					$stmt->bind_param("sss", $name, $username, $amount_earned);
					$stmt->execute();
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "ERROR: Could not prepare SQL statement.";
				}
	
				// redirec the user
				header("Location: view-affiliates.php");
			}
			
		}
		
		// if the form hasn't been submitted yet, show the form
		else
		{
			renderForm();
		}
	}
	
	// close the mysqli connection
	$mysqli->close();
?>

I thought it would be possible to choose what columns to update?

Sorry

Thank you in advance

Ian

Barand · December 2, 2015

The logic flow seems all over the place. Try something like this

if ($_SERVER['REQUEST_METHOD'] == 'POST') {    // was data posted?
    if (empty($_POST['id'])) {
        // insert record
    }
    else {
        // update that record
    }
}
 
if (isset($_GET['id']) && !empty($_GET['id'])) {
    // get data from table and display in form for edit
}
else {
    // display blank form for new record
}

Edited December 2, 2015 by Barand

mac_gyver · December 2, 2015

similar to Barand's reply, you have both too much program logic and it's not organized. it's also doing things that don't make sense in the context where you are doing them (htmlentities() is an output function and should only be used on values being output to the browser, not on values that you are using in a database query.)

for your actual problem, you are dong a header redirect after you have output an error message for a failed prepare() statement (you have a sql syntax error in the update query.) you will never see that error message, because php/most of the all-in-one development systems have output_buffering turned on by default and any output your code produced is lost when the redirect occurs.

prepare() and execute()/query() errors are fatal problems for code that's database dependent. your code should have logic that handles all database errors. the database error information should be displayed when learning, developing, or debugging code, and it should be logged when your code is running on a live server. when there is a database error, your code should not continue running as though no error occurred.

it's easiest to handle fatal errors within your code by using exceptions. you won't have to put a conditional statement around each database function that can fail (you don't have any conditional test around your ->execute() statements anyways.) for code that will never be used on a live server, you can just enable exceptions for the database statements, without catching them or supplying your own exception handler, to get the error information to be output (uncaught exceptions are fatal php errors and php will stop program execution if one occurs..)

to enable exceptions for the mysqli_ statements, add the following two lines of code -

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ALL; // note: this will also throw exceptions for queries that don't use an index. if you only want excpetions for actual errors, use MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT

Edited December 2, 2015 by mac_gyver

Sign In

updating a record

Recommended Posts

ianhaney

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information