I currently have these PHP pages which lets me add a record to a database. (in this case its members) It works perfectly in the sense that I can ADD, DELETE and VIEW. But Im not sure how to get the edit(or UPDATE functionality working.
Here is my db connection Code:
<?php
// Server Info
$server = 'localhost';
$username = 'root';
$password = '';
$database = 'gamgam';
// Connect to database
$connection = new mysqli($server, $username, $password, $database);
?>
Here is my Add Code:
<!DOCTYPE html>
<html>
<head><title>Insert Users</title></head>
<body>
<h2>Insert User Confirmation</h2>
<form action="<?php $_SERVER['PHP_SELF']?>" method="post"/> <br>
<?php
require_once('connection.php');
echo "<label for='memberID' >Member ID:</label>";
echo "<input type='text' name='memberID' id='memberID' />";
echo "<br /><br />";
echo "<label for='username' >Username:</label>";
echo "<input type='text' name='username' id='username' />";
echo "<br /><br />";
echo "<label for='password' >Password:</label>";
echo "<input type='password' name='password' id='password' />";
echo "<br /><br />";
echo "<label for='fName' >Firstname:</label>";
echo "<input type='text' name='fName' id='fName' />";
echo "<br /><br />";
echo "<label for='lName' >Lastname:</label>";
echo "<input type='text' name='lName' id='lName' />";
echo "<br /><br />";
echo "<label for='address' >Address:</label>";
echo "<input type='text' name='address' id='address' />";
echo "<br /><br />";
echo "<label for='email' >Email:</label>";
echo "<input type='text' name='email' id='email' />";
echo "<br /><br />";
echo "<input type='submit' name='submit' value='Submit' />";
echo "<input type='reset' value='Clear' />";
echo "<br /><br />";
?>
</form>
</section>
<p><a href='login.php'>Login</a></p>
<?php
if(!isset($_POST['submit'])) {
echo 'Please Register';
} else {
$memberID = $_POST['memberID'];
$username = $_POST['username'];
$password = $_POST['password'];
$fName = $_POST['fName'];
$lName = $_POST['lName'];
$address = $_POST['address'];
$email = $_POST['email'];
$query = "INSERT INTO `members`
(MemberID, Username, Password, FirstName, LastName,
StreetAddress, Email)
VALUES ('$memberID', '$username', '$password', '$fName',
'$lName', '$address', '$email')";
mysqli_query($connection, $query)
or die(mysqli_error($connection));
$rc = mysqli_affected_rows($connection);
if ($rc==1)
{
echo '<h4>The database has been updated with the following details: </h4> ';
echo 'MemberID: '.$memberID.'<br />';
echo 'Username: '.$username.'<br />';
echo 'Password: '.$password.'<br />';
echo 'First Name: '.$fName.'<br />';
echo 'Last Name: '.$lName.'<br />';
echo 'Address: '.$address.'<br />';
echo 'Email: '.$email.'<br />';
} else {
echo '<p>The data was not entered into the database this time.</p>';
}
}
?>
</body>
</html>
Here is my View Code:
<!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>
<table border="1" style="width:100%" >
<?php
/*
VIEW.PHP
Displays all data from 'players' table
*/
// connect to the database
include('connection.php');
// get results from database
$result = mysqli_query($connection, "SELECT * FROM members")
or die(mysqli_error());
// loop through results of database query, displaying them in the table
while($row = mysqli_fetch_array( $result )) {
// echo out the contents of each row into a table
echo "<tr>";
echo '<td>' . $row['MemberID'] . '</td>';
echo '<td>' . $row['Username'] . '</td>';
echo '<td>' . $row['Password'] . '</td>';
echo '<td>' . $row['FirstName'] . '</td>';
echo '<td>' . $row['StreetAddress'] . '</td>';
echo '<td>' . $row['Email'] . '</td>';
echo '<td><a href="edit.php?MemberID=' . $row['MemberID'] . '">Edit</a></td>';
echo '<td><a href="delete.php?MemberID=' . $row['MemberID'] . '">Delete</a></td>';
echo "</tr>";
}
// close table>
echo "</table>";
?>
<p><a href="insert_user.php">Add a new record</a></p>
</body>
</html>
And here is the Delete Code:
<?php
// Connect to the database
include('connection.php');
// Confirm that the 'code' variable has been set
if (isset($_GET['MemberID']))
{
// Get the 'MemberID' variable from the URL
$MemberID = $_GET['MemberID'];
// Delete record from database
if ($stmt = $connection->prepare("DELETE FROM members WHERE MemberID = ? LIMIT 1")) {
$stmt->bind_param("i",$MemberID);
$stmt->execute();
$stmt->close();
} else {
echo "ERROR: could not prepare SQL statement.";
}
$connection->close();
// Redirect user after delete is successful
header("Location: view.php");
} else {
// If the 'code' variable isn't set, redirect the user
header("Location: view.php");
}
?>
I have gone through many basic php form templates online trying to incorporate what they have done to achieve results but have not had any success. What code needs to be written for my website to have the functionality to edit records already created in the database without going through phpmyadmin. Any help is apreciated.
Edit will be just just like Add, but you need to read the record first and populate the field values.
Start with the code from add and do something like:
<?php $MemberID = (int) $_GET['MemberID']; ?>
<form action="<?php $_SERVER['PHP_SELF']?>" method="post"/> <br>
<input type="hidden" name="MemberID" value="<?php echo $MemberID; ?>"
<?php
require_once('connection.php');
$result = mysqli_query($connection, "SELECT * FROM members where MemberID = $MemberID") or die(mysqli_error());
// loop through results of database query, displaying them in the table
$row = mysqli_fetch_assoc($result);
extract($row);
echo "<label for='memberID' >Member ID:</label>";
echo "$memberID"; // member ID should not be editable
echo "<br /><br />";
echo "<label for='username' >Username:</label>";
echo "<input type='text' name='username' id='username' value="$username" />";
echo "<br /><br />";
The PHP code will have a query like
`UPDATE `members` SET `username` = '$username' ... WHERE `MemberID` = '$MemberID'"