Jump to content

Basic PHP System: View, Edit, Add, Delete records with MySQLi


falkencreative

Recommended Posts

Hi Ben,

 

Thanks for getting back to me on this.

 

As requested here is my "view.php":

I have re-checked it against your original & it seems OK....but then again, what do I know.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head> 
<title>View Records</title>
</head>
<body>
<?php
/*         VIEW.PHP  
   Displays all data from the customer table      
*/ 

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

// get results from database        
$result = mysql_query("SELECT * FROM customer")                 
or die(mysql_error());  

// display data in table        
echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
echo "<table border='1' cellpadding='10'>"; 
echo "<tr> <th>Customer No</th> <th> Name</th> <th>Address</th> <th>Phone no</th><th></th> <th></th></tr>";  

// loop through results of database query, displaying them in the table        
while($row = mysql_fetch_array( $result )) 
{                                
// echo out the contents of each row into a table   

echo "<tr>";                
echo '<td>' . $row['cust_no'] . '</td>'; 
echo '<td>' . $row['name'] . '</td>';                
echo '<td>' . $row['address'] . '</td>'; 
echo '<td>' . $row['phone_no'] . '</td>';
echo '<td><a href="edit.php?cust_no=' . $row['cust_no']. '">Edit</a></td>';
echo '<td><a href="delete.php?cust_no=' . $row['cust_no'] . '">Delete</a></td>'; 
echo "</tr>";         
}         
// close table>        
echo "</table>";
?>
<p><a href="new.php">Add a new record</a></p>
</body>
</html> 

 

On the "edit.php" question.

I went over it again & did find that stupid mistake that you showed above regarding the "//"

Its working great now, thanks.

I have left in the last "}" at the end as if I took it out it left an eneven number throughout the document.

They are supposed to be even pairs ..aren't they?

 

Regards,

TomTom.

Link to comment
Share on other sites

I have left in the last "}" at the end as if I took it out it left an eneven number throughout the document.

They are supposed to be even pairs ..aren't they?

When I formatted the file to show the hierarchy of the code, I thought I noticed an extra "}". I'm guessing I was incorrect though.

 

I took another look at your delete page, and I think it may be deleting the record correctly, but simply not redirecting you back. Note this line:

 

// redirect back to the view page header("Location: view.php"); 

should be two lines:

 

// redirect back to the view page 
header("Location: view.php"); 

Link to comment
Share on other sites

Hi Ben,

 

Yes, again, a foolish mistake.

 

I also changed the "id" to "cust_no" in the following:

$result = mysql_query("DELETE FROM customer WHERE id=$cust_no") 

 

It is working now.Thanks.

Much appreciated.

 

I have also been trying to put together a "select" query for my database where the user can decide on the criteria that they want to search the table by.

The table has shop_no,shop_ address, contact_no, contact_name.

Would check boxes be the best way to go ?

<form action="select.php" method="get"> 
Shop Address<input name="1" type="checkbox" value="shop_address" /> 
Contact No<input name="1" type="checkbox" value="contact_no" /> 
Contact name<input name="1" type="checkbox" value="weight"/> 
<input name="2" type="text" /> 
<input type="submit" value="Submit" /> 
</form> 

 

Could this be used to tick one box & then enter text to narrow the query further?

Not sure where to go from there.

Link to comment
Share on other sites

Ideally, i am trying to figure out a way for the user to pick one of, say 3, options:

Search database table by:

1)shop address.

2)contact no

3)contact name

and based on that have the information delivered in a table.

 

<form action="select.php" method="get"> 
Shop Address<input name="1" type="checkbox" value="shop_address" /> 
Contact No<input name="1" type="checkbox" value="contact_no" /> 
Contact name<input name="1" type="checkbox" value="contact_name"/> 
<input name="2" type="text" /> 
<input type="submit" value="Submit" /> </form> 

 

Thought that checkbox's were good that the user could tick the desired checkbox (address) & then enter in the text area (location from address in the table)

& get the details echoed out from that.

Of course putting the php together for that is way beyond me at this stage.

Or is there an easier way that you know of to basically let the user choose the criteria & then search based on that criteria?

Link to comment
Share on other sites

  • 2 weeks later...

Hi Ben , thanks for your code , it's really useful.

I have a problem with the file edit.php

 

This is the error:

Parse error: syntax error, unexpected $end in /opt/lampp/htdocs/server/GestionIp/edit.php on line 116

 

this is the code :

 

