Php mysql edit data

Tekky · July 26, 2022

Hello. I'd like to make a form page where i can edit the data stored in my data base. Here's the steps i need :

-> Select the date corresponding to the data row i want to edit.

-> Show in <input> the current row data so i can edit only the <input> i want and the other's will write the same as before.

-> Press button to send.

With one condition : if no data available for the date selected, then show <input> with nothing in, so we can start uploading new data in a new row corresponding to the date.

I already did 80% of the program i think, the input show the current data row and when button pressed, the data corresponding to today date get updated, but i have a problem with the date picker, i can't get to change the <input> data depending of the days selected. And same with the condition, i'm not sure where to add the code for it.

Following next the total code i've done so far already :

Form part :

<?php

$host = "localhost";
$username = "client";
$password = "client";
$dbname = "site";// On crée la connection PDO ici

$dsn = "mysql:host=$host;dbname=$dbname";
$pdo = new PDO($dsn, $username, $password);

$date = $_GET['date'] ?? date('Y-m-d');          //  si aucun input, on met la date d'aujourd'hui par défault
$days = $_GET['days'] ?? 0;                      // le nombre de jour par défault a 0

$dt = new DateTime($date);
$newdate = $dt->modify("$days days")->format('Y-m-d');

//query
$res = $pdo->prepare("SELECT * FROM cercles WHERE dates = ? ");
$res->execute([$newdate]);

if ($res->rowCount() > 0){

$row = $res->fetch();

$Energie = $row["Energie"];
$Planete = $row["Planete"];
$Lune = $row["Lune"];
$Saint = $row["Saint"];

$JardinConseil=$row["JardinConseil"];
$Ange=$row["Ange"];
$ConseilJour=$row["ConseilJour"];
$Soleil=$row["Soleil"];
$TypeLune=$row["TypeLune"];

echo

"<html>
<body>
<form>
<button name='days' value='-1'>&lt;</button>
<input type='date' name='date' value='<?=$newdate?>' >
<button name='days' value='1' >&gt;</button>

    <form action='phpUpdateFormScript.php' method='post'>
    Date: $newdate<br>
    Changer la date : <input type='date' name='dates' value='$newdate'>
    <br>

    Energie: <input type='text' name='Saint' value='$Energie'><br>
    Planete: <input type='text' name='Saint' value='$Planete'><br>
    Conseil jardin: <input type='text' name='Saint' value='$JardinConseil'><br>
    Ange: <input type='text' name='Saint' value='$Ange'><br>
    Saint: <input type='text' name='Saint' value='$Saint'><br>
    Conseil du jour: <input type='text' name='Saint' value='$ConseilJour'><br>
    Lune: <input type='text' name='Energie' value='$Lune'><br>
    Soleil: <input type='text' name='Planete' value='$Soleil'><br>
    TypeLune: <input type='text' name='Lune' value='$TypeLune'><br><br>
    <input type ='submit'>
    </form>
</form
</body>
</html>";

} else {
	echo "Not Found";
}

?>

Update script used in the <form action> :

<?php

$newdate = $_POST["dates"];
$Planete = $_POST["Planete"];
$Lune = $_POST["Lune"];
$Saint = $_POST["Saint"];
$Energie=$_POST["Energie"];
$JardinConseil=$_POST["JardinConseil"];
$Ange=$_POST["Ange"];
$ConseilJour=$_POST["ConseilJour"];
$Soleil=$_POST["Soleil"];
$TypeLune=$_POST["TypeLune"];

$servername = "localhost";
$username = "client";
$password = "client";
$db = "site";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
	die("Connection failed: ". $conn->connect_error);
}

$sql = "update cercles set `dates`='[$newdate]',`Energie`='$Energie',`Planete`='$Planete',`JardinConseil`='$JardinConseil',`Ange`='$Ange',`Saint`='$Saint',`ConseilJour`='$ConseilJour',`Lune`='$Lune',`Soleil`='$Soleil',`TypeLune`='$TypeLune'";

if ($conn->query($sql) === TRUE) {
	echo "Les données suivant ont étaient mis a jour pour la date ".$newdate." : ".$Energie"-".$Planete."-".$JardinConseil."-".$Ange"-".$Saint"-".$ConseilJour"-".$Soleil"-".$TypeLune;
} else {
	echo "Error: ".$sql."<br>".$conn->error;
}

$conn->close();

?>

The front-end result :

image.png.068baf8321308bcf782ecdc58e23a9b1.png

Thank you in advance for the help

Edited July 26, 2022 by Tekky

mac_gyver · July 26, 2022

the reason the date isn't being used in the first type='date' field is because that's already a php string. don't use <?= ?> tags around the variable, just use the $newdate variable - <input type='date' name='date' value='$newdate' >

next, nested forms are invalid. the date search form needs to be closed with a </form> tag, before you start the next form.

several of the form fields have the same name, so, only the value from the last one will be used.

the post method form processing code should be on the same page as the form. this will simplify all the code and allow you to repopulate the field values if there is a user/validation error in the form processing code.

all those lines of code copying one variable to another is a waste of your time typing. just use the original variables.

why on earth are you using the PDO database extension to get the existing data to be edited, then using the mysqli database extension in the post method form processing code? just use the much simpler PDO extension everywhere. also, use a prepared query for the UPDATE query and the UPDATE query needs a WHERE clause so that you are updating the correct row.

lastly, if there can be more than one row per date, you need to loop to fetch and produce the edit form(s) with either one total form or one form per row and you would need to use an id (autoincrement primary index) to determine which row to update.

Edited July 26, 2022 by mac_gyver

mac_gyver · July 26, 2022

2 hours ago, Tekky said:

data in a new row

the key to updating an existing row or inserting a new row is the existence or absence of an id (autoincrement primary index) in the form data.

if your database table doesn't already have an id/autoincrment column, add it. when you query to get the existing data to edit, add this column to the SELECT ... list (you should actually list out the columns you are selecting, rather than use *). output the id in a hidden form field. in the form processing code, if the id input is a true value, execute the code for an UPDATE query. if it is not a true value, execute the code for an INSERT query.

to handle both having existing data to edit or no data, you should have an intermediate array variable, such as $post, that will initially hold any existing data that you fetch from the SELECT query, then inside the post method form processing code be assigned a trimmed copy of the $_POST form data. in the form value attributes, use php's null coalescing operator (??) to output the corresponding $post value or an empty value -

Energie: <input type='text' name='Energie' value='<?=$post['Energie'] ?? ''?>'><br>

Psycho · July 27, 2022

On 7/26/2022 at 10:17 AM, mac_gyver said:

if your database table doesn't already have an id/autoincrment column, add it. when you query to get the existing data to edit, add this column to the SELECT ... list (you should actually list out the columns you are selecting, rather than use *). output the id in a hidden form field. in the form processing code, if the id input is a true value, execute the code for an UPDATE query. if it is not a true value, execute the code for an INSERT query.

That is generally how I would handle that logic as well. But, I would add a couple comments on that. Based on what I see in the OP's code it would seem that the 'dates' value should be unique. If only relying upon the id value being present or not, it could lead to duplicate records for the same 'dates' value. A unique constraint can be added to the column in the DB, but there will also need to be code to check for that condition. That can be done by doing a SELECT first to see if there is a record for that date (as is already being done), but that does allow for a race condition to allow duplicates. Depending on the needs of the application that may not be an issue. But, the proper way is to execute the INSERT statement and capture the error when it fails and check the error to see if it was due to a uniqueness issue. I'll be honest that I almost never do this:)

Tekky · July 28, 2022

On 7/26/2022 at 3:28 PM, mac_gyver said:

next, nested forms are invalid. the date search form needs to be closed with a </form> tag, before you start the next form.

Then how should i do it?

Thank you for the clear answer by the way, i really appreciate it.

mac_gyver · July 28, 2022

2 hours ago, Tekky said:

Then how should i do it?

you have a get method (the default for a form without a method attribute) search form. where does that form currently have an opening <form> tag and where is its closing </form> tag?

next, you have a post method edit (and insert) form. where does that form currently have an opening <form ...> tag and where is its closing </form> tag?

mac_gyver · July 28, 2022

and if you do all of that, plus a bunch of fundamental things i didn't bother to write out, you should end up with code that looks like this (untested) -

<?php

// initialization
session_start(); // used by success message
 
$host = "localhost";
$username = "client";
$password = "client";
$dbname = "site";// On crée la connection PDO ici
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			];

$dsn = "mysql:host=$host;dbname=$dbname;charset=$DB_ENCODING";
$pdo = new PDO($dsn, $username, $password, $options);

$post = []; // array to hold a trimmed working copy of the form data and initially holds the data to be edited.
$errors = []; // array to hold user/validation errors.

// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	// trim all the data at once
	$post = array_map('trim',$_POST); // if any input is an array, use a recursive trim call-back function here instead of php's trim

	// if there is a true id input, you are editing an existing row
	// if there is not a true id input, you are inserting a new row
	
	// validate inputs here... storing validation errors in the $errors array, using the field name as the array index
	
	// if no errors, use the input data
	if(empty($errors))
	{
		if($post['id'])
		{
			// update existing row
			$sql = "update cercles set dates=?, Energie=? WHERE id = ?";
			$stmt = $pdo->prepare($sql);
			$stmt->execute([ $post['dates'] ,$post['Energie'], $post['id'] ]);
			// if this query can result in duplicate or out of range data, handle that here and setup a message for the user letting them know what exactly was wrong with the data that they submitted
			// the current design assumes that there is only one row per dates. this column should be defined as a unique index
			// in which case inserting/updating a duplicate date will result in an error.
		}
		else
		{
			// insert new row
			$sql = "INSERT cercles (dates,Energie) VALUE (?,?)"; // set `dates`='[$newdate]',`Energie`='$Energie',`Planete`='$Planete',`JardinConseil`='$JardinConseil',`Ange`='$Ange',`Saint`='$Saint',`ConseilJour`='$ConseilJour',`Lune`='$Lune',`Soleil`='$Soleil',`TypeLune`='$TypeLune'";
			$stmt = $pdo->prepare($sql);
			$stmt->execute([ $post['dates'],$post['Energie'] ]);
			// if this query can result in duplicate or out of range data, handle that here and setup a message for the user letting them know what exactly was wrong with the data that they submitted
			// the current design assumes that there is only one row per dates. this column should be defined as a unique index
			// in which case inserting/updating a duplicate date will result in an error.
		}
	}
	
	// if no errors, success
	if(empty($errors))
	{
		// to display a one-time success message, store it in a session variable, then test, dusplay, and clear that variable at the appropriate location in the html document
		$_SESSION['success_message'] = "Les données suivant ont étaient mis a jour..."; // pour la date $newdate : $Energie-$Planete-$JardinConseil-$Ange-$Saint-$ConseilJour-$Soleil-$TypeLune";
		// redirect to the exact same url of the current page to cause a get request for the page
		die(header("Refresh:0"));
	}
}

// get method business logic - get/produce data needed to display the page

$date = $_GET['date'] ?? date('Y-m-d');          //  si aucun input, on met la date d'aujourd'hui par défault
$days = $_GET['days'] ?? 0;                      // le nombre de jour par défault a 0

$dt = new DateTime($date);
$newdate = $dt->modify("$days days")->format('Y-m-d');

// if the form has never been submitted, get any existing data to be edited -
if(empty($post))
{
	//query
	// you should list the columns you are selecting
	// and build the sql query in a php variable
	$sql = "SELECT id, dates, Energie FROM cercles WHERE dates = ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$newdate]);
	$post = $stmt->fetch();
}

// html document
?>
<html>
<body>

<?php
// display the search form
?>
<form>
<button name='days' value='-1'>&lt;</button>
<input type='date' name='date' value='<?=$newdate?>' >
<button name='days' value='1' >&gt;</button>
</form>

<?php
// display any success message
if(isset($_SESSION['success_message']))
{
	echo "<p>{$_SESSION['success_message']}</p>";
	unset($_SESSION['success_message']);
}
?>

<?php
// display any errors
if(!empty($errors))
{
	echo '<p>'.implode('<br>',$errors).'</p>';
}
?>

<?php
// display the edit/insert form
if(empty($post['id']))
{
	echo "There is no existing data to edit, create a new entry:";
}
else
{
	echo "Edit existing data:";
}
?>
<form method='post'>
<input type='hidden' name='id' value='<?= $post['id'] ?? ''?>'>
Date: <?=$newdate?><br>
Changer la date : <input type='date' name='dates' value='<?= $post['dates'] ?? $newdate?>'><br>
Energie: <input type='text' name='Energie' value='<?= $post['Energie'] ?? ''?>'><br>
<input type ='submit'>
</form>

</body>
</html>

this is incomplete (no validation logic or error handling for duplicate unique database entries) and only operates on a few of the form fields. you need to get your logic to work at all before you worry about all the code and markup needed for all the form fields. in fact, if you have more than about 2-3 form fields, you should use a data-driven design, where you have a data structure (array, database table) that defines the fields, what validation there is for each field, and what processing will be done for each field, then loop over that definition to validate and process (build either the update or insert query) to dynamically do this rather than to write out code for every field.

Tekky · July 29, 2022

22 hours ago, mac_gyver said:

and if you do all of that, plus a bunch of fundamental things i didn't bother to write out, you should end up with code that looks like this (untested) -

<?php

// initialization
session_start(); // used by success message
 
