updating database

Justafriend · September 10, 2017

I am having a hard time updating a entry rather then me just pasting my whole script ill break it up so you understand where it is coming from I just don't know where to put

<form action="" method="POST" enctype="multipart/form-data"> to include the data from the select box

Step one a simple echo form to post all the results of the query which works perfectly

<html>
<?php
	try {
		$dbh = new PDO("mysql:dbname=directors;host=localhost","root","");
	}catch(PDOException $e) {
		die('Error');
	}

	$tableheader = false;
	$query = "SELECT * FROM `comments` ORDER BY `id` ASC";
	$sth = $dbh->prepare($query);

	if(!$sth->execute()) {
		die('Error');
	}

	echo "<table>";

	while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
		if($tableheader == false) {
			echo '<tr>';
			foreach($row as $key=>$value) {
				echo "<th>{$key}</th>";
			}
			echo '</tr>';
			$tableheader = true;
		}
		echo "<tr>";
		foreach($row as $value) {
			echo "<td>{$value}</td>";
		}
		echo "</tr>";
	}
	echo "</table>";
	?>

Step 2 is to use the info from the database to create a drop down menu for the id numbers which are listed and this works fine

// To get the id numbers currently in the db to update the records which need to be looked at


<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('directors');

$sql = "SELECT id FROM comments";
$result = mysql_query($sql);

echo "UPDATE RECOED NUMBER" . "<select name='id'>";
while ($row = mysql_fetch_array($result)) {
    echo "<option value='" . $row['id'] ."'>" . $row['id'] ."</option>";
}
echo "</select>";
?>
// rest of form to use to update remaining columns
</br>
NNN Read <input id="NNNread" type="checkbox" value="yes"></br>
Habs Read <input id="HabsRead" type="checkbox"value="yes"></br>
Directors Comments:</br><textarea name="Directorscomments" rows="10" cols="25"></textarea></br>
<input type="submit" value="Submit" name="submit">
</form>
<?php
include("config.php");
if(isset($_POST['submit'])) {
    $Directorscomments = $_POST['Directorscomments'];
    $NNNread = $_POST['NNNread'];
    $HabsRead = $_POST['HabsRead'];
    $id = $_SESSION['id'];
// updating blank entrys  for NNNread and HabsRead  as well as to concat directors comments to one entry

    $sql = "UPDATE comments SET NNNread=:NNNread, HabsRead=:HabsRead, SET Directorscomments = CONCAT(Directorscomments, 'Directorscomments') WHERE id=:id";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(":Directorscomments", $Directorscomments, PDO::PARAM_STR);
	$stmt->bindValue(":NNNread", $NNNread, PDO::PARAM_STR);
    $stmt->bindValue(":HabsRead", $HabsRead, PDO::PARAM_STR);
    $stmt->bindValue(":id", $id, PDO::PARAM_STR);
    $stmt->execute();
}
?>
</select>

</html>

and the bottom part is the rest of the form

Justafriend · September 10, 2017

I just figured out where I could put it I put it on the line above the 2nd piece of code but the issue is that this code isn't updating it just on submits acts like its posting but doesn't update the id row

mac_gyver · September 11, 2017

why not just produce 'edit' links, in the first piece of code, that would have the id as a get parameter on the end of the each link?

next, why are you making an in-line pdo connection, a mysql_ connection, and an PDO connection via an included file? this is just making more work and more different sections of code that you must keep track of. a point of programming, isn't to use the most code to accomplish a task. programming is already a tedious typing task, you should be looking for ways of reducing the amount of work you have to do to produce code.

if you have code in an external .php file that's making a PDO connection, just use that connection in all cases and if you eliminate the select/option menu in favor of links, the code using the obsolete mysql_ statements will go-a-way.

any post method form processing code needs to be near the top of your file, before the start of your html document.

Justafriend · September 12, 2017

i never even thought of that i appreciate your idea and yes that would be a lot better to work with the select code I had used from a script to quickly solve an issue till I had more time to write it

Justafriend · September 12, 2017

Ok i got a code to link the edit and that part works i have almost got it all working but the inputs to populate the form are all populating with errors and I get an error on top of pages I have counted many times and everything matches up

