MySQLi+PHP table update/add records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

MySQLi+PHP table update/add records

Post by tobig »

Hi every1! Im new in MySQLI and PHP. Trying to create an editable table, but my code is not working. I'm getting no errors or warnings. It just dont update or add records. Im getting my form with all values but after clicking submit nothing change. The records stil the same.
Here is my code:

Code: Select all

<?php
//connect
include ("db/connect.php");

//create new/edit

function renderForm($nameS = '', $nummerS = '', $dateS='', $stuckS='', $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>Name: </strong> <input type="text" name="name" value="<?php echo $nameS; ?>"/><br/>
			<strong>Nummer: </strong> <input type="text" name="nummer" value="<?php echo $nummerS; ?>"/><br/>
			<strong>Date: </strong> <input type="text" name="date" value="<?php echo $dateS; ?>"/><br/>
			<strong>Stuck: </strong> <input type="text" name="stuck" value="<?php echo $stuckS; ?>"/><br/>
			<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'];
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = htmlentities($_POST['date'], ENT_QUOTES);

//check if empty
if($name == '' || $nummer == '' || $stuck == ''){
	$error = 'ERROR: Please fill in all required fields!';
	renderForm($name, $nummer, $date, $stuck, $error, $id);
}else{
	// if everything is fine, update the record in the database
	if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, cdate=?, stuck =? WHERE id=?")){
		$stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);
		$stmt->execute();
		$stmt->close();
	}else{
		//show error
		echo "ERROR: could not prepare SQL statement.";
	}
	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 ware WHERE id=?"))
{
	$stmt->bind_param("i",$id);
	$stmt->execute();

	$stmt->bind_result($id,$name, $nummer, $cdate, $stuck);
	$stmt->fetch();

	//show form
	renderForm($name, $nummer, $cdate, $stuck, 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
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = date ("d-m-y");


// check that are both not empty
if ($name == '' || $nummer == '' || $stuck == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($name, $nummer, $cdate, $stuck, $error);
}
else
{
// insert the new record into the database
if ($stmt = $mysqli->prepare("INSERT ware (name, nummer, cdate, stuck) VALUES (?, ?,?,?)"))
{
$stmt->bind_param("ss", $name, $nummer,$cdate, $stuck);
$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();
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

Code: Select all

        if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, cdate=?, stuck =? WHERE id=?")){
                $stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);
First thing that jumped out at me. You're trying to define five bound parameters but have only specified types for three. That should be generating an error, but you're not listening for it. Fix that and add some error handling if $mysqli->execute() fails.
tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

Re: MySQLi+PHP table update/add records

Post by tobig »

Celauran wrote:

Code: Select all

        if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, cdate=?, stuck =? WHERE id=?")){
                $stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);
First thing that jumped out at me. You're trying to define five bound parameters but have only specified types for three. That should be generating an error, but you're not listening for it. Fix that and add some error handling if $mysqli->execute() fails.
Thank you for your answer!

I dont think that i did it right but i tried.
Here is reworked part of code but still the same. No updating.

Code: Select all

...
$id = $_POST['id'];
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = htmlentities($_POST['date'], ENT_QUOTES);

//check if empty
if($name == '' || $nummer == '' || $cdate = '' || $stuck == '' || $id == ''){
	$error = 'ERROR: Please fill in all required fields!';
	renderForm($name, $nummer, $cdate, $stuck, $error, $id);
}else{
	// if everything is fine, update the record in the database
	if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, cdate=?, stuck =? WHERE id=?")){
		$stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);

		if($stmt->execute()){
		// it worked
			echo "DONE!";
		}else {
   		// it didn't
			echo "ERROR!";
		}
		
		$stmt->close();
	}else{
		//show error
		echo "ERROR: could not prepare SQL statement.";

	}
	header("location:view.php");
}...
i think its happens because of that $cdate var. In my table it is the date of addit or editing.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

Code: Select all

$stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);
You still have this to fix. Should probably 'ssssi' (or maybe 'ssisi' -- I don't know what $nummer is, so I'm guessing)
tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

Re: MySQLi+PHP table update/add records

Post by tobig »

Thank you for fast reply!
I updated that line but stil no results.

Code: Select all

$stmt->bind_param("ssiii",$name,$nummer, $cdate, $stuck,$id);
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

Consider removing the header call so you're not being redirected away before seeing output from your script. Capture the return value of $stmt->execute() and inspect it. Check $mysqli->error(). Those should shed some light on what's happening.
tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

Re: MySQLi+PHP table update/add records

