Edit existing database entries

[David_P]

I have the book "Php, MySQL and Apache All in One"

I did the "Creating an Online Address Book" tutorial and I have it up and running - no problems.

Once you enter someone in the address book and then select an entry to view, you have two options on the page showing the information of the person you selected - "add info" or "select another".

On the "add info" page you're presented with the same page (is same page) as the "add entry" page with the fields empty. Her you can add more information to the existing entry. This is fine and I like this feature, but...

I also want to be able to edit an entry.

On the bottom of the "selentry2.php" page I would like to have an optional link to an "editentry.php" page along with the "add info" or "select another" options.

I can add the link of course, but I need help with the "editentry.php" page itself. I would like for the page to populate the fields with the information of that entry and just make my changes and update the database.

This particular address book uses a relational database. The master table is called master_names (id, f_name & l_name). The sub-tables have the master_id included. These sub-tables include an address table, phone table, fax table, cell table (I added this one), email table and a personal notes table.

I've never worked with a relational database before and this is really throwing me.

Thanks
David P.

[sneakyimp]

i don't have that book.

when you create a link to your editentry.php page, you need to pass along a bit of information to help you retrieve the correct record. given that you are viewing information about someone, the 'id' of that particular database record is probably available in the php code for that page.

if you read the php code of that page you will probably see a query that searches the database for that particular user's information. something like:

SELECT * FROM master_names WHERE id='$id';

and then a variable is created that they use to create the link to the addinfo page. you'll need to pass that var along to your editentry.php page.

<a href="editentry.php?id=<?=$id ?>">edit entry</a>

then your edit entry will need to find that id in $_GET['id'] and create a query to fetch the information you want to edit.

[David_P]

It's a 3-part "editentry.php" page.

First - it displays the names of the entries with an Edit link next to the names. The edit link - links to the same page (editentry.php)

Second - it displays the same fields from the "addentry2.php" page, but with the information from the database tables filled in. Here you can make changes to whichever field you want and hit submit (also linked to editentry.php)

Third - It updates the database tables with the new information.

This is what it's suppose to do, but it's not working out. It's populating the fields (to a degree), but it's not updating the tables.

Here's the code:

<?php
include('includes/top.php');
?>

<br />
<p align="center"><b><a href="address.php">Return to Address Book Main Page</a></b></p>
<br />
<h3 align="center">Edit an Address Entry</h3>


<?php
//connect to database
        $conn = mysql_connect("localhost", "username", "password") or die(mysql_error());
        mysql_select_db("database",$conn)  or die(mysql_error()); 


if(!isset($cmd)) 
{ 
//display all the entries 


$result = mysql_query("select * from master_name order by l_name"); 

//run the while loop that grabs all the entry names
while($r = mysql_fetch_array($result)) 
{ 
//grab the names and the id of entries

$l_name=$r["l_name"];//take out the last name 
$f_name=$r["f_name"];//take out the first name 
$id=$r["id"];//take out the id 

echo "
<p><!--Id:&nbsp;$id &nbsp;&nbsp;-->$l_name, $f_name&nbsp;&nbsp;&nbsp;<a href=\"editentry.php?cmd=edit&id=$id\">Edit</a><br /><br />
"; 

} 
} 
?>



<?php