The only thing that might be the difference is do I need to have a spot for each column from db even though those columns aren't going to be ever updated

here is the code I have

<?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($first = '', $last ='', $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 } ?>
Your SHG Player Name(required):<input type="text" name="commentfrom" required="required" value="<?php echo $commentfrom; ?>"/> /></br>
Your Email Address(required):  <input type="text" name="email" required="required" value="<?php echo $email; ?>"/> /></br>
Player Or Host this is about: <input type="text" name="about" value="<?php echo $about; ?>"/> /></br>
Do you Require a Director to contact you?:<p>
<input type="text" name="reply" value="<?php echo $reply; ?>"/>  <br/>
Please include your comments here:</br><textarea name="comments" rows="10" cols="25"value="<?php echo $comments; ?>"/>></textarea></br>
NNN Read <input id="NNNread" type="text" value="<?php echo $NNNread; ?>"/>></br>
Habs Read <input id="HabsRead" type="text" value="<?php echo $HabsRead; ?>"/>></br>
Category :</br><textarea name="category " rows="10" cols="25">value="<?php echo $category; ?>"/></textarea></br>

<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'];
$commentfrom = htmlentities($_POST['commentfrom'], ENT_QUOTES);
$email = htmlentities($_POST['email'], ENT_QUOTES);
$about = htmlentities($_POST['about'], ENT_QUOTES);
$reply = htmlentities($_POST['reply'], ENT_QUOTES);
$comments = htmlentities($_POST['comments'], ENT_QUOTES);
$NNNread = htmlentities($_POST['NNNread'], ENT_QUOTES);
$HabsRead = htmlentities($_POST['HabsRead'], ENT_QUOTES);
$Directorscomments = htmlentities($_POST['Directorscomments'], ENT_QUOTES);
$category  = htmlentities($_POST['category '], ENT_QUOTES);
// check that commentfrom and email are both not empty
//if ($commentfrom == '' || $email == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($id, $commentfrom, $email, $about, $reply, $comments, $NNNread, $HabsRead, $Directorscomments, $category);
}

{
// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE comments SET commentfrom = ?, email = ?, about = ?, reply = ?, comments = ?, NNNread = ?, HabsRead = ?, Directorscomments = ?, category = ?
WHERE id=?"))
{
$stmt->bind_param("ssi", $commentfrom, $email, $about, $reply, $comments, $NNNread, $HabsRead, $Directorscomments, $category, $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 comments WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();

$stmt->bind_result($id, $commentfrom, $email, $about, $reply, $comments, $NNNread, $HabsRead, $Directorscomments, $category);
$stmt->fetch();

// show the form
renderForm($commentfrom, $email, $about, $reply, $comments, $NNNread, $HabsRead, $Directorscomments, $category,  $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");
}
}
}


$mysqli->close();
?>

Gandalf64 · September 12, 2017

Another way to go about doing this is when you display the record(s). For example on my website I have a CMS for my web page(s) and I do this ->

if (isset($_SESSION['user']) && ($_SESSION['user']->security_level === 'sysop' || $_SESSION['user']->id === $this->row->user_id)) {
  echo '<div class="system">' . "\n";
  echo '<a class="edit" href="edit/' . urlencode($this->row->id) . '">Edit</a>' . "\n";
  echo '<a class="delete" href="delete_page.php?id=' . urlencode($this->row->id) . '">Delete</a>' . "\n";
  echo "</div>\n";
}

then on top of my edit page (edit.php) I have the following

<?php
require_once '../private/initialize.php';

use Library\CMS\CMS;

protected_page();

$cms = new CMS();

if (isset($_GET['id']) && filter_var($_GET['id'], FILTER_VALIDATE_INT)) {
    $id = filter_var($_GET['id']);
    $result = $cms->readId($id);
} elseif (isset($_GET['id'])) {
    header("Location: members_page.php");
    exit();
}

like I said this is just one way of doing it.

Sign In

updating database

Recommended Posts

Justafriend

Link to comment

Share on other sites

Justafriend

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Justafriend

Link to comment

Share on other sites

Justafriend

Link to comment

Share on other sites

Gandalf64

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information