Wickham Posted September 5, 2012 Report Share Posted September 5, 2012 (edited) I've been setting up a booking system and I used Ben's topic "basic-php-system-view-edit-add-delete-records-with-mysqli/" http://www.killersites.com/community/index.php?/topic/3064-basic-php-system-view-edit-add-delete-records-with-mysqli/ I've tested with Wampserver and online with three fields (id plus two like Ben's tutorial) and it works, but if I add in an extra field to the database, view.php file and the edit file, I get these warnings "Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in /home/broadcha/public_html/edit-new-records.php on line 88 Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: (HY000/2031): No data supplied for parameters in prepared statement in /home/broadcha/public_html/edit-new-records.php on line 89 Warning: Cannot modify header information - headers already sent by (output started at /home/broadcha/public_html/edit-new-records.php:88) in /home/broadcha/public_html/edit-new-records.php on line 99" The edit file that works with id plus two fields is <?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($room = '', $who ='', $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 Record"; } else { echo "New Record"; } ?> </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <form action="" method="post"> <div> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID: <?php echo $id; ?></p> <?php } ?> <strong>Room: *</strong> <input type="text" name="room" value="<?php echo $room; ?>"/><br/> <strong>Name: *</strong> <input type="text" name="who" value="<?php echo $who; ?>"/> <p>* required</p> <input type="submit" name="submit" value="Submit" /> </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']; $room = htmlentities($_POST['room'], ENT_QUOTES); $who = htmlentities($_POST['who'], ENT_QUOTES); // check that firstname and lastname are both not empty if ($room == '' || $who == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($room, $who, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE villagehallbooking SET room = ?, who = ? WHERE id=?")) { $stmt->bind_param("ssi", $room, $who, $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: villagehall-with-form2admin.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 * FROM villagehallbooking WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($id, $room, $who ); $stmt->fetch(); // show the form renderForm($room, $who, 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: villagehall-with-form2admin.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 $room = htmlentities($_POST['room'], ENT_QUOTES); $who = htmlentities($_POST['who'], ENT_QUOTES); $who = htmlentities($_POST['who'], ENT_QUOTES); // check that room and club are both not empty if ($room == '' || $who == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($room, $who, $error); } else { // insert the new record into the database if ($stmt = $mysqli->prepare("INSERT villagehallbooking (room, who) VALUES (?, ?)")) { $stmt->bind_param("ss", $room, $who); $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: villagehall-with-form2admin.php"); } } // if the form hasn't been submitted yet, show the form else { renderForm(); } } // close the mysqli connection $mysqli->close(); ?> which is exactly like Ben's except for field names and table name changes but the file with an extra field that doesn't work is <?php /* Allows the user to both create new records and edit existing records */ error_reporting(E_ALL); // 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($room = '', $who ='', $book ='', $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 Record"; } else { echo "New Record"; } ?> </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <form action="" method="post"> <div> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID: <?php echo $id; ?></p> <?php } ?> <strong>Room: *</strong> <input type="text" name="room" value="<?php echo $room; ?>"/><br/> <strong>Name: *</strong> <input type="text" name="who" value="<?php echo $who; ?>"/><br/> <strong>Status: *</strong> <input type="text" name="book" value="<?php echo $book; ?>"/> <p>* required</p> <input type="submit" name="submit" value="Submit" /> </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']; $room = htmlentities($_POST['room'], ENT_QUOTES); $who = htmlentities($_POST['who'], ENT_QUOTES); $book = htmlentities($_POST['book'], ENT_QUOTES); // check that firstname and lastname are both not empty if ($room == '' || $who == '' || $book == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($room, $who, $book, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE villagehallbooking SET room = ?, who = ?, book = ? WHERE id=?")) { $stmt->bind_param("ssi", $room, $who, $book, $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: villagehall-with-form2admin.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 * FROM villagehallbooking WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($id, $room, $who, $book ); $stmt->fetch(); // show the form renderForm($room, $who, $book, 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: villagehall-with-form2admin.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 $room = htmlentities($_POST['room'], ENT_QUOTES); $who = htmlentities($_POST['who'], ENT_QUOTES); $book = htmlentities($_POST['book'], ENT_QUOTES); // check that room and club are both not empty if ($room == '' || $who == '' || $book == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($room, $who, $book, $error); } else { // insert the new record into the database if ($stmt = $mysqli->prepare("INSERT villagehallbooking (room, who, book) VALUES (?, ?)")) { $stmt->bind_param("ss", $room, $who, $book); $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: villagehall-with-form2admin.php"); } } // if the form hasn't been submitted yet, show the form else { renderForm(); } } // close the mysqli connection $mysqli->close(); ?> which seems to stall at $stmt->bind_param("ssi", $room, $who, $book, $id); or just before. It processes the error message if I leave a field blank in the edit box but fails just after that. All I've done is add a field to the database, edited the view file for the extra column (shows the column OK) and added the extra variable into the Edit file. The Edit page shows the data ready to edit, but fails to make the change. Refreshing the database and view file make no difference (they update automatically with the file that works). The New entry box fails the same way. My wampserver just goes staight to the Location page (which shows no change) without showing errors but online I get the errors above. The last warning re headers should go away if the first two can be cured. Any ideas? Edited September 5, 2012 by Wickham Quote Link to comment Share on other sites More sharing options...
falkencreative Posted September 5, 2012 Report Share Posted September 5, 2012 The issue is this line: $stmt->bind_param("ssi", $room, $who, $book, $id); The first string within that function "ssi" contains the types of the various variables you are inserting into the query. "s" is a string, "i" is an integer, etc. Since MySQLi needs to know what data is being passed in, the number of items in the first string needs to match the total number of variables that follow it. I believe that adding an extra "s" will fix it, since then you'll have "sssi" and that will match the 4 variables that you are trying to insert. So, I believe the correct code is: $stmt->bind_param("sssi", $room, $who, $book, $id); Quote Link to comment Share on other sites More sharing options...
Wickham Posted September 5, 2012 Author Report Share Posted September 5, 2012 (edited) Thanks, that's something I would never have guessed. Editing for all three fields works now, but the New Entry page doesn't add a new id and data. I changed $stmt->bind_param("ss", $room, $who, $book); by adding an extra s but it doesn't work. When everything is set up for id plus 2 fields like your tutorial, the New Record page does create a new record, but adding the extra s in the above code didn't work for me for id + 3 fields. EDIT Got it. I had to add an extra ,? in if ($stmt = $mysqli->prepare("INSERT villagehallbooking2 (room, who, book) VALUES (?, ?, ?)")) Now to add the other 12 fields (lots of s and ,? needed). I had set up a booking form in 2007 with MySQL but it wasn't used because I had to enter the database directly to edit and delete, but now with the edit and delete via a web page someone else can do it easily. Edited September 5, 2012 by Wickham Quote Link to comment Share on other sites More sharing options...
falkencreative Posted September 5, 2012 Report Share Posted September 5, 2012 Glad to hear you got it fixed, and are finding that script useful. 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.
Note: Your post will require moderator approval before it will be visible.