PHP form update mysql database returns all database records after editing one record

Question

so I have this code that other Stack members have helped me fine tune and correct some errors, the code all works as it should but there is one small detail, after successfully editing one record and clicking the update button ALL of the existing records that are in the database load into the page. Here is my code below:

<?php 

$con = mysql_connect("localhost", "root", "M1q2w3e4r");
if (!$con) {
die("Can not connect: " . mysql_error());
}
mysql_select_db("inventory",$con);

if (isset($_POST['update'])){
$UpdateQuery = "UPDATE invoice SET `inv_number`='$_POST[inv_number]', `from_date`='$_POST[from_date]', `to_date`='$_POST[to_date]',`date_type`='$_POST[date_type]', `notes`='$_POST[notes]' WHERE id='$_POST[id]'";               
mysql_query($UpdateQuery, $con);
};

$where = '';
    if(!empty($_GET) && !empty($_GET['edit'])) {
        $where = ' where id='.$_GET['edit'];
    }
    $sql = "SELECT * FROM invoice".$where;

$myData = mysql_query($sql,$con);
echo "<table border=1>
<tr>
<th>Inv #</th>
<th>From</th>
<th>To</th>
<th>Type</th>
<th>Notes</th>
</tr>";
while($record = mysql_fetch_array($myData)){
echo "<form action='edit.php' method='post'>";
echo "<tr>";
echo "<td>" . "<input type='text' name='inv_number' value='" . $record['inv_number'] . "'> </td>";
echo "<td>" . "<input type='text' id='from' name='from_date' value='" . $record['from_date'] . "'> </td>";
echo "<td>" . "<input type='text' id='to' name='to_date' value='" . $record['to_date'] . "'> </td>";
echo "<td>" . "<input type='text' name='date_type' value='" . $record['date_type'] . "'> </td>";
echo "<td>" . "<input type='text' name='notes' value='" . $record['notes'] . "'> </td>";
echo "<td>" . "<input type='hidden' name='id' value='" . $record['id'] . "'> </td>";
echo "<td>" . "<input type='hidden' name='hidden' value='" . $record['id'] . "'> </td>";
echo "<td>" . "<input type='submit' name='update' value='update'>" . " </td>";
echo "</tr>";
echo "</form>";
}

echo "</table>";
mysql_close($con);

?>

I know it has to do with the form action="edit.php", as it refreshes the page the id number in the url is pulled out. So I tried this:

echo "<form action='edit.php?edit=<?php echo $_REQUEST["id"]; ?>' method='post'>";

but this only led to my edit.php to display as a blank page. If anyone can help me figure out how to prevent all the database records from being displayed in the page after clicking the update button it would really help.

Either you omit the SELECT, use two different files or do a header redirect after successful query. The latter is your best bet. You can't have your cake and eat it too ;) — Funk Forty Niner
– Funk Forty Niner, Commented Sep 23, 2014 at 3:10
what do you want it to do? Display the updated record or nothing? There are a few ways to solve it. — Geoduck
– Geoduck, Commented Sep 23, 2014 at 3:15
ideally, after the update has taken place, i would like the page to redirect back my index page. first step is index, second is edit.php to retrieve the current record values, then edit.php to submit new values and then ideally a redirect afterwards. — WebEducate
– WebEducate, Commented Sep 23, 2014 at 3:17
if its easier, just preventing all the other records (rest of the database) from displaying after updating the record on the edit.php page will do fine for my purposes — WebEducate
– WebEducate, Commented Sep 23, 2014 at 3:19
mysql_query($UpdateQuery, $con); header("Location: http://www.example.com/index.php"); exit; - done like dinner — Funk Forty Niner
– Funk Forty Niner, Commented Sep 23, 2014 at 3:19

Geoduck · Accepted Answer · 2014-09-23 03:18:14Z

2

I might do this, for example, if I just wanted to show the record that was just updated:

if (isset($_POST['update'])){
    $UpdateQuery = "UPDATE invoice SET `inv_number`='$_POST[inv_number]',    `from_date`='$_POST[from_date]', `to_date`='$_POST[to_date]',`date_type`='$_POST[date_type]', `notes`='$_POST[notes]' WHERE id='$_POST[id]'";               
    mysql_query($UpdateQuery, $con);
    $where = ' where id='.$_POST[id];
}
else {
    $where = '';
    if(!empty($_GET) && !empty($_GET['edit'])) {
        $where = ' where id='.$_GET['edit'];
    }
}

You could also use REQUEST instead of GET and make a hidden input field with the name "edit" in your form.

answered Sep 23, 2014 at 3:18

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WebEducate Over a year ago

yes this worked nicely, thank you Garr and Fred, the redirect may not be the best bet actually since I can just provide a link back to the previous page in order to load the new changes to the updated record(s). Thanks guys

Collectives™ on Stack Overflow

PHP form update mysql database returns all database records after editing one record

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related