<?php
/*  EDIT.PHP 
Allows user to edit specific entry in database
*/ 

// creates the 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($ID, $IP, $NOMBRE, $DESC, $error) 
{ 
?> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> 
<html> 
<head> 
<title>Edit Record</title> 
</head> 
<body> 
<?php  
// if there are any errors, display them 
if ($error != '') 
{ 
echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>'; 
} 
?>   

<form action="" method="post"> 
<input type="hidden" name="id" value="<?php echo $IP; ?>"/> 
<div>
<p><strong>ID:</strong> <?php echo $ID; ?></p>
<p><strong>IP:</strong> <?php echo $IP; ?></p>
<strong>Nombre: </strong> <input type="text" name="NOMBRE" value="<?php echo $NOMBRE; ?>"/><br/>
<strong>DESC: </strong> <input type="text" name="DESC" value="<?php echo $DESC; ?>"/><br/>

<input type="submit" name="submit" value="Submit">
</div> 
</form>  
</body> 
</html>  
<?php 
}

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


// check if the form has been submitted. If it has, process the form and save it to the database 
if (isset($_POST['submit'])) 
{ 

// confirm that the 'id' value is a valid integer before getting the form data 
if (is_numeric($_POST['ID'])) 
{

// get form data, making sure it is valid 
$ID = $_POST['ID']; 
$NOMBRE = mysql_real_escape_string(htmlspecialchars($_POST['NOMBRE'])); 
$DESC = mysql_real_escape_string(htmlspecialchars($_POST['DESC']));

// check that NOMBRE/DESC fields are both filled in 
if ($NOMBRE == '' || $DESC == '') 
{

// generate error message 
$error = 'ERROR: Please fill in all required fields!';  

//error, display form 
renderForm($IP, $ID, $NOMBRE, $DESC, $error); 
} 
else 
{

// save the data to the database 
mysql_query("UPDATE players SET NOMBRE='$NOMBRE', DESC='$DESC' WHERE IP='$ID'") 
or die(mysql_error()); 

// once saved, redirect back to the view page header("Location: enviar.php");  
} 
} 
else

// if the form hasn't been submitted, get the data from the db and display the form 
{

// get the 'ID' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0) 
if (isset($_GET['$ID']) && is_numeric($_GET['$ID']) && $_GET['$ID'] > 0) 
{
// query db 
$ID = $_GET['$ID']; 
$result = mysql_query("SELECT * FROM players WHERE $ID=$ID") 
or die(mysql_error());  
$row = mysql_fetch_array($result);

// check that the 'IP' matches up with a row in the databse 
if($row) 
{

// get data from db 
$NOMBRE = $row['NOMBRE']; 
$DESC = $row['DESC'];

// show form 
renderForm($ID, $IP, $NOMBRE, $DESC, ''); 
} 
else 
// if no match, display result 
{ echo "No results!";

} 
} 
else 
// if the 'IP' in the URL isn't valid, or if there is no 'IP' value, display an error 
{ 
echo 'Error!'; 
} 
}
?>

 

I don't know what happen because I copy your code and replaced the specific code for my configuration.

Thanks for all

Bye

 

PD: One question , What does it do the function renderform()?;

Link to comment
Share on other sites

  • 6 months later...

Thank you so much for the code. I'm trying to use 2 different tables. One for Males and the other one for Females. They both contain the same information (ID, gender, name, last name). How do I modify the code so that the output will have all the Males in one table and the Females on another table? Sorry, I'm a php noob. Any help will be greatly appreciated.

Link to comment
Share on other sites

  • 2 months later...
  • 4 weeks later...

Hi Ben,

 

I have been looking for a practical mysqli tutorial all over the internet I couldn't find one like yours.

 

I am so glad to have found your tutorial here.

 

Please, can you find time to also code a sample to query from a html form with multiple inputs?

 

Stay blessed.

 

joseph

Link to comment
Share on other sites

Hi Ben,

 

I am trying to output an image stored from a directory (it's name save in mysql database) as follows:

 

<img src=\"./images/$row[15]\" width=\"90\" height=\"120\" alt=\"\" />

 

But I am only getting \"\" printed instead of an image itself.

 

Please, help.

 

joseph

Link to comment
Share on other sites

  • 3 months later...
  • 1 month later...
  • 1 month later...

Hi Ben,

 

Thanks this was great. I learned a lot.

 

I was wonder how to set it up on the new.php so that you have a choice.

1. Submit and go back to view.php

2. Submit and go to new.php to add another record.

 

Thanks I really do appreciated the time and effort that was put into this.

Link to comment
Share on other sites

@JeffH13 - Basically, you'd need to add two radio buttons or a select element at the end of the form, giving you the option of either choice. Then, in your PHP code, when the form submits and get processed, you'd check the value of the option and perform the correct action based on the value. Should be relatively straight-forward.

Link to comment
Share on other sites

I agree is sounds easy I just learning the code and how it is written compared how I think.

 

The Submit area:

 <table wi:th="450" border="0" cellspacing="0" cellpadding="10">
  <tr>
    <td align="center"> <input type="submit" name="submit" value="Add-Another"></td>
    <td> </td>
    <td align="center"> <input type="submit" name="submit" value="Submit"></td>
  </tr>
</table>

 

The Post and redirect area:

 if (isset($_POST['submit']))
{ 
// get form data, making sure it is valid
$rlyear = mysql_real_escape_string(htmlspecialchars($_POST['rlyear']));
$rlplayername = mysql_real_escape_string(htmlspecialchars($_POST['rlplayername']));
$rlcarries = mysql_real_escape_string(htmlspecialchars($_POST['rlcarries']));
$rlyards = mysql_real_escape_string(htmlspecialchars($_POST['rlyards']));
$rlavg = mysql_real_escape_string(htmlspecialchars($_POST['rlavg']));
$rltd = mysql_real_escape_string(htmlspecialchars($_POST['rltd']));

// check to make sure all fields are entered
if ($rlyear == '' || $rlplayername == '' || $rlcarries == '' || $rlyards == '' || $rlavg == '' || $rltd == '')
{
// generate error message
$error = 'ERROR: Please fill in all required fields!';

// if either field is blank, display the form again
renderForm($rlyear, $rlplayername, $rlcarries, $rlyards, $rlavg, $rltd, $error);
}
else
{
// save the data to the database
mysql_query("INSERT rushingleaders SET rlyear='$rlyear', rlplayername='$rlplayername', rlcarries='$rlcarries', rlyards='$rlyards', rlavg='$rlavg', rltd='$rltd'")
or die(mysql_error()); 

// once saved, redirect back to the view page
header("Location: rushingleaders-view-paginated.php"); 
}
}
else

 

I figure that I need to write an if else statement here:

// once saved, redirect back to the view page
header("Location: rushingleaders-view-paginated.php"); 

 

Not really sure how?

 

Thanks.

Link to comment
Share on other sites

  • 1 month later...

Thanks a lot for for the tutorial... I am new to MySQLi and im trying to use this script... my problem is that it redirects to the main page again.. meaning the info has been added I take it but it doesn't actually add any of the details, I have checked phpMyAdmin and it doesn't get added...I was wondering if you could help me with this, I have the following:

 

<?php

/*

Allows the user to both create new records and edit existing records

*/

 

// connect to the database

include("includes/connecti.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($title = '', $poster ='', $date = '', $story = '', $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>Title: *</strong> <input type="text" name="title" value="<?php echo $title; ?>"/><br/>

<strong>Poster: *</strong> <input type="text" name="poster" value="<?php echo $poster; ?>"/>

<strong>Date: *</strong> <input type="text" name="date" value="<?php echo $date; ?>"/>

<strong>Story: *</strong> <input type="text" name="story" value="<?php echo $story; ?>"/>

<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'];

$title = htmlentities($_POST['title'], ENT_QUOTES);

$poster = htmlentities($_POST['poster'], ENT_QUOTES);

$date = htmlentities($_POST['date'], ENT_QUOTES);

$story = htmlentities($_POST['story'], ENT_QUOTES);

 

// check that firstname and lastname are both not empty

if ($title == '' || $poster == '' || $date == '' || $story == '')

{

// if they are empty, show an error message and display the form

$error = 'ERROR: Please fill in all required fields!';

renderForm($title, $poster, $date, $story, $error, $id);

}

else

{

// if everything is fine, update the record in the database

if ($stmt = $mysqli->prepare("UPDATE news SET title = ?, poster = ?, date = ?, story = ?

WHERE id=?"))

{

$stmt->bind_param("ssi", $title, $poster, $date, $story, $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.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 news WHERE id=?"))

{

$stmt->bind_param("i", $id);

$stmt->execute();

 

$stmt->bind_result($id, $title, $poster, $date, $story);

$stmt->fetch();

 

// show the form

renderForm($title, $poster, $date, $story, 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.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

$title = htmlentities($_POST['title'], ENT_QUOTES);

$poster = htmlentities($_POST['poster'], ENT_QUOTES);

$date = htmlentities($_POST['date'], ENT_QUOTES);

$story = htmlentities($_POST['story'], ENT_QUOTES);

 

// check that firstname and lastname are both not empty

if ($title == '' || $poster == '' || $date == '' || $story == '')

{

// if they are empty, show an error message and display the form

$error = 'ERROR: Please fill in all required fields!';

renderForm($title, $poster, $date, $story, $error);

}

else

{

// insert the new record into the database

if ($stmt = $mysqli->prepare("INSERT news (title, poster, date, story) VALUES (?, ?, ?, ?)"))

{

$stmt->bind_param("ss", $title, $poster, $date, $story);

$stmt->execute();

$stmt->close();

}

// show an error if the query has an error

else

{

echo "ERROR: Could not prepare SQL statement.";

}

 

// redirect the user

header("Location: view.php");

}

 

}

// if the form hasn't been submitted yet, show the form

else

{

renderForm();

}

}

 

// close the mysqli connection

$mysqli->close();

?>

Link to comment
Share on other sites

  • 2 weeks later...

Hi,

 

I am using your code on my site but I have different column names...

 

I changed these thinking I could find a replace the names in the records.php but when i go to the records.php my value names do not echo the data... why is this? I cannot get my head road why the values do not work...

 

I need to add another 10 columns to the records.php so what would I need to amend on the records sheet for this to work?

 

You help would be much appreciated.

 

Regards

Benny

				<?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($Site = '', $bloc ='', $error = '', $id = '')
					{ ?>
									<body>

			<div class="container theme-showcase">
											<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="text" name="id" value="<?php echo $id; ?>" />
															<p>ID: <?php echo $id; ?></p>
													<?php } ?>

													<strong>Site Ref: *</strong> <input type="text" name="SiteRef"
															value="<?php echo $Site; ?>"/>
													<strong>Block: *</strong> <input type="text" name="block"
															value="<?php echo $bloc; ?>"/>
													<p>* required</p>
													<input type="submit" name="submit" value="Submit" />
											</div>
											</form>
			</div>
			<?php include"footer.php"?>

					<?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'];
											$SiteRef = htmlentities($_POST['SiteRef'], ENT_QUOTES);
											$block = htmlentities($_POST['block'], ENT_QUOTES);

											// check that SiteRef and block are both not empty
											if ($SiteRef == '' || $block == '')
											{
													// if they are empty, show an error message and display the form
													$error = 'ERROR: Please fill in all required fields!';
													renderForm($SiteRef, $block, $error, $id);
											}
											else
											{
													// if everything is fine, update the record in the database
													if ($stmt = $mysqli->prepare("UPDATE FireRiskAssessment SET SiteRef = ?, block = ?
															WHERE id=?"))
													{
															$stmt->bind_param("ssi", $SiteRef, $block, $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.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 record from the database
											if($stmt = $mysqli->prepare("SELECT * FROM FireRiskAssessment WHERE id=?"))
											{
													$stmt->bind_param("i", $id, $SiteRef, $block);
													$stmt->execute();

													$stmt->bind_result($id, $SiteRef, $block);
													$stmt->fetch();

													// show the form
													renderForm($SiteRef, $block, 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.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
									$SiteRef = htmlentities($_POST['SiteRef'], ENT_QUOTES);
									$block = htmlentities($_POST['block'], ENT_QUOTES);

									// check that SiteRef and block are both not empty
									if ($SiteRef == '' || $block == '')
									{
											// if they are empty, show an error message and display the form
											$error = 'ERROR: Please fill in all required fields!';
											renderForm($SiteRef, $block, $error);
									}
									else
									{
											// insert the new record into the database
											if ($stmt = $mysqli->prepare("INSERT FireRiskAssessment (SiteRef, block) VALUES (?, ?)"))
											{
													$stmt->bind_param("ss", $SiteRef, $block);
													$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.php");
									}

							}
							// if the form hasn't been submitted yet, show the form
							else
							{
									renderForm();
							}
					}

					// close the mysqli connection
					$mysqli->close();
			?>

Link to comment
Share on other sites

  • 3 months later...

Ben thanks for this fine code!

After changing the code, mathching my table for our choir, and adding fields i got the following messages:

 

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in /xxxxx/records.php on line 205

 

Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: (HY000/2031): No data supplied for parameters in prepared statement in /home/xxxxxxxxx/records.php on line 206

 

Warning: Cannot modify header information - headers already sent by (output started at /home/xxxxxxx/records.php:205) in /home/xxxxxxx/records.php on line 216

 

I searched and searched but i cant't find the fault.

Could you help me out?

 

greetings Jos

 

============code koorrecord.php also attached as pagina.php==================

<?php

/*

Allows the user to both create new records and edit existing records

*/

 

// connect to the database

include("koorconnect-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($naam = '', $mp3naam ='', $links ='', $linka ='', $linkt='', $linkb='', $yt1='', $yt2='', $bladm='', $opm='', $error = '', $id = '')

{

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>

<link rel='stylesheet' href='style2.css' type='text/css'>

<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>Naam: *</strong> <input type="text" name="naam"

value="<?php echo $naam; ?>"/><br/>

<strong>Mp3 naam: *</strong> <input type="text" name="mp3naam"

value="<?php echo $mp3naam; ?>"/>

<strong>Sopraan: *</strong> <input type="text" name="links"

value="<?php echo $links; ?>"/>

<strong>Alt: *</strong> <input type="text" name="linka"

value="<?php echo $linka; ?>"/>

<strong>Tenor: *</strong> <input type="text" name="linkt"

value="<?php echo $linkt; ?>"/>

<strong>Bas: *</strong> <input type="text" name="linkb"

value="<?php echo $linkb; ?>"/>

<strong>Youtube 1: *</strong> <input type="text" name="yt1"

value="<?php echo $yt1; ?>"/>

<strong>Youtube 2: *</strong> <input type="text" name="yt2"

value="<?php echo $yt2; ?>"/>

<strong>Bladm: *</strong> <input type="text" name="bladm"

value="<?php echo $bladm; ?>"/>

<strong>Opmerking: *</strong> <input type="text" name="opm"

value="<?php echo $opm; ?>"/>

<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'];

$naam = htmlentities($_POST['naam'], ENT_QUOTES);

$mp3naam = htmlentities($_POST['mp3naam'], ENT_QUOTES);

$links = htmlentities($_POST['links'], ENT_QUOTES);

$linka = htmlentities($_POST['linka'], ENT_QUOTES);

$linkt = htmlentities($_POST['linkt'], ENT_QUOTES);

$linkb = htmlentities($_POST['linkb'], ENT_QUOTES);

$yt1 = htmlentities($_POST['yt1'], ENT_QUOTES);

$yt2 = htmlentities($_POST['yt2'], ENT_QUOTES);

$bladm = htmlentities($_POST['bladm'], ENT_QUOTES);

$opm= htmlentities($_POST['opm'], ENT_QUOTES);

 

 

// check that firstname and lastname are both not empty

if ($naam == '' || $mp3naam == '')

{

// if they are empty, show an error message and display the form

$error = 'ERROR: Please fill in all required fields!';

renderForm($naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm , $error, $id);

}

else

{

// if everything is fine, update the record in the database

if ($stmt = $mysqli->prepare("UPDATE players SET naam = ?, mp3naam = ?, links = ?, linka = ?, linkt = ?, linkb = ?, yt1 = ?, yt2 = ?, bladm = ?, opm = ?

WHERE id=?"))

{

$stmt->bind_param("ssi", $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm, $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: xxxxx/koor");

}

}

// 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 nummers WHERE id=?"))

{

$stmt->bind_param("i", $id);

$stmt->execute();

 

/*$stmt->bind_result($id, $naam, $mp3naam);*/

$stmt->bind_result($id, $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm);

$stmt->fetch();

 

// show the form

renderForm($naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm, 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.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

$naam = htmlentities($_POST['naam'], ENT_QUOTES);

$mp3naam = htmlentities($_POST['mp3naam'], ENT_QUOTES);

$links = htmlentities($_POST['links'], ENT_QUOTES);

$linka = htmlentities($_POST['linka'], ENT_QUOTES);

$linkt = htmlentities($_POST['linkt'], ENT_QUOTES);

$linkb = htmlentities($_POST['linkb'], ENT_QUOTES);

$yt1 = htmlentities($_POST['yt1'], ENT_QUOTES);

$yt2 = htmlentities($_POST['yt2'], ENT_QUOTES);

$bladm = htmlentities($_POST['bladm'], ENT_QUOTES);

$opm= htmlentities($_POST['opm'], ENT_QUOTES);

 

// check that firstname and lastname are both not empty

if ($naam == '' || $mp3naam == '')

{

// if they are empty, show an error message and display the form

$error = 'ERROR: Vul de verplichte velden in!';

renderForm($naam, $mp3naam, $error);

}

else

{

// insert the new record into the database

if ($stmt = $mysqli->prepare("INSERT nummers (naam, mp3naam, links, linka, linkt, linkb, yt1, yt2, bladm, opm) VALUES (?, ?, ?,?,?,?,?,?,?,? )"))

{

$stmt->bind_param("ss", $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm);

$stmt->execute();

$stmt->close();

}

// show an error if the query has an error

else

{

echo "ERROR: Kan geen SQL statement maken.";

}

 

// redirec the user

header("Location:xxxxx/koor");

}

 

}

// if the form hasn't been submitted yet, show the form

else

{

renderForm();

}

}

 

// close the mysqli connection

$mysqli->close();

?>

pagina.php

Link to comment
Share on other sites

The error is exactly what PHP describes: "Number of elements in type definition string doesn't match number of bind variables in /xxxxx/records.php on line 205"

 

I believe (I haven't checked the line numbers, that this line is the issue:

 

$stmt->bind_param("ssi", $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm, $id);

Note the first parameter, "ssi"? The number of characters needs to match up with the number of perimeters after it. "s" is a string, "i" is an int, etc. So I believe it should be:

 

$stmt->bind_param("ssssssssssi", $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm, $id);

Make sure to fix any other bind_param() lines to match up the number/type of parameters as well.

Link to comment
Share on other sites

The error is exactly what PHP describes: "Number of elements in type definition string doesn't match number of bind variables in /xxxxx/records.php on line 205"

 

I believe (I haven't checked the line numbers, that this line is the issue:

 

$stmt->bind_param("ssi", $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm, $id);

Note the first parameter, "ssi"? The number of characters needs to match up with the number of perimeters after it. "s" is a string, "i" is an int, etc. So I believe it should be:

 

$stmt->bind_param("ssssssssssi", $naam, $mp3naam, $links, $linka, $linkt, $linkb, $yt1, $yt2, $bladm, $opm, $id);

Make sure to fix any other bind_param() lines to match up the number/type of parameters as well.

Link to comment
Share on other sites

  • 1 month later...

Hi.

First off, great site and tutorials!

 

Now, I have copied your tutorial and pasted in etc...

BUT,

 

I am now getting an error on records.php

error is:

 

"Warning: Cannot modify header information - headers already sent by (output started at C:\......\records.php:2) in C:\.......\records.php on line 183"

 

I have tries reading up about it and tried different suggestions, how ever as this error then dissapears plenty others arise. Any help or suggestions would be greatly appreciated.

Link to comment
Share on other sites

Hi.

First off, great site and tutorials!

 

Now, I have copied your tutorial and pasted in etc...

BUT,

 

I am now getting an error on records.php

error is:

 

"Warning: Cannot modify header information - headers already sent by (output started at C:\......\records.php:2) in C:\.......\records.php on line 183"

 

I have tries reading up about it and tried different suggestions, how ever as this error then dissapears plenty others arise. Any help or suggestions would be greatly appreciated.

A "cannot modify header information" error means that you are sending data to the browser before trying to redirect using header(). I'm not sure exactly what your code looks like, but make sure you aren't echoing anything out before calling header(), and that there aren't any stray spaces at the start of the file before the opening PHP tag.

Link to comment
Share on other sites

  • 3 months later...

Hello, thanks for the tutorial! For the most part, it works, but I've been having one issue with it: when I try to edit a record, I end up adding an entirely new one instead. I'm not entirely sure what's causing this error to happen, I've spent days trying to figure it out (including multiple debugging programs) and I've come up with nothing. Could you take a quick look at my code? I've attached the files to this post.

 

settingupsoloAdmin.zip

 

 

Thank you for your help!

Link to comment
Share on other sites

I don't have the database, so this is a little hard to test. What happens when you click the Edit button currently? Are you initially seeing the data, and you can edit it, and it's only after you submit that it enters it as a new record? 

 

One potential issue: it seems like in your PS_Manage_Appts.php file, you are creating the edit/delete buttons with "?id=" in the URL, but the code in the update_post.php file makes reference to a $_GET['groomingid']. I can't really tell if that is the issue, but your update_post file never accesses $_GET['id'] at all. If you are going to pass variables via the URL, you need to make sure you are both setting and getting the same variable name.

Link to comment
Share on other sites

  • 1 month later...

I have copy & pasted all the code as is and it is working fine using the records DB and players table exactly as illustrated. I have tried to change the data to match a different DB & table and now on the edit.php page I am simply getting "Error! Is there a way I can debug where this error is coming from? The view.php page is working OK with my DB information. I am a newbie when it comes to PHP & MySql so many thanks in advance for any help you can offer.

 

Here is the edit.php code I have:

 

<?php
/* 
 EDIT.PHP
 Allows user to edit specific entry in database
*/

 // creates the 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($agency_num, $agency_name, $agency_contact, $error)
 {
 ?>
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
 <html>
 <head>
 <title>Edit Agency Information</title>
 </head>
 <body>
 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?> 
 
 <form action="" method="post">
 <input type="hidden" name="agency_num" value="<?php echo $agency_num; ?>"/>
 <div>
 <p><strong>Agency Num:</strong> <?php echo $agency_num; ?></p>
 <strong>Agency Name: *</strong> <input type="text" name="agency_name" value="<?php echo $agency_name; ?>"/><br/>
 <strong>Contact Name: *</strong> <input type="text" name="agency_contact" value="<?php echo $agency_contact; ?>"/><br/>
 <p>* Required</p>
 <input type="submit" name="submit" value="Submit">
 </div>
 </form> 
 </body>
 </html> 
 <?php
 }



 // connect to the database
 include('connect-db.php');
 
 // check if the form has been submitted. If it has, process the form and save it to the database
 if (isset($_POST['submit']))
 { 
 // confirm that the 'id' value is a valid integer before getting the form data
 if (is_numeric($_POST['agency_num']))
 {
 // get form data, making sure it is valid
 $agency_num = $_POST['agency_num'];
 $agency_name = mysql_real_escape_string(htmlspecialchars($_POST['agency_name']));
 $agency_contact = mysql_real_escape_string(htmlspecialchars($_POST['agency_contact']));
 
 // check that firstname/lastname fields are both filled in
 if ($agency_name == '' || $agency_contact == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';
 
 //error, display form
 renderForm($agency_num, $agency_name, $agency_contact, $error);
 }
 else
 {
 // save the data to the database
 mysql_query("UPDATE contact_data SET agency_name='$agency_name', agency_contact='$agency_contact' WHERE agency_num='$agency_num'")
 or die(mysql_error()); 
 
 // once saved, redirect back to the view page
 header("Location: view1.php"); 
 }
 }
 else
 {
 // if the 'id' isn't valid, display an error
 echo 'ID Error!';
 }
 }
 else
 // if the form hasn't been submitted, get the data from the db and display the form
 {
 
 // get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['agency_num']) && is_numeric($_GET['agency_num']) && $_GET['agency_num'] > 0)
 {
 // query db
 $id = $_GET['agency_num'];
 $result = mysql_query("SELECT * FROM contact_data WHERE agency_num=$id")
 or die(mysql_error()); 
 $row = mysql_fetch_array($result);
 
 // check that the 'id' matches up with a row in the databse
 if($row)
 {
 
 // get data from db
 $agency_name = $row['agency_name'];
 $agency_contact = $row['agency_contact'];
 
 // show form
 renderForm($agency_num, $agency_name, $agency_contact, '');
 }
 else
 // if no match, display result
 {
 echo "No results!";
 }
 }
 else
 // if the 'id' in the URL isn't valid, or if there is no 'id' value, display an error
 {
 echo 'Error!';
 }
 }
?>
Link to comment
Share on other sites

  • 1 month later...

Hi, I've modified your script in order to fit the requirements of my database but I am now getting the following errors:

 

 

 

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\DataEditingTables\billing_info_editing_newANDedit.php on line 124

 

 

 

Warning: Cannot modify header information - headers already sent by (output started at C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\DataEditingTables\billing_info_editing_newANDedit.php:124) in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\DataEditingTables\billing_info_editing_newANDedit.php on line 135

 

Here is the code for my php editing file:

/*

           EDIT RECORD

        */
	// if the 'id' variable is set in the URL, we know that we need to edit a record
	if (isset($_GET['account_code']))
	{
		// 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_string($_POST['account_code']))
			{
				// get variables from the URL/form
				$account_code = htmlentities($_POST['account_code'], ENT_QUOTES);
				$forename = htmlentities($_POST['forename'], ENT_QUOTES);
				$surname = htmlentities($_POST['surname'], ENT_QUOTES);
				$address_1 = htmlentities($_POST['address_1'], ENT_QUOTES); 
				$address_2 = htmlentities($_POST['address_2'], ENT_QUOTES); 
				$town_city = htmlentities($_POST['town_city'], ENT_QUOTES); 
				$county = htmlentities($_POST['county'], ENT_QUOTES); 
				$postcode = htmlentities($_POST['postcode'], ENT_QUOTES); 
				$company_name = htmlentities($_POST['company_name'], ENT_QUOTES);
				$email = htmlentities($_POST['email'], ENT_QUOTES);
				$telephone = htmlentities($_POST['telephone'], ENT_QUOTES); 
				$mobile = htmlentities($_POST['mobile'], ENT_QUOTES);
				
				// check that firstname and lastname are both not empty
				if ($forename == '' || $surname == '')
				{
					// if they are empty, show an error message and display the form
					$error = 'ERROR: Please fill in all required fields!';
					renderForm($account_code, $forename, $surname, $address_1, $address_2, $town_city, $county, $postcode, $company_name, $email, $telephone, $mobile, $error);
				}
				else
				{
					
					/*$result = mysqli_query($cxn,"UPDATE tbl_billing_info SET account_code='$account_code',forename'$forename',surname='$surname',address_1='$address_1',address_2='$address_2',town_city='$town_city',county='$county',postcode='$postcode',company_name='$company_name',email='$email',telephone='$telephone',mobile='$mobile' WHERE account_code='$account_code'"); or die ("Could not connect to the database server<br />\n" . mysqli_connect_error());*/					
					
					// if everything is fine, update the record in the database
					if ($stmt = $cxn->prepare("UPDATE tbl_billing_info SET account_code = ?, forename = ?, surname = ?, address_1 = ?, address_2 = ?, town_city = ?, county = ?, postcode = ?, company_name = ?, email = ?, telephone = ?, mobile = ? WHERE account_code= ?"))
					{
						$stmt->bind_param("s,s,s,s,s,s,s,s,s,s,s,s", $account_code, $forename, $surname, $address_1, $address_2, $town_city, $county, $postcode, $company_name, $email, $telephone, $mobile);
						$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: billing_info_editing_newANDedit.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_string($_GET['account_code']) && $_GET['account_code'] != NULL)
			{
				// get 'id' from URL
				$account_code = $_GET['account_code'];
				
				// get the record from the database
				if($stmt = $cxn->prepare("SELECT account_code,forename,surname, address_1, address_2, town_city, county, postcode, company_name, email, telephone, mobile FROM tbl_billing_info WHERE account_code= ?"))
				{
					$stmt->bind_param("s", $account_code);
					$stmt->execute();
					
					$stmt->bind_result($account_code, $forename, $surname, $address_1, $address_2, $town_city, $county, $postcode, $company_name, $email, $telephone, $mobile);
					$stmt->fetch();
					
					// show the form
					renderForm($account_code, $forename, $surname, $address_1, $address_2, $town_city, $county, $postcode, $company_name, $email, $telephone, $mobile, NULL);
					
					$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: billing_info_editing_newANDedit.php");
			}
		}
	}

For Quote 1:

 

All fields that I use in the table are VCHAR datatype except for 'town_city' and 'postcode' which are ENUM datatypes and 'postcode' which is CHAR. I know that this binding method only uses a few data types such as string, decimal etc. and maybe the ENUM data type is causing the trouble here. I have been reading up on this but I can't find any info on the web which suggests that passing an ENUM type as a string would cause any issues. I'm relatively new to PHP and MySQL.

 

For Quote 2:

 

Not sure what is causing this exactly, I'm aware that outputting echo statements earlier on the script can cause this error and I haven't included the entirety of my code on this example. Just the piece that is causing the issue. 

 

Any help on this would be appreciated.

 

Thanks  :mellow:

Link to comment
Share on other sites

  • 2 months later...

Hi]

 

Is this still active as got the following error on my records.php page

 

Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /home/sites/irhwebsites.com/public_html/irhwebsites-crm/records.php on line 129

 

I have seen others had it and have corrected the coding but still got the error

 

on line 129 is the following

$stmt->bind_result($id, $firstname, $lastname, $company);

I got the correct number of strings in the following coding

$stmt->bind_param("sssi", $firstname, $lastname, $company, $id);

and for the insert part of the coding

$stmt->bind_param("sss", $firstname, $lastname, $company);

Would it help if I posted all the records.php code

 

Any help would be great as can't work it out, it works if it is just firstname and lastname but trying to add a new one at a time

 

Thank you in advance

Edited by ianhaney
Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • Create New...