First off, never take your information from W three Schools. They are often wrong and despite the impression have nothing to do with the W3 Consortium.
Security issues:
Never connect to the DBMS as user root.
Never have a root user without password.
Never pass unescaped data into SQL queries. Or used prepared statements
header('content-type: text/plain; charset=utf-8');
$query = 'INSERT INTO Persons(firstname, lastname, age) VALUES '.PHP_EOL;
$_POST['lastname'] = 'Doe';
$_POST['age'] = '30';
# Query is ok for this value
$_POST['firstname'] = 'John';
$values = "('$_POST[firstname]', '$_POST[lastname]', $_POST[age])";
echo $query . $values .PHP_EOL.PHP_EOL;
# Query is NOT ok
$_POST['firstname'] = "O'Reilly";
$values = "VALUES ('$_POST[firstname]', '$_POST[lastname]', $_POST[age])";
echo $query . $values .PHP_EOL.PHP_EOL;
$_POST['firstname'] = "O', 'a', 15) and now you can inject SQL code here; followed by garbage: ";
$values = "VALUES ('$_POST[firstname]', '$_POST[lastname]', $_POST[age])";
echo $query . $values .PHP_EOL;
Also note that you can't assume its safe to use $POST['age'] either. First off, it's external data and external data is never safe. Secondly, it's a string containing a number (or rather, which you assume is a number). The user may still send anything in there. However, if you …
$_POST['age'] = (int) $_POST['age'];
… then you'd actually have an int. The value would be 0 if the variable could not otherwise be converted to an int.
All of the above can be handled by using prepared statements, as shown below.
Should you really persist in using the mysql-extension, cast ints to int and floats. Then escape strings with mysql_real_escape_string(), which actually uses an active db connection to ask the database how to escape stuff properly.
The root user can do anything and pretty much all of those anythings will never ever be needed by a PHP script. Create a new user and give them SELECT, INSERT, UPDATE and DELETE privileges, or perhaps just SELECT privs on all or most tables while they get the others on a select few (such as DELETE, INSERT, UPDATE on their own shopping cart, while only having INSERT privs on the order table).
The mysql-extension used to access MySQL databases is very old and should no longer be used. There are better alternatives, such as mysqli (i as in Improved) or PDO. PDO does things a bit differently, but is easy to use and also have the advantage of being able to access other databases without changhing the code (more or less). Both mysqli and PDO have support for prepared statements.
As for your question, there are several ways of achieving what you want. It's pretty much the same as letting the user insert records, with the only difference that you will
1. Show them stored data instead of blank fields
2. Have to keep track of which record(s) they are editing
The rest is the same.
You could SELECT stuff FROM table... iterate over the result set to show all those rows in their respective form control elements and send everything back to be updated (or not) as per user selection.
# This is the name of PDO's mysql driver and NOT the same as the
# mysql-extension you have been using
$pdo_driver = 'mysql';
$dsn = sprintf('%s:dbname=%s;host=%s',
$pdo_driver,
'NameOfDatabase',
'127.0.0.7'
);
$user = 'username';
$pass = 'supersecret';
$db = new PDO($dsn, $user, $pass);
# Handle updates for all selected (by checkbox) items
# $_POST['update'] corresponds to checkboxes which have row id as value
# thus giving an easy way to update data. See output section further down first.
if (isset($_POST['update']))
{
echo '
<h2>Updated</h2>';
printf('
<div>%d items to update</div>',
count($_POST['update'])
);
# Prepared statement. This way you don't have to worry about escaping
# data to avoid SQL injection. The data is sent separately from the query
# and the database deals with everything else.
$query = 'UPDATE members SET name=:name WHERE id=:id';
$db->prepare($query);
echo '<pre>';
foreach ($_POST['update'] as $id)
{
printf("%7d: %s\n",
$id,
$_POST['name'][$id]
);
}
# Execute the prepared statement. The only thing sent on each
# execution is the new parameters. Thus, the DBMS doesn't have
# to keep parsing the query every time.
$db->execute(array(':id' => $id, ':name' => $_POST['name'][$id]));
echo '</pre>';
}
# Display all editable data
$stmt = $db->query("SELECT id, name FROM members WHERE stuff='condition'");
echo '
<h2>Users</h2>
<form action="" method="post">
<table>
<thead>
<tr>
<th>Id</th><th>Name</th><th>Update</th>
</tr>
</thead>
<tbody>';
foreach ($stmt->fetchAll() as $row)
{
# Using the id as array element keys for your form elements and using
# checkboxes containing these id values, you have a simple way of
# accessing the data which should be updated (see section handling
# post data above)
printf('
<tr>
<td>%1$d</td>
<td class="formElement textInput"><input type="text" name="name[%1$d]" value="%2$s"></td>
<td class="formElement"><input type="checkbox" name="update[]" value="%1$d"></td>
</tr>',
$row['id'],
$row['name']
);
}
echo '
<tbody>
</table>
<div>
<input type="submit" name="submit" value="submit">
</div>
</form>
This approach should only be used if the user is allowed to edit any record of this table. You have no control over what data they send, so even if you will only display certain record to a specific user, he's the one sending the id values, and as such could send any id value at all.
Another way is to only let the user click links for records to edit and display one record at the time on an "edit record page".
Or you could use the approach shown here, but change the checkboxes to inputs of type button and use ajax to send updates for one record at the time.