$host = "localhost";
$username = "client";
$password = "client";
$dbname = "site";// On crée la connection PDO ici
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			];

$dsn = "mysql:host=$host;dbname=$dbname;charset=$DB_ENCODING";
$pdo = new PDO($dsn, $username, $password, $options);

$post = []; // array to hold a trimmed working copy of the form data and initially holds the data to be edited.
$errors = []; // array to hold user/validation errors.

// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	// trim all the data at once
	$post = array_map('trim',$_POST); // if any input is an array, use a recursive trim call-back function here instead of php's trim

	// if there is a true id input, you are editing an existing row
	// if there is not a true id input, you are inserting a new row
	
	// validate inputs here... storing validation errors in the $errors array, using the field name as the array index
	
	// if no errors, use the input data
	if(empty($errors))
	{
		if($post['id'])
		{
			// update existing row
			$sql = "update cercles set dates=?, Energie=? WHERE id = ?";
			$stmt = $pdo->prepare($sql);
			$stmt->execute([ $post['dates'] ,$post['Energie'], $post['id'] ]);
			// if this query can result in duplicate or out of range data, handle that here and setup a message for the user letting them know what exactly was wrong with the data that they submitted
			// the current design assumes that there is only one row per dates. this column should be defined as a unique index
			// in which case inserting/updating a duplicate date will result in an error.
		}
		else
		{
			// insert new row
			$sql = "INSERT cercles (dates,Energie) VALUE (?,?)"; // set `dates`='[$newdate]',`Energie`='$Energie',`Planete`='$Planete',`JardinConseil`='$JardinConseil',`Ange`='$Ange',`Saint`='$Saint',`ConseilJour`='$ConseilJour',`Lune`='$Lune',`Soleil`='$Soleil',`TypeLune`='$TypeLune'";
			$stmt = $pdo->prepare($sql);
			$stmt->execute([ $post['dates'],$post['Energie'] ]);
			// if this query can result in duplicate or out of range data, handle that here and setup a message for the user letting them know what exactly was wrong with the data that they submitted
			// the current design assumes that there is only one row per dates. this column should be defined as a unique index
			// in which case inserting/updating a duplicate date will result in an error.
		}
	}
	
	// if no errors, success
	if(empty($errors))
	{
		// to display a one-time success message, store it in a session variable, then test, dusplay, and clear that variable at the appropriate location in the html document
		$_SESSION['success_message'] = "Les données suivant ont étaient mis a jour..."; // pour la date $newdate : $Energie-$Planete-$JardinConseil-$Ange-$Saint-$ConseilJour-$Soleil-$TypeLune";
		// redirect to the exact same url of the current page to cause a get request for the page
		die(header("Refresh:0"));
	}
}

// get method business logic - get/produce data needed to display the page

$date = $_GET['date'] ?? date('Y-m-d');          //  si aucun input, on met la date d'aujourd'hui par défault
$days = $_GET['days'] ?? 0;                      // le nombre de jour par défault a 0

$dt = new DateTime($date);
$newdate = $dt->modify("$days days")->format('Y-m-d');

// if the form has never been submitted, get any existing data to be edited -
if(empty($post))
{
	//query
	// you should list the columns you are selecting
	// and build the sql query in a php variable
	$sql = "SELECT id, dates, Energie FROM cercles WHERE dates = ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$newdate]);
	$post = $stmt->fetch();
}

// html document
?>
<html>
<body>

<?php
// display the search form
?>
<form>
<button name='days' value='-1'>&lt;</button>
<input type='date' name='date' value='<?=$newdate?>' >
<button name='days' value='1' >&gt;</button>
</form>

<?php
// display any success message
if(isset($_SESSION['success_message']))
{
	echo "<p>{$_SESSION['success_message']}</p>";
	unset($_SESSION['success_message']);
}
?>

<?php
// display any errors
if(!empty($errors))
{
	echo '<p>'.implode('<br>',$errors).'</p>';
}
?>

<?php
// display the edit/insert form
if(empty($post['id']))
{
	echo "There is no existing data to edit, create a new entry:";
}
else
{
	echo "Edit existing data:";
}
?>
<form method='post'>
<input type='hidden' name='id' value='<?= $post['id'] ?? ''?>'>
Date: <?=$newdate?><br>
Changer la date : <input type='date' name='dates' value='<?= $post['dates'] ?? $newdate?>'><br>
Energie: <input type='text' name='Energie' value='<?= $post['Energie'] ?? ''?>'><br>
<input type ='submit'>
</form>

</body>
</html>

