Updating Records

Workshop Requirements

Before beginning this workshop ensure that you have the ability to upload PHP files to a PHP enabled webserver that has a MySQL database server installed. See Setting Up a Server for details on how to install one if you don't already have access.

You should also have completed the following Virtual Workshops:

You should also have some experience with HTML programming.

A little bit of planning

So far we have been looking at the basic techniques of PHP and database integration without really worrying about where it is taking us. However in the last workshop we began to look at combining functions within the same script. To have a fully functioning database application we will need to add more functions (which wont be that hard) but at this stage we should identify them. We will need to be able to:

We will also need to connect to the database for each function/task , so rather than than repeating the code for making that connection in each function we will create a connection function that will assign the details to the $link variable which can be called from within the other variables. This will also allow us to add another little nicety which will make searching for an artist easier.

Reorganising the code

Before adding any more functions related to the different data manipulation tasks we should create the connection function and adjust the existing functions (html_form and select_artist) to use our new function.

First remove the connection code from the select_artist function and place that in out new function called my_conn. We also want to add a line 'return $link' as that will make that value available to whichever function uses (or calls) the connection function.


function my_conn() { 

/* set's the variables for MySQL connection */ 

$server = "localhost:3306"; // this is the server address and port 
$username = "vworksusr"; // change this to your username 
$password = "vworkspass"; // change this to your password 

/* Connects to the MySQL server */ 

$link = @mysql_connect ($server, $username, $password) or die (mysql_error()); 

/* Defines the Active Database for the Connection */ 

if (!@mysql_select_db("cdlibrary", $link)) { 
     echo "<p>There has been an error. This is the error message:</p>";
     echo "<p><strong>" . mysql_error() . "</strong></p>";
     echo "Please Contact Your Systems Administrator with the details";
}

return $link;

}

We next need to replace the code in the select_artist function with the single line that will call the function and assign the resulting connection reference (the returned $link variable) to a new variable called $conn.


function select_cd() {
?>
<h4>Cds Bought</h4> 
<?
$conn = my_conn();

The value of $conn is the same as $link in our old example and so we also need to change the other references to $link within the select_artist function:


$result = mysql_query($sql, $conn); 

...and...


mysql_close ($conn); 

Save the file, upload it and check that everything still works. Assuming that we haven't broken everything it is time to add that nicety. The html_form function simply displays a blank form ready for user input. We could provide as an alternative a dropdown menu that will display all the available artists.

E.g.:

In order to achieve this will use our connection function, create a different SQL statement and use a while loop to create each option in a select form tag within our html_form function. Thus change the html_form function to read:


function html_form() {

/* Calls Connection Function */

$conn = my_conn();

/* Defines query */

$sql = "SELECT DISTINCT cd_releases.Artist FROM cd_releases;";

/* Passes query to database */ 

$result = mysql_query($sql, $conn);
if (!$result) {
    echo("<p>Error performing query: " . mysql_error() . "</p>");
    exit();
}

/* Outputs HTML form */
?>

<p>Please enter the search term for the artist</p>
<form name="cds" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
Name of Artist: <select name="artist">
<? 
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo("<option value=\"" . $row["Artist"] . "\">" . $row["Artist"] . "</option>\n");
}
?>
</select>
<input type="submit" name="Submit" value="ViewCds" />
</form>

<?
/* Closes Connection to the MySQL server */

mysql_close ($conn);



A few things to note about this code:

We are now ready to start adding our new functions.

Editing existing data

As noted above updating a record will require two tasks - one to retrieve the record and place the values into a HTML form and another to process any changes made to data by the user. We will also need to add a couple of changes to the if control structure at the very beginning of our script that determines which function we want to use and also we need to add another column to our outputted 'artist' results in the function select_artist. This will allow the user to choose which record to edit.

Currently if the $_REQUEST['Submit'] array member exists, it doesn't matter what it's value is, the script performs a search for cds by the artist. We can change this so that if the array member exists AND its value determines the function to run. We know that the value of the first submit button is 'ViewCds' which means that we can adjust our code again to test for submit and the value 'ViewCds'.


if (!$_REQUEST['Submit']) {
     html_form();
} elseif ($_REQUEST['Submit'] == "ViewCds") {
     select_cd(); 
}  

Note: A single equals-sign is used to assign a variable, to check for the matching variable we need to use a double equals-sign.

So let's add two functions to our if control statement. If 'Submit' has a value of 'Edit' this will trigger our get_data() function whereas if 'Submit' has a value of 'Update' this will update the database via the update_cd function.


if (!$_REQUEST['Submit']) {
     html_form();
} elseif ($_REQUEST['Submit'] == "ViewCds") {
     select_cd();
} elseif ($_REQUEST['Submit'] == "Edit") {
     get_data(); 
} elseif ($_REQUEST['Submit'] == "Update") {
     update_cd(); 
}  

To add an additional column we will need to edit the select_artist artist function so that the output is similar to this with an edit link:

Artist Title Year Label No of Tracks  
Christina Aguilera Stripped 2002 RCA 20 Edit

Thus make the following changes:-

Add a blank column in the first row of the table:


<td><strong>No of Tracks</strong></td>
<td></td>
</tr>

Next change the while control loop to read:


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo("<tr>\n<td>" . $row["Artist"] . "</td>");
    echo("<td>" . $row["Title"] . "</td>");
    echo("<td>" . $row["Year"] . "</td>");
    echo("<td>" . $row["Label"] . "</td>");
    echo("<td>" . $row["No_of_tracks"] . "</td>");
    echo("<td><a href=\"" . $_SERVER['PHP_SELF'] . "?releaseID=" .$row['releaseID'] . "&Submit=Edit\">Edit</a></td></tr>\n\n");
}

