ianhaney Posted December 2, 2015 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted December 2, 2015 Share Posted December 2, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted December 2, 2015 Share Posted December 2, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.