this is incomplete (no validation logic or error handling for duplicate unique database entries) and only operates on a few of the form fields. you need to get your logic to work at all before you worry about all the code and markup needed for all the form fields. in fact, if you have more than about 2-3 form fields, you should use a data-driven design, where you have a data structure (array, database table) that defines the fields, what validation there is for each field, and what processing will be done for each field, then loop over that definition to validate and process (build either the update or insert query) to dynamically do this rather than to write out code for every field.

Thank you a lot for everything and i'm sorry for the late answer, it took me a bit of time to understand every part, and as you said, it's just about logic working before even starting to code. I'm really gratefull, i managed to make it work with other's fields and everything. And i now understand why doing both in the same code is also easier !

Tekky · July 29, 2022

A last small question, whenever a string contain a special character (in my case, the apostrophe), the sentence stop right before and half of the sentence is missing and etc. On my other project i was used to do this :

<?php 
$jourconseil=utf8_encode($row['ConseilJour']);
?>

But since i was used to copy one variable to another for nothing. I was wondering if there was a way to do it without using a variable.

On 7/26/2022 at 3:28 PM, mac_gyver said:

all those lines of code copying one variable to another is a waste of your time typing. just use the original variables.

I tried this :

<input type='text' class="form-control" id="formJour" name='ConseilJour' value='<?= utf8_encode($post['ConseilJour']) ?? ''?>'>

But it doesn't really work, is there any way to do this ?(the php we should be looking at is in the <value>)

thank you again for all the help

mac_gyver · July 29, 2022

apply htmlentities(), with the ENT_QUOTES flag, when you output the value.

Tekky · July 29, 2022

28 minutes ago, mac_gyver said:

htmlentities()

I also tried this, but this doesn't work on all my input, the only one working is :

value='<?= htmlentities($post['ConseilJour'],ENT_QUOTES) ?? ''?>

value='<?= htmlentities($post['ConseilJour'],ENT_QUOTES) ?? ''?>

But if i do it with another value, for example :

value='<?= htmlentities($post['JardinConseil'],ENT_QUOTES) ?? ''?>

This is not working on this input

image.png.64ff64417c26eb293d11357d2cdbfdea.png

This is why i'm a bit confused...

mac_gyver · July 29, 2022

while this (probably) isn't the cause of the problem, the Null Coalescing Operator ?? goes with the php variable, for the case where it won't exist when creating/inserting new data. value='<?= htmlentities($post['ConseilJour']??'',ENT_QUOTES)?>'

what does the view source of the web page show? i suspect that the character(s) in question are not simple quotes that htmlentities operates on, leaving them as is, but are meaningful to the browser and are breaking the markup.

Tekky · July 29, 2022

The input which is working show :

<input type='text' class="form-control" id="formJour" name='ConseilJour' value='un t&#039;a'>

and one of the input not working :

<input type='text' class="form-control" id="formJardin" name='JardinConseil' value='Ceci est un conseil d'>

Barand · July 29, 2022

Instead of

value='<?= htmlentities($post['JardinConseil'],ENT_QUOTES) ?? ''?>'
      ^                                                           ^

try

value="<?= htmlentities($post['JardinConseil'],ENT_QUOTES) ?? ''?>"
      ^                                                           ^

Tekky · July 29, 2022

This is working fine when a data is in, but once nothing is in the string, i get this error :

on the code web view :

<input type='text' class="form-control" id="formJardin" name='JardinConseil' value="<br />
<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1' cellspacing='0' cellpadding='1'>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Notice: Trying to access array offset on value of type bool in C:\wamp64\www\b\form.php on line <i>186</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left' bgcolor='#eeeeec'>Function</th><th align='left' bgcolor='#eeeeec'>Location</th></tr>
<tr><td bgcolor='#eeeeec' align='center'>1</td><td bgcolor='#eeeeec' align='center'>0.0001</td><td bgcolor='#eeeeec' align='right'>363456</td><td bgcolor='#eeeeec'>{main}(  )</td><td title='C:\wamp64\www\b\form.php' bgcolor='#eeeeec'>...\form.php<b>:</b>0</td></tr>
</table></font>
">

mac_gyver · July 29, 2022

the undefined error is due to this -

50 minutes ago, mac_gyver said:

the Null Coalescing Operator ?? goes with the php variable, for the case where it won't exist when creating/inserting new data. value='<?= htmlentities($post['ConseilJour']??'',ENT_QUOTES)?>'

Tekky · July 29, 2022

4 hours ago, mac_gyver said:

the undefined error is due to this -

Oh i'm sorry i read this part too fast earlier. Thank you again!

Sign In

Php mysql edit data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information