The new final column creates a URL link of 'cds.php?releaseid=number&Submit=Edit' this allows us to pass variables via the URL to the script as we saw in part 2. We are using the releaseID as an identifier because we don't need to know the copy number to edit the actual cd details. Also note that as there are double-quotes used in the echo string we need to 'escape' these (using a preceding backslash \") so that they are not included as part of the PHP code, but as outputted HTML. The final stage is to create a small get_data function so that we can test that everything is OK before going any further.


function get_data() { 
    echo "You wanna edit cd no. " . $_REQUEST['releaseID']; 
}

Save the file, upload it and check that you get a message saying 'You wanna edit CD no. number' when you click edit next to a CD

Using the PRINT function

If we look at the echo function used to print out out edit column above you can see that it is becoming a bit complicated to join the variables and the outputted HTML, it is also a bit annoying to have to have an echo statement on every line. So from now on we are going to use the print function that allows us to include variables and normal text together AND print multiple lines.

Returning to our get_data() function we what to run an SQL query which retrieves the data based on the releaseID and then display that data in the form fields, with the option to update the record. The code that we are creating in this function is purely variations on what has gone before and so annotations throughout will offer explanations rather than re-explaining everything.

Note: With the print function the $row['name'] array members do not need the single quotes so put $row[name]:


function get_data() {    
/* Calls our connection function */

$conn = my_conn(); 

/* Defines query */

$sql = "SELECT * FROM cd_releases WHERE (cd_releases.releaseID = " . $_REQUEST['releaseID'] . ")"; 

/* Passes query to database */

$result = mysql_query($sql, $conn);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();


/* creates our row array with an if statement to report errors */

if ($row = @mysql_fetch_array($result, MYSQL_ASSOC)) { 

/* prints out the artist and title */

print "<h4>$row[Artist] - $row[Title]</h4>";

/* prints out our HTML form '\"' */

print "<form name=\"CDs\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">";

/* Prints out hidden releaseID - we don't put this in the HTML form
so that the uer cannot edit the Key value in error */

print "<input type=\"text\" name=\"releaseID\" value=\"$row[releaseID]\">";

/* prints out our HTML table and fields 'escaping' any double quotes '\"' */ 
 
print "<table width=\"600\"> 

<tr>
<td width=\"150\"><strong>Artist</strong></td>
<td width=\"350\"><input type=\"hidden\" name=\"artist\" value=\"$row[Artist]\"></td>
<td rowspan=\"5\" valign=\"top\"><input type=\"submit\" name=\"Submit\" value=\"Update\">
</td>
</tr> 

<tr>
<td width=\"150\"><strong>Title</strong></td> 
<td width=\"350\"><input type=\"text\" name=\"title\" value=\"$row[Title]\"></td> 
</tr> 
<tr> 
<td width=\"150\"><strong>Year</strong></td> 
<td width=\"350\"><input type=\"text\" name=\"year\" value=\"$row[Year]\"></td> 
</tr> 
<tr> 
<td width=\"150\"><strong>Label</strong></td> 
<td width=\"350\"><input type=\"text\" name=\"label\" value=\"$row[Label]\"></td> 
</tr> 
<tr> 
<td width=\"150\"><strong>Tracks</strong></td> 
<td width=\"350\"><input type=\"text\" name=\"tracks\" value=\"$row[No_of_tracks]\"></td> 
</tr> 
</table> 
</form>"; 

} else { 
    echo("There has been an error" . mysql_error()); 


/* closes connection */ 

mysql_close ($conn); 



Once more - save, upload and debug the script until you have it working.

Updating the Record

Now that the user has some fields to edit we need to add our update_cd function that will translate user changes to the field into changes in the database. To achieve this we will use an SQL update statement. We could do something complicated like retrieving the record then comparing each field to see if it has changed and if so update that field, but this is overkill. Following a chain of logic we are retrieving all the correct data from the database, thus inserting all the fields using just one statement (whether the user has modified the fields or not) will ensure that the correct data IS in the database.

Note: Once we process the query we simply call the get_data() function to display the updated values.


function update_cd() {

/* Calls our connection function */

$conn = my_conn();

/* Defines query */

$sql_update = "UPDATE cd_releases SET ";
$sql_update .= "cd_releases.Artist = '" . $_REQUEST['artist'] . "', ";
$sql_update .= "cd_releases.Title = '" . $_REQUEST['title'] . "', ";
$sql_update .= "cd_releases.Year = '" . $_REQUEST['year'] . "', ";
$sql_update .= "cd_releases.Label = '" . $_REQUEST['label'] . "', ";
$sql_update .= "cd_releases.No_of_tracks = '" . $_REQUEST['tracks'] . "' ";
$sql_update .= "WHERE (cd_releases.releaseID = " . $_REQUEST['releaseID'] . ")";

/* Passes query to database */

$result = mysql_query($sql_update, $conn);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}

/* Prints succes message */

print "<p> Successfully Updated</p>";

/* closes connection */

mysql_close ($conn);

/* Calls get_data() function */

get_data();

}


That's the end of this workshop and if you are getting worried, don't. This has been the steep part of the learning curve part six (where we tidy up, adding new records, copies and deleting records) will be easier.

Full Code Listing

Rather than print it in this page you can get it here

In this section

Related Reading

Related Books

PHP and MySQL Web Development

Related Ads