homepage Welcome to WebmasterWorld Guest from 54.166.123.2
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Update and Delete data in database using forms
Help with code to request the data then edit or delete
AliTaylor4411




msg:4190618
 9:23 pm on Aug 22, 2010 (gmt 0)

Hi Guys,

I have a form which I am using successfully to insert data into my database. I want to copy this form and use it to edit or delete data from my database.

For example I have a table called agent. If they want to update a record in the agent table they access (through a menu page) a list of all the records in the agent table. I then want them to click on which record they want (identified by Agent$id and Agent Name) which will then take them to the form which will have the whole record in the fields and then be able to UPDATE or DELETE.

Any ideas?

This is the form:-

<form method="post" action="insertagent.php">
<p><table width="100%" border="0" cellpadding="1" class="LOGO">
<tr class="logo_table">
<td width="14%" align="left" valign="middle"><img src="../images/home-logo.png" alt="LOGO" width="137" height="127" align="absmiddle" /> HOMES LIMITED ESTATE AGENTS</td>
</tr>
</table>
<p>&nbsp;</p>
<table width="50%" border="0" align="center" cellpadding="0">
<tr class="insert_header">
<td>INSERT A NEW AGENT RECORD</td>
</tr>
</table>
<p>&nbsp;</p>
<table width="100%" border="0" cellpadding="0" class="instruction_bar">
<tr>
<td>Complete the form below and click SUBMIT button to insert a new record into the Database</td>
</tr>
</table>
<p>&nbsp;</p>
<table width="100%" border="0" cellpadding="0" class="box_table">
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">First Name</td>
<td width="15%"><input name="firstname" type="varchar">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">Surname</td>
<td><input name="surname" type="varchar">&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">Agent Mobile No</td>
<td width="15%"><input name="mobnumber" type="varchar">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">Address (Street)</td>
<td><input name="add1" type="varchar">&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">Office</td>
<td width="15%"><input name="officeid" type="varchar">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">Address (Area)</td>
<td><input name="add2" type="varchar">&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">Address (Town/City)</td>
<td><input name="add3" type="varchar">&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
<tr>
<td width="5%">&nbsp;</td>
<td width="20%">Postcode</td>
<td><input name="postcode" type="varchar">&nbsp;</td>
<td>&nbsp;</td>
<td width="20%">&nbsp;</td>
<td width="15%">&nbsp;</td>
<td width="5%">&nbsp;</td>
</tr>
</table>
<p>&nbsp;</p>
<table width="100%" border="0" cellpadding="0" class="insert_buttoncolbg">
<tr>
<td><span class="insert_buttoncolbg"></span>
<input name="Submit" type="submit" class="insert_button" value="Insert Record" /> &nbsp;</td>
</tr>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>

 

Sp4rkyM4rk




msg:4190896
 1:22 pm on Aug 23, 2010 (gmt 0)

To edit data you'll need to get the data from the database first, then set it as a value in the form. Here is an example using first name and a file called editagent.php, assuming you have an ID attached to each record:

<?php

// The ID will be declared in the URL, for example: "editagent.php?id=4"

$getid = $_GET['id'];

$firstname = $_POST['firstname'];
$submit = $_POST['submit'];

$query = mysql_query('SELECT firstname FROM agents WHERE id = "' . $getid . '"');
$rows = mysql_fetch_assoc($query);

$firstnamedb = $rows['firstname'];

echo '<form action="editagent.php?id=' . $getid . '" method="post"><br />
<input type="text" name="firstname" value="' . $firstnamedb . '"><br />
<input type="submit" name="submit">';

if ($submit)
{

// Edit the data

$updatequery = mysql_query('UPDATE agents SET firstname = "' . $firstname . '" WHERE id = "' . $getid . '"');

echo 'The data has updated successfully.';

}

?>


For deleting data, using the same $_GET['id']; as above, it'd be a simple:

$delete = mysql_query('DELETE FROM agents WHERE id = "' . $getid . '"');
rocknbil




msg:4190994
 4:32 pm on Aug 23, 2010 (gmt 0)

There is no input type varchar, it's working because it defaults to text.

What you *should* do is put this form in a function, and pass the form state to it, which will open up a lot of avenues for reusable code. The cliff notes, something like

if (isset($_GET['id') and is_numeric($_GET['id']) and ($_GET['id'] > 0)) {
$form_state = 'edit_item';
$rec = $_GET['id'];
}
else {
$form_state = 'add_item';
$rec=null;
}
$output = get_form($form_state,$rec);
echo $output;

Then the function would do something like this. You'll see calls to other functions, these are not inherent in PHP but you should write them as you can re-use them.


function get_form($action,$id=null) {
//
$table = 'agent_records';
// Get the field names for the agent table so you
// can explicitly initialize values to null, squelching warnings
$fieldnames = get_field_names($table);
foreach ($fieldname as $field) { $agt_values[$field]=null; }
//
if ($action=='edit_form') {
if ($id > 0) {
// Query the DB here so you can populate the form
// with preset values. This idea would return the
// recordset in an associative array "$agt_records"
// which would now overwrite the null values set above.
$agt_records= get_specific_record($table,'id',$id);
}
else { die("You want to edit, but no id!"); }
$recfield = "<input type=\"hidden" name=\"id\" value=\"$id\">";
$button = 'Edit data for ' . $agt_values['fname'] . ' ' . $agt_values['lname'];
}
else {
$recfield=null;
$button = 'Add Record';
}
// Build the form. As I said, "cliff notes." :-)
$form = '
<form action="yourscript.php" method="post">
' . $recfield .
'<input type="hidden" name="frm_action" value="' . $action . '">
'<p><label for="fname">First Name</label>
<input type="text" name="fname" id="fname" value="' .
$agt_values['fname'] . '"></p>
<p><input type="submit" value="' . $button . '"></p>
</form>
';
return $form;
}


Then when you submit, you would either ADD or UPDATE based on

$_POST['frm_action']

You'll note a couple things that move you toward "reusable code."

$fieldnames = get_field_names($table);

This function would accept a table name and return a list array of all fields in the table - any table in your database. It's required for the next one to work - or, you could incorporate this function into the next one. There are cases where you may want a list of table names by itself - i.e., headers for an "all records" display, so it may be more useful if it's a separate function.

$agt_records= get_specific_record($table,'id',$id);

This one is the same way - pass the table name, the unique ID field to query (in case you've wisely not made all your unique id's "id" for every single table,) and the specific unique id to query. It would return an associative array of the values for the record:

$agt_values = Array {
'fname' => 'John',
'lname' => 'Doe'
);

To prevent PHP warnings for add, when there's no records ("undefined index 'fname') we initialize this array right after getting the field names, allowing you to use the same variables for the value attribute in the form.

It's not too late to back up and think forward. :-)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved