Jump to content

updating database

Justafriend

By Justafriend
in PHP Coding Help

 Share
Go to solution Solved by Gandalf64,

Recommended Posts

Justafriend Member

Justafriend

Posted
Posted

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

 

Link to comment
https://forums.phpfreaks.com/topic/304938-updating-database/
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted

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.

Link to comment
https://forums.phpfreaks.com/topic/304938-updating-database/#findComment-1551145
Share on other sites
Justafriend Member

Justafriend

Posted
  • Author
Posted

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();
?>
Link to comment
https://forums.phpfreaks.com/topic/304938-updating-database/#findComment-1551166
Share on other sites
  • Solution
Gandalf64 Member

Gandalf64

Posted
  • Solution
Posted

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. 

Link to comment
https://forums.phpfreaks.com/topic/304938-updating-database/#findComment-1551174
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.