if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit") 
{ 
if (!isset($_POST["submit"])) 
{
$id = $_GET["id"]; 
$sql = "SELECT * FROM master_name WHERE id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>

<center>
<table width="95%" cellspacing="0" cellpadding="3" border="0">
<tr>
<td>

<form action="editentry.php" method="post">


<input type=hidden name="id" value="<? echo $myrow['id'] ?>"> 

First / Last Names:<br /> 
<INPUT TYPE="TEXT" NAME="f_name" VALUE="<? echo $myrow['f_name'] ?>" SIZE=30> 

&nbsp;&nbsp;

<INPUT TYPE="TEXT" NAME="l_name" VALUE="<? echo $myrow['l_name'] ?>" SIZE=30

<br /><br /> 
</td>
</tr>



<?php
$sql = "SELECT * FROM address WHERE master_id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>
<tr>
<td>
<br /><br />
	<P>City/State/Zip:<br>
	<input type="text" name="city" VALUE="<? echo $myrow['city'] ?>" size=30 maxlength=50>
	<input type="text" name="state" VALUE="<? echo $myrow['state'] ?>" size=5 maxlength=2>
	<input type="text" name="zipcode" VALUE="<? echo $myrow['zipcode'] ?>" size=10 maxlength=10>

<P>Address Type:<br>
<input type="radio" name="add_type" value="home" checked> home
<input type="radio" name="add_type" value="work"> work
<input type="radio" name="add_type" value="other"> other

<br /><br />
</td>
</tr>


<?php
$sql = "SELECT * FROM telephone WHERE master_id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>
<tr>
<td>
	<P>Telephone Number:<br>
	<input type="text" name="tel_number" VALUE="<? echo $myrow['tel_number'] ?>" size=30 maxlength=25>
	<input type="radio" name="tel_type" value="home" checked> home
	<input type="radio" name="tel_type" VALUE="work"> work
	<input type="radio" name="tel_type" VALUE="other"> other

<br /><br />
</td>
</tr>


<?php
$sql = "SELECT * FROM fax WHERE master_id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>
<tr>
<td>
	<P>Fax Number:<br>
	<input type="text" name="fax_number" VALUE="<? echo $myrow['fax_number'] ?>" size=30 maxlength=25>
	<input type="radio" name="fax_type" value="home" checked> home
	<input type="radio" name="fax_type" value="work"> work
	<input type="radio" name="fax_type" value="other"> other

<br /><br />
</td>
</tr>


<?php
$sql = "SELECT * FROM cell WHERE master_id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>
<tr>
<td>
	<P>Cell Phone Number:<br>
	<input type="text" name="cell_number" VALUE="<? echo $myrow['cell_number'] ?>" size=30 maxlength=25>
	<input type="radio" name="cell_type" value="personal" checked> personal
	<input type="radio" name="cell_type" value="work"> work

<br /><br />
</td>
</tr>


<?php
$sql = "SELECT * FROM email WHERE master_id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>
<tr>
<td>

<P>Email Address:<br>
<input type="text" name="email" VALUE="<? echo $myrow['email'] ?>" size=30 maxlength=150>
<input type="radio" name="email_type" value="home" checked> home
<input type="radio" name="email_type" value="work"> work
<input type="radio" name="email_type" value="other"> other

<br /><br />
</td>
</tr>


<?php
$sql = "SELECT * FROM personal_notes WHERE master_id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 
?>
<tr>
<td>
	<P>Personal Note:<br>
	<textarea name="note" cols=35 rows=5 wrap=virtual><? echo $myrow['note'] ?></textarea>

<br /><br />
</td>
</tr>

<tr>
<td>
<input type="hidden" name="cmd" value="edit"> 
<input type="submit" name="submit" value="submit"> 
</form> 
</td>
</tr>

</table>
</center>

<?php
}

?>






<?php
if ($_POST["$submit"]) {


if ($_POST[master_id] == "") {
    //update master_name table

$f_name = $_POST['f_name'];
$l_name = $_POST['l_name'];

$sql = "UPDATE master_name SET f_name='$f_name', l_name='$l_name' WHERE master_id=$id)";
$result = mysql_query($sql); 



    //get master_id for use with other tables
    $master_id = mysql_insert_id();
} else {
     $master_id = $_POST[master_id];
}

if (($_POST[address]) || ($_POST[city]) || ($_POST[state]) || ($_POST[zipcode])) {
	//something relevant, so edit address table

$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$add_type = $_POST['add_type'];

	$sql = "UPDATE address SET address='$address', city='$city', state='$state', zipcode='$zipcode', add_type='$add_type' WHERE master_id=$id)";
$result = mysql_query($sql); 
	}


if ($_POST[tel_number]) {
	//something relevant, so add to telephone table

$tel_number = $_POST['tel_number'];
$tel_type = $_POST['tel_type'];

	$sql = "UPDATE telephone SET tel_number='$tel_number', tel_type='$tel_type' WHERE master_id=$id)";
$result = mysql_query($sql); 
	}


if ($_POST[fax_number]) {
	//something relevant, so add to fax table

$fax_number = $_POST['fax_number'];
$fax_type = $_POST['fax_type'];

	$sql = "UPDATE fax SET fax_number='$fax_number', fax_type='$fax_type' WHERE master_id=$id)";
$result = mysql_query($sql); 
	}


if ($_POST[cell_number]) {
	//something relevant, so add to cell table

$cell_number = $_POST['cell_number'];
$cell_type = $_POST['cell_type'];

	$sql = "UPDATE cell SET cell_number='$cell_number', cell_type='$cell_type' WHERE master_id=$id)";
$result = mysql_query($sql); 
	}


if ($_POST[email]) {
	//something relevant, so add to email table

$email = $_POST['email'];
$email_type = $_POST['email_type'];

	$sql = "UPDATE email SET email='$email', email_type='$email_type' WHERE master_id=$id)";
$result = mysql_query($sql); 
	}


if ($_POST[note]) {
	//something relevant, so add to notes table

$note = $_POST['note'];

	$sql = "UPDATE personal_notes SET note='$note' WHERE master_id=$id)";
$result = mysql_query($sql); 
	}



echo "


<h3 align=\"center\">Entry has been successfully edited</h3>

<p align=\"center\">Would you like to <a href=\"editentry.php\">Edit another Entry</a></p>


";

  }

}
?>


