Page 1 of 2
Update MySQL with PHP
Posted: Fri Aug 22, 2014 12:12 pm
by heyjim
Hi all,
I've pretty much just started with PHP and I'm trying to make small app/script.
At the moment, the script works well for adding to the DB and viewing from the DB but I'm struggling on how to go about updating the DB.
This is the page when it lists the DB entries and works pretty much how I want it to:
Code: Select all
<?php
//Shows any errors in the script
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
//SQL statement
$result = mysqli_query($con,"SELECT * FROM tbl_customers");
//Display results in table
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Company Name</th>
<th>Firstname</th>
<th>Lastname</th>
<th>E-mail</th>
<th>Update</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['companyname'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
?>
<td><a href="update.php?id=<?echo $row['id'];?>">Update</td>
<?
echo "</tr>";
}
echo "</table>";
?>
This is the page where I would like to take the values from the form and update the DB:
Code: Select all
<?php
//Shows any errors in the script
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
//Gets the ID string from the URL
$id = $_GET['id'];
//SQL statement
$result = mysqli_query($con,"SELECT * FROM tbl_customers WHERE id=$id");
//Display results in table
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Company Name</th>
<th>Firstname</th>
<th>Lastname</th>
<th>E-mail</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
?>
<form method="post" action"edit_customer.php">
<td><input name="companyname" type="text" id="companyname" value="<? echo $row['companyname'] ?>"></td>
<td><input name="firstname" type="text" id="firstname" value="<? echo $row['firstname'] ?>"></td>
<td><input name="lastname" type="text" id="lastname" value="<? echo $row['lastname'] ?>"></td>
<td><input name="email" type="text" id="email" value="<? echo $row['email'] ?>"></td>
<?
echo "</tr>";
}
echo "</table>";
?>
<input type="submit" value="Submit">
</form>
The main bit I'm struggling with is how I tell the "edit_customer.php" page what ID was present so it knows which record to update.
Hope this makes sense and any help would be greatly appreciated.
Thanks
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 12:19 pm
by Celauran
Why not include it in the form via hidden field?
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 12:45 pm
by heyjim
Celauran wrote:Why not include it in the form via hidden field?
Thanks for replying
So if I add that to the form and hide it like this, how do I call that from the next page?
Code: Select all
<form method="post" action"edit_customer.php">
<td><input name="id" type="hidden" id="id" value="<? echo $row['id'] ?>"></td>
<td><input name="companyname" type="text" id="companyname" value="<? echo $row['companyname'] ?>"></td>
<td><input name="firstname" type="text" id="firstname" value="<? echo $row['firstname'] ?>"></td>
<td><input name="lastname" type="text" id="lastname" value="<? echo $row['lastname'] ?>"></td>
<td><input name="email" type="text" id="email" value="<? echo $row['email'] ?>"></td>
<?
echo "</tr>";
}
echo "</table>";
?>
<input type="submit" value="Submit">
</form>
Thanks
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 12:48 pm
by Celauran
$_POST['id']
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 1:23 pm
by heyjim
Sorry, should of known that!
The edit_customer.php is as follows:
Code: Select all
<?php
//Shows any errors in the script
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
//Escape variables for security
$id = mysqli_real_escape_string($con, $_POST['id']);
$companyname = mysqli_real_escape_string($con, $_POST['companyname']);
$firstname = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname = mysqli_real_escape_string($con, $_POST['lastname']);
$email = mysqli_real_escape_string($con, $_POST['email']);
$sql="UPDATE tbl_customers SET companyname='$companyname', firstname='$firstname', lastname'$lastname', email'$email' WHERE id='$id'";
if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
echo "1 record updated";
?>
Now I've done this in a similar way to how my initial submit page is like, but I get undefined index notices - am I missing something or going about it in the wrong way?
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 1:48 pm
by Celauran
My guess is that you're submitting the form with some fields empty, so they aren't in the $_POST array. Before trying to assign them to variables, check if the array key exists and has a value using isset().
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:09 pm
by heyjim
I actually only get that error when browsing to edit_customer.php direct.
If I try to submit from the form, the values revert back to what they originally were?

Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:20 pm
by Celauran
heyjim wrote:I actually only get that error when browsing to edit_customer.php direct.
Which still boils down to what I said; you're trying to assign to variables values from the $_POST array that haven't been set.
heyjim wrote:If I try to submit from the form, the values revert back to what they originally were? :?
What does this mean?
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:28 pm
by heyjim
Celauran wrote:
Which still boils down to what I said; you're trying to assign to variables values from the $_POST array that haven't been set.
Could you point out where I'm going wrong?
This is update.php:
Code: Select all
<?php
//Shows any errors in the script
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
//Gets the ID string from the URL
$id = $_GET['id'];
//SQL statement
$result = mysqli_query($con,"SELECT * FROM tbl_customers WHERE id=$id");
//Display results in table
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Company Name</th>
<th>Firstname</th>
<th>Lastname</th>
<th>E-mail</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
?>
<form method="post" action"edit_customer.php">
<input name="id" type="hidden" id="id" value="<? echo $row['id'] ?>">
<td><input name="companyname" type="text" id="companyname" value="<? echo $row['companyname'] ?>"></td>
<td><input name="firstname" type="text" id="firstname" value="<? echo $row['firstname'] ?>"></td>
<td><input name="lastname" type="text" id="lastname" value="<? echo $row['lastname'] ?>"></td>
<td><input name="email" type="text" id="email" value="<? echo $row['email'] ?>"></td>
<?
echo "</tr>";
}
echo "</table>";
?>
<input type="submit" value="Submit">
</form>
And this is edit_customer.php
Code: Select all
<?php
//Shows any errors in the script
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
//Escape variables for security
$id = mysqli_real_escape_string($con, $_POST['id']);
$companyname = mysqli_real_escape_string($con, $_POST['companyname']);
$firstname = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname = mysqli_real_escape_string($con, $_POST['lastname']);
$email = mysqli_real_escape_string($con, $_POST['email']);
$sql="UPDATE tbl_customers SET companyname='$companyname', firstname='$firstname', lastname'$lastname', email'$email' WHERE id='$id'";
if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
echo "1 record updated";
?>
Celauran wrote:
What does this mean?
When I view the update page, I get the current values from the DB in the form fields. If i then enter my own text into them fields and click submit (to update the data), the page kind of refreshes and puts the original content back in (pulled from the DB) if that makes sense?
Apologies if I'm missing something obvious!
Thanks
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:31 pm
by Celauran
heyjim wrote:When I view the update page, I get the current values from the DB in the form fields. If i then enter my own text into them fields and click submit (to update the data), the page kind of refreshes and puts the original content back in (pulled from the DB) if that makes sense?
So it's never hitting the edit page at all? Because I don't see a redirect back from edit to update.
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:35 pm
by heyjim
Celauran wrote:
So it's never hitting the edit page at all? Because I don't see a redirect back from edit to update.
Yes it never actual goes to the edit page, just kind of refreshes.
Once I manage to get the actual update function work I'll add the links back to the other pages etc.
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:37 pm
by Celauran
Code: Select all
<?php
// First let's check if the form has been submitted. If it hasn't, there's
// nothing to do here and we'll redirect back to update.php
if (empty($_POST)) {
header('Location: update.php');
exit;
}
// You could move this to an included config file to not have to repeat it everywhere
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
// Escape variables for security
// But first we check if they've been set
$id = isset($_POST['id']) ? mysqli_real_escape_string($con, $_POST['id']) : '';
$companyname = isset($_POST['companyname']) ? mysqli_real_escape_string($con, $_POST['companyname']) : '';
$firstname = isset($_POST['firstname']) ? mysqli_real_escape_string($con, $_POST['firstname']) : '';
$lastname = isset($_POST['lastname']) ? mysqli_real_escape_string($con, $_POST['lastname']) : '';
$email = isset($_POST['email']) ? mysqli_real_escape_string($con, $_POST['email']) : '';
// You were missing some = in your query
$sql = "UPDATE tbl_customers SET companyname = '{$companyname}', firstname = '{$firstname}', lastname = '{$lastname}', email = '{$email}' WHERE id = '{$id}'";
if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
echo "1 record updated";
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 2:48 pm
by Celauran
Cleaned up the update page a little as well.
Code: Select all
<?php
//Shows any errors in the script
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
include("includes/db_config.php");
include("includes/check_sql.php");
// Gets the ID string from the URL
$id = isset($_GET['id']) ? mysqli_real_escape_string($con, $_GET['id']) : '';
// SQL statement
// You need to escape here, too! (See above)
$result = mysqli_query($con, "SELECT * FROM tbl_customers WHERE id = $id");
?>
<?php /* You don't really need a table here... */ ?>
<form method="post" action"edit_customer.php">
<input name="id" type="hidden" id="id" value="<?php echo $row['id']; // <?= is fine for echoes, but don't make a habit of short open tags ?>">
<table border='1'>
<tr>
<th>ID</th>
<th>Company Name</th>
<th>Firstname</th>
<th>Lastname</th>
<th>E-mail</th>
</tr>
<?php while($row = mysqli_fetch_array($result)): ?>
<tr>
<td><input name="id" type="hidden" value="<?= $row['id']; ?>"><?= $row['id']; ?></td>
<td><input name="companyname" type="text" id="companyname" value="<?php echo $row['companyname'] ?>"></td>
<td><input name="firstname" type="text" id="firstname" value="<?php echo $row['firstname'] ?>"></td>
<td><input name="lastname" type="text" id="lastname" value="<?php echo $row['lastname'] ?>"></td>
<td><input name="email" type="text" id="email" value="<?php echo $row['email'] ?>"></td>
</tr>
<?php endwhile; ?>
</table>
<input type="submit" value="Submit">
</form>
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 3:02 pm
by heyjim
Ok, so I've made the above changes but the form still doesn't seem to submit to the edit page.
Just out of interest, what does this do on the end of the varibles? Is it because you've added the isset?
Thanks
Re: Update MySQL with PHP
Posted: Fri Aug 22, 2014 3:56 pm
by Celauran
I'm using a ternary operator, which is basically shorthand for an if/else block. If isset() is false, it sets the variable to an empty string.