Update Entire SQL Table

[espfutbol98]

I was wondering how would I echo an entire table with a link on each row to the update page specifically for that row. I can already create a new row, view an entire table, but I have no clue how to do this.

If you need any source code, just ask. Thanks for the help.

[gomarc]

This full working example shows you how to delete records from a shopping cart.

With some changes, you can make it update records instead of deleting them.

Link to example

[espfutbol98]

I have gotten close to finishing but when I get to update.php?rid=<WHATEVER> and click the update button, nothing happens. The information from the database goes in the inputs but it cant update:

<?php

$record = $_GET["rid"];

$db_server = "localhost";
$db_name = "database";
$db_user = "username";
$db_pass = "password";

$con1 = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db($db_name, $con1);
    
if (isset($_POST['submit']) && $_POST['submit'] == "Update") {
$query_update = "UPDATE table SET " .
        "date = '" . $_POST['date'] . "', " .
        "amount = '" . $_POST['amount'] . "', " .
        "description = '" . implode(", ",$_POST['description']) . "', " . 
        "name = '" . $_POST['name'] . "', " .
        "paid = '" . $_POST['paid'] . "', " .
        "id = '" . $_POST['id'] .
        "' WHERE id = '" . $record . "'";
$result_update = mysql_query($query_update) or die(mysql_error());
header("Location: view_table.php");
}
else {
$query = "SELECT * FROM table " .
           "WHERE id = '" . $record . "'";
$result = mysql_query($query) 
    or die(mysql_error());
$row = mysql_fetch_array($result);
$description = explode(", ", $row['description']);
?> 
<h1>Update Debt</h1>
Date: <input type="text" name="date" value="<?php echo $row['date']; ?>" /> <br />
Amount: <input type="text" name="amount" value="<?php echo $row['amount']; ?>" /> <br />
Description: <textarea name="description[]"><?php echo $row['description']; ?></textarea> <br />
Name: <input type="text" name="name" value="<?php echo $row['name']; ?>" /> <br />
Paid: <input type="text" name="paid" value="<?php echo $row['paid']; ?>" /> <br />
ID: <input type="text" name="id" value="<?php echo $row['id']; ?>" /> <br />
<input type="submit" name="submit" value="Update" />
<?php
}
?>

[xav0989]

Here is a working version, with some comments:

• Always sanitize data from the user (with mysql_real_escape_string() )
• Try separating your code and your content, always -> MVC
• You need to wrap a form with the <form> tag for it to work
• Be consistent with your indenting style

<?php

$record = $_GET["rid"];

$db_server = "localhost";
$db_name = "database";
$db_user = "username";
$db_pass = "password";

$con1 = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db($db_name, $con1);

$record = mysql_real_escape_string($record);
    
if (isset($_POST['submit']) && $_POST['submit'] == "Update") {
    $query_update = "UPDATE table SET " .
            "date = '" . $_POST['date'] . "', " .
            "amount = '" . $_POST['amount'] . "', " .
            "description = '" . implode(", ",$_POST['description']) . "', " . 
            "name = '" . $_POST['name'] . "', " .
            "paid = '" . $_POST['paid'] . "', " .
            "id = '" . $_POST['id'] .
            "' WHERE id = '" . $record . "'";
    $result_update = mysql_query($query_update) or die(mysql_error());
    header("Location: view_table.php");
    exit();
} else {
    $query = "SELECT * FROM table " .
               "WHERE id = '" . $record . "'";
    $result = mysql_query($query) 
        or die(mysql_error());
    $row = mysql_fetch_array($result);
    $description = explode(", ", $row['description']);
}
?> 
<h1>Update Debt</h1>
<form action="?" method="post">
Date: <input type="text" name="date" value="<?php echo $row['date']; ?>" /> <br />
Amount: <input type="text" name="amount" value="<?php echo $row['amount']; ?>" /> <br />
Description: <textarea name="description[]"><?php echo $row['description']; ?></textarea> <br />
Name: <input type="text" name="name" value="<?php echo $row['name']; ?>" /> <br />
Paid: <input type="text" name="paid" value="<?php echo $row['paid']; ?>" /> <br />
ID: <input type="text" name="id" value="<?php echo $row['id']; ?>" /> <br />
<input type="submit" name="submit" value="Update" />
</form>

[misson]

description[] probably isn't doing what you expect. In the code posted, $_POST['description'] is a one element array. You could drop the implode() with no ill effect.

To xav0989's recommendations I'd add replace (2) "or die(...)" with exceptions or trigger_error. You can also perform some validation/sanitization with the filter functions.

[espfutbol98]

I tried the source above by xav0989 and the page shows all of the data from the sql database but when I click Change, I am redirected but the data is not updated. I am sure all of the sql corresponds to the sql database.

[xav0989]

I see the error, the 'record' get variable isn't passed down. Here is an updated version of the last lines

<h1>Update Debt</h1>
<form action="?rid=<?php echo $record; ?>" method="post">
Date: <input type="text" name="date" value="<?php echo $row['date']; ?>" /> <br />
Amount: <input type="text" name="amount" value="<?php echo $row['amount']; ?>" /> <br />
Description: <textarea name="description"><?php echo $row['description']; ?></textarea> <br />
Name: <input type="text" name="name" value="<?php echo $row['name']; ?>" /> <br />
Paid: <input type="text" name="paid" value="<?php echo $row['paid']; ?>" /> <br />
ID: <input type="text" name="id" value="<?php echo $row['id']; ?>" /> <br />
<input type="submit" name="submit" value="Update" />
</form>

[espfutbol98]

Works great! thx

[xav0989]

You are very welcomed

[espfutbol98]

I found one slight problem easily fixed. It was turning $_POST['description'] into an array that didn't have to be. Here is the final code:

<?php

$record = $_GET["rid"];

$db_server = "localhost";
$db_name = "database";
$db_user = "username";
$db_pass = "password";

$con1 = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db($db_name, $con1);

$record = mysql_real_escape_string($record);
    
if (isset($_POST['submit']) && $_POST['submit'] == "Update") {
    $query_update = "UPDATE table SET " .
            "date = '" . $_POST['date'] . "', " .
            "amount = '" . $_POST['amount'] . "', " .
            "description = '" . $_POST['description'] . "', " . 
            "name = '" . $_POST['name'] . "', " .
            "paid = '" . $_POST['paid'] . "', " .
            "id = '" . $_POST['id'] .
            "' WHERE id = '" . $record . "'";
    $result_update = mysql_query($query_update) or die(mysql_error());
    header("Location: view_debt.php");
    exit();
} else {
    $query = "SELECT * FROM table " .
               "WHERE id = '" . $record . "'";
    $result = mysql_query($query) 
        or die(mysql_error());
    $row = mysql_fetch_array($result);
}
?> 
<h1>Update Debt</h1>
<form action="this_page.php?rid=<?php echo $record; ?>" method="post">
Date: <input type="text" name="date" value="<?php echo $row['date']; ?>" /> <br />
Amount: <input type="text" name="amount" value="<?php echo $row['amount']; ?>" /> <br />
Description: <textarea name="description"><?php echo $row['description']; ?></textarea> <br />
Name: <input type="text" name="name" value="<?php echo $row['name']; ?>" /> <br />
Paid: <input type="text" name="paid" value="<?php echo $row['paid']; ?>" /> <br />
ID: <input type="text" name="id" value="<?php echo $row['id']; ?>" /> <br />
<input type="submit" name="submit" value="Update" />
</form>

Thanks everyone for your help.