Jump to content


Photo

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


  • Please log in to reply
58 replies to this topic

#31 TomTom

TomTom

    New member

  • New Members
  • 5 posts

Posted 08 April 2012 - 01:57 PM

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.
  • 0

#32 Ben

Ben

    Administrator

  • Administrators
  • 5,643 posts
  • LocationChico, CA

Posted 08 April 2012 - 07:00 PM

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

  • 0
Benjamin Falk
Falken Creative : Twitter

#33 TomTom

TomTom

    New member

  • New Members
  • 5 posts

Posted 09 April 2012 - 06:20 AM

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.
  • 0

#34 Ben

Ben

    Administrator

  • Administrators
  • 5,643 posts
  • LocationChico, CA

Posted 09 April 2012 - 08:43 PM

If you are wanting the user to only select one of the boxes, and be unable to select multiple boxes at one time, you probably should use radio buttons rather than checkboxes.
  • 0
Benjamin Falk
Falken Creative : Twitter

#35 TomTom

TomTom

    New member

  • New Members
  • 5 posts

Posted 10 April 2012 - 01:49 PM

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?
  • 0

#36 romero01

romero01

    New member

  • New Members
  • 1 posts

Posted 24 April 2012 - 05:36 AM

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()?;
  • 0

#37 b7itzz

b7itzz

    New member

  • New Members
  • 1 posts

Posted 01 November 2012 - 06:57 PM

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.
  • 0

#38 ImproperUsername

ImproperUsername

    New member

  • New Members
  • 1 posts

Posted 12 January 2013 - 06:33 PM

I see that this is an old post, but I want to thank Ben for this. It is extremely helpful in my effort to move from mysql to mysqli.
  • 0

#39 josephbupe

josephbupe

    New member

  • New Members
  • 4 posts

Posted 07 February 2013 - 04:14 AM

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
  • 0

#40 josephbupe

josephbupe

    New member

  • New Members
  • 4 posts

Posted 07 February 2013 - 12:34 PM

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
  • 0

#41 cygnet

cygnet

    Member

  • Member
  • PipPip
  • 21 posts

Posted 14 May 2013 - 06:49 AM

Great! Will try it out and see does it really works or not!
  • 0

#42 mscro

mscro

    New member

  • New Members
  • 1 posts

Posted 19 June 2013 - 07:18 PM

Hi, Great tutorial. Work fine and i have learned a lot. Thank you for so good and easy tutorial.
  • 0

#43 JeffH13

JeffH13

    New member

  • New Members
  • 9 posts

Posted 26 July 2013 - 08:10 AM

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.
  • 0

#44 Ben

Ben

    Administrator

  • Administrators
  • 5,643 posts
  • LocationChico, CA

Posted 27 July 2013 - 11:03 PM

@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.
  • 0
Benjamin Falk
Falken Creative : Twitter

#45 JeffH13

JeffH13

    New member

  • New Members
  • 9 posts

Posted 28 July 2013 - 08:49 AM

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>&nbsp;</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.
  • 0

#46 Heisenberg

Heisenberg

    New member

  • New Members
  • 1 posts

Posted 31 August 2013 - 11:49 AM

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...l4/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();
?>
  • 0

#47 Benny

Benny

    New member

  • New Members
  • 1 posts

Posted 11 September 2013 - 05:58 AM

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

  • 0

#48 Skippy1957

Skippy1957

    New member

  • New Members
  • 3 posts

Posted 10 January 2014 - 05:29 AM

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...l4/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();
?>

Attached Files


  • 0

#49 Ben

Ben

    Administrator

  • Administrators
  • 5,643 posts
  • LocationChico, CA

Posted 10 January 2014 - 09:21 AM

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.
  • 0
Benjamin Falk
Falken Creative : Twitter

#50 Skippy1957

Skippy1957

    New member

  • New Members
  • 3 posts

Posted 10 January 2014 - 02:57 PM

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.


  • 0

#51 Skippy1957

Skippy1957

    New member

  • New Members
  • 3 posts

Posted 10 January 2014 - 02:58 PM

Thanks Ben !!!
I got it
  • 0

#52 cathrinestellaau

cathrinestellaau

    New member

  • New Members
  • 3 posts
  • Facebook:https://www.facebook.com/technocrabsolutions
  • LocationIndia

Posted 11 January 2014 - 02:03 AM

Here nice information is shared regarding the normal user who started to learn PHP, because the most important thing that a user know is to mug up these queries. Thank I like this post as searching and get this one.
  • 0

#53 loggainsd

loggainsd

    New member

  • New Members
  • 1 posts

Posted 11 February 2014 - 04:09 PM

I have made some changes to fit me and everything is working except add and edit.

Attached is records.php

I'm sure it's something simple and was wondering if you can help me out.

Thanks,

Attached Files


  • 0

#54 jasonborne

jasonborne

    New member

  • New Members
  • 1 posts

Posted 16 February 2014 - 02:14 PM

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.
  • 0

#55 Ben

Ben

    Administrator

  • Administrators
  • 5,643 posts
  • LocationChico, CA

Posted 17 February 2014 - 10:07 AM

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.
  • 0
Benjamin Falk
Falken Creative : Twitter

#56 settingupSolo

settingupSolo

    New member

  • New Members
  • 1 posts

Posted 21 May 2014 - 11:53 AM

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.

 

Attached File  settingupsoloAdmin.zip   7.14KB   25 downloads

 

 

Thank you for your help!


  • 0

#57 Ben

Ben

    Administrator

  • Administrators
  • 5,643 posts
  • LocationChico, CA

Posted 22 May 2014 - 09:24 PM

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.


  • 0
Benjamin Falk
Falken Creative : Twitter

#58 Kandu

Kandu

    New member

  • New Members
  • 1 posts

Posted 27 June 2014 - 04:30 PM

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!';
 }
 }
?>

  • 0

#59 sovereign

sovereign

    New member

  • New Members
  • 1 posts

Posted 11 August 2014 - 04:08 AM

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:


  • 0




3 user(s) are reading this topic

0 members, 2 guests, 0 anonymous users


    Facebook (1)