<br /><br />

<p align="center"><b><a href="address.php">Return to Address Book Main Page</a></b></p>

<?php
include('includes/bottom.php');
?>

[sneakyimp]

what, exactly, is going wrong? let's start with specific stuff and work through it.

[David_P]

I just edited the code in my last post, beginning with the second part of the editentry.php - where the fields are populated with the information from the database tables - through the final part that is suppose to update the tables.

I pretty much am getting the fields populated, but the tables aren't updating with the changes.

*Update to this post - Actually, I'm not getting the form populated as I should. I have a couple of entries that have 2 or 3 phone numbers listed and only 1 is being returned on the editentry.php page.

Also, this address book uses, as a field: "Type" - "Enum" - ('home', 'work', 'other')
these are radio buttons that you select when initially entering into the address book. I do not know how to make these populate with the correct setting.

--- I know there are other address books out there that are available with all these things already. But, I like this one because it only requires 4 php pages, including the one I'm trying to make to edit an entry. Plus it seamlessly fits right into my existing web page. Since I will be the only one using it, I don't need a sign in script thrown in, but if I ever do I can always use the server side .htaccess.

[sneakyimp]

your code appears to have the correct query to fetch more than one phone number but the php doesn't loop to show all the results. you should change this:

<?php
$sql = "SELECT * FROM telephone WHERE master_id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<tr>
<td>
    <P>Telephone Number:<br>
    <input type="text" name="tel_number" VALUE="<? echo $myrow['tel_number'] ?>" size=30 maxlength=25>
    <input type="radio" name="tel_type" value="home" checked> home
    <input type="radio" name="tel_type" VALUE="work"> work
    <input type="radio" name="tel_type" VALUE="other"> other

<br /><br />
</td>
</tr> 

to this:

<?php
$sql = "SELECT * FROM telephone WHERE master_id=$id";
$result = mysql_query($sql);
$while (myrow = mysql_fetch_array($result)) {
?>
<tr>
<td>
    <P>Telephone Number:<br>
    <input type="text" name="tel_number[]" VALUE="<? echo $myrow['tel_number'] ?>" size=30 maxlength=25>
    <input type="radio" name="tel_type[]" value="home" checked> home
    <input type="radio" name="tel_type[]" VALUE="work"> work
    <input type="radio" name="tel_type[]" VALUE="other"> other

<br /><br />
</td>
</tr>
<?
} // while phone
?> 

EDIT: It's important to note that your submit-handling code will be considerably more complicated because you have multiple phone numbers per user.

and as for the enum data types. i'm not sure exactly what you mean. database fields that are of type 'enum' are only permitted to take on one of the values defined in your database schema. if your db schema says an enum field can be 'foo', 'bar', or 'wow' then it will end up as empty or null if you try to stick in 'los angeles'. in order to change the options, you will have to change the definition of the field in your database schema.

[David_P]

I'm getting this error:

Parse error: parse error, unexpected '=' in...

on this line:

