Jump to content

Edit database doesn't work with four fields


Wickham

Recommended Posts

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 by Wickham
Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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 by Wickham
Link to comment
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...