Page 1 of 1

updating existing records

Posted: Tue May 23, 2006 10:33 am
by jtoara
Hi all,
I wanted to update existing records on mysql database. What I wanted is I should be able to select from the form using a drop down list...this drop down list would be the primary keys of data on the database...
Now if i select a primary key/record from the drop down list, all the fields on the form should be populated with the correct data for the selcted priamary key...
I should then edit the records on the form and then resubmit...

Can this be done?

If your have any sample forms/examples that does this let me know...


Kind Regards
Joseph

Posted: Tue May 23, 2006 1:40 pm
by bdlang
The gist of it is performing a SELECT on the table, pulling down the primary key value to be used in the UPDATE along with any relevant data to display to the user, e.g. a username or product name that the primary key references.
(untested example)

Code: Select all

SELECT ID, product FROM sometable;
PHP code creates the MySQL connection, performs the query and returns the values to the form structure, e.g.
(untested pseudocode example)

Code: Select all

$result= mysql_query('SELECT ID, product FROM sometable');
echo '<select>';
while ( $row= mysql_fetch_assoc($result) ) {
    echo "<option value='{$row['ID']}'>{$row['product']}</option>";
}
echo '</select>';
The form submission calls on PHP to perform an UPDATE based on the key value, e.g.
(untested pseudocode example)

Code: Select all

$result= mysql_query("UPDATE sometable SET somevalue='{$_POST['somevalue']}' WHERE ID={$_POST['ID']});
Obviously you'll need to validate POST data coming from the form, etc prior to any attempts to INSERT or UPDATE into your database.
Beware SQL injection.

MySQL Manual: Data Manipulation statements
MySQL Manual: UPDATE syntax
PHP Manual: MySQL functions -or- MySQLi functions (depending on your versions of PHP / MySQL)