$while (myrow = mysql_fetch_array($result)) {

[sneakyimp]

forgot the dollar sign before 'myrow':

 $while ($myrow = mysql_fetch_array($result)) { 

[David_P]

Right - dollar sign - gotit. That fixed that problem, but there were others so I decided to just abandon this particular address book.

This address book used relational tables. The tables that made up this address book included:

• name (master)
• address
• telephone
• fax
• email
• note

It worked fine. You could - create a record, view a record, add to a record or delete a record, but you could not edit a record without physically going into MySQL and manually altering the entry. It screamed for an edit.php script, which I tried to create, but it proved too problematic due to it involving relational tables.

I like the simplicity of this address book and the clean layout, so I decided to make an address book based on this one. First thing was "Keep it Simple", which meant using just one table. I have it up and running and everthing works fine, except...

But this little problem is on an added feature that I could do without.

I have a regular edit page that displays all the entries and you click on the one you want to edit. It populates the fields, you make your changes, click Submit and BAM - it's done.

The added feature is when you're viewing an entry it has on the bottom the option of "edit this entry", which is linked to a different edit page (big code below).

It doesn't make the changes and when I hit Submit - the page reloads with the fields blank and with this error message:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in... on line 27

and this is line 27:

$myrow = mysql_fetch_array($result); 

Like I said, this is just an added feature that I would like to have if it's an easy fix. Thanks.

David P.

The big code:

<?php
$conn = mysql_connect("localhost","username","password");
mysql_select_db("db",$conn) or die( "Unable to select database"); 

$id = $_GET["id"]; 
$sql = "SELECT * FROM my_addresses WHERE id=$id"; 
$result = mysql_query($sql); 
$myrow = mysql_fetch_array($result); 

?>

<h3 align="center">Edit Record for <span style="color:red"><u><?php echo $myrow['first'] ?>&nbsp;<?php echo $myrow['last'] ?></u></span></h3>
<br />

<center>
<table width="95%" cellspacing=0 cellpadding=3 border=0>
<tr>
<td>
<form method="post" action="<?php echo $PHP_SELF?>" >

<input type=hidden name="id" value="<?php echo $myrow['id'] ?>"> 


Last Name:<br />
<input type="text" size="35" name="last" VALUE="<?php echo $myrow['last'] ?>">

<br />
<br />

First Name:<br />
<input type="text" size="35" name="first" VALUE="<?php echo $myrow['first'] ?>">

<br />
<br />

Street Address:<br />
<input type="text" size="45" name="address" VALUE="<?php echo $myrow['address'] ?>">

<br />
<br />

City / State / Zipcode:<br />
<input type="text" size="35" name="city" VALUE="<?php echo $myrow['city'] ?>">&nbsp;&nbsp;

<input type="text" size="2" name="state" VALUE="<?php echo $myrow['state'] ?>">&nbsp;&nbsp;

<input type="text" size="10" name="zipcode" VALUE="<?php echo $myrow['zipcode'] ?>">

<br />
<br />

Home Phone:<br />
<input type="text" size="35" name="home_phone" VALUE="<?php echo $myrow['home_phone'] ?>">

<br />
<br />

Work Phone:<br />
<input type="text" size="35" name="work_phone"  VALUE="<?php echo $myrow['work_phone'] ?>">

<br />
<br />

Cell Phone:<br />
<textarea name="cell_phone" cols=45 rows=5 wrap=virtual><?php echo $myrow['cell_phone'] ?></textarea>

<br />
<br />

Email:<br />
<input type="text" size="65" name="email" VALUE="<?php echo $myrow['email'] ?>">

<br />
<br />

Personal Notes:<br />
<textarea name="notes" cols=45 rows=5 wrap=virtual><?php echo $myrow['notes'] ?></textarea>

<br />
<br />

<input type="hidden" name="cmd" value="edit"> 
<input type="submit" name="submit" value="Submit"> 
</form> 
</td>
</tr>
</table>
</center>


<?php
if ($_POST['$submit']) 
{ 

$id = $_POST['id'];
$last = $_POST['last'];
$first = $_POST['first'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$home_phone = $_POST['home_phone'];
$work_phone = $_POST['work_phone'];
$cell_phone = $_POST['cell_phone'];
$email = $_POST['email'];
$notes = $_POST['notes'];

$sql = "UPDATE my_addresses SET last='$last', first='$first', address='$address', city='$city', state='$state', zipcode='$zipcode', home_phone='$home_phone', work_phone='$work_phone', cell_phone='$cell_phone', email='$email', notes='$notes' where id = $id";

$result = mysql_query($sql); 
echo "

<br />
<br />

<p align=\"center\"><span style=\"color:red\">Information updated.</span></p>

<br />
<br />

";
}

?>