Post by tobig »

Warning: mysqli::prepare(): (42S22/1054): Unknown column 'cdate' in 'field list' in ...records.php on line 67.
As i said, that $cdate var is giulty!!!
how can i change it?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

Also note you've got the same error in your create record as you do in your edit record section.

Code: Select all

$stmt->bind_param("ss", $name, $nummer,$cdate, $stuck);
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

tobig wrote:Warning: mysqli::prepare(): (42S22/1054): Unknown column 'cdate' in 'field list' in ...records.php on line 67.
As i said, that $cdate var is giulty!!!
how can i change it?
Not quite. The variable names aren't important. It's saying you're referencing a column -- cdate -- which does not exist in the table. Looks like there's a typo in the column name or the column just doesn't exist.
tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

Re: MySQLi+PHP table update/add records

Post by tobig »

i reworked it like this.

Code: Select all

$cdate = date ("d-m-y");
...
if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, date=?, stuck =? WHERE id=?"))...
so now im getting actuall date and i deleted "C" sign before "date = ?" cause the name of the field is "date" in my table.

now im getting that error
Warning: mysqli_stmt::execute(): (22007/1292): Incorrect datetime value: '0' for column 'date' at row 1 in ...records.php on line 69.

Yeah, thank you for noting that! Im changing it with my edit section!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

Dates in MySQL are typically YYYY-MM-DD. I recommend storing them as such and doing whatever transforms you need on display. Will make life easier.
tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

Re: MySQLi+PHP table update/add records

Post by tobig »

i changed it like this

Code: Select all

$cdate = date('Y-m-d H:i:s');
but getting that error:
Incorrect datetime value: '0' for column 'date' at row 1.

any suggestions?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

That line by itself is insufficient context. Looks like something got mixed up somewhere, though, as 0 is being passed to the prepared statement. Please provide your updated code and we can investigate.
tobig
Forum Newbie
Posts: 12
Joined: Wed May 04, 2016 6:46 am

Re: MySQLi+PHP table update/add records

Post by tobig »

Code: Select all

<?php
//connect
include ("db/connect.php");

//create new/edit

function renderForm($nameS = '', $nummerS = '', $dateS='', $stuckS='', $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>Name: </strong> <input type="text" name="name" value="<?php echo $nameS; ?>"/><br/>
			<strong>Nummer: </strong> <input type="text" name="nummer" value="<?php echo $nummerS; ?>"/><br/>
			<strong>Date: </strong> <input type="text" name="date" value="<?php echo $dateS; ?>"/><br/>
			<strong>Stuck: </strong> <input type="text" name="stuck" value="<?php echo $stuckS; ?>"/><br/>
			<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'];
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = date('Y-m-d H:i:s');

//check if empty
if($name == '' || $nummer == '' || $cdate = '' || $stuck == ''){
	$error = 'ERROR: Please fill in all required fields!';
	renderForm($name, $nummer, $cdate, $stuck, $error, $id);
}else{
	// if everything is fine, update the record in the database
	if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, date=?, stuck =? WHERE id=?")){
		$stmt->bind_param("ssiii",$name,$nummer, $cdate, $stuck, $id);
		if(!$stmt->execute()) echo $stmt->error;
		/*if($stmt->execute()){
			// it worked
			echo "DONE!";
		}else {
   		// it didn't
			echo "ERROR!";
		}*/

		$stmt->close();
	}else{
		//show error
		echo "ERROR: could not prepare SQL statement.";

	}
	//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 ware WHERE id=?"))
{
	$stmt->bind_param("i",$id);
	$stmt->execute();

	$stmt->bind_result($id,$name, $nummer, $cdate, $stuck);
	$stmt->fetch();

	//show form
	renderForm($name, $nummer, $cdate, $stuck, 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
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = date ("d-m-y");


// check that are both not empty
if ($name == '' || $nummer == '' || $stuck == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($name, $nummer, $cdate, $stuck, $error);
}
else
{
// insert the new record into the database
if ($stmt = $mysqli->prepare("INSERT ware (name, nummer, date, stuck) VALUES (?, ?,?,?)"))
{
$stmt->bind_param("ssii", $name, $nummer,$cdate, $stuck);
$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();
?>
error message says:
incorrect datetime value: '0' for column 'date' at row 1 line 69
that is the line with folowing code: (if(!$stmt->execute()) echo $stmt->error;)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQLi+PHP table update/add records

Post by Celauran »

Date is a string. That needs to be 'ssisi'
Post Reply