Virtual Workshops

Part of keithjbrown.co.uk

Main Menu

• Server Environment
• MySQL
• PHP Databases
• Web Design
• Excel

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:

• PHP Web Databases - Part 1
• PHP Web Databases - Part 2
• PHP Web Databases - Part 3
• PHP Web Databases - Part 4
• The MySQL Series of 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:

• Edit existing cd data
  • Retrieve data ready to be edited
  • Insert updated values into the database
• Add new cd data
  • Display blank form fields for new data
  • Insert new data into the database
• Add a new Copy
  • Count Copies
  • Add Copy
• Delete copy
  • Get Available Copies
  • Delete Copy

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.: Britney Spears Pink Christina Aguilera Darius Will Young Robbie Williams

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:

• The SQL statement is 'SELECT DISTINCT cd_releases.artist FROM cd_releases' which only selects each artist once.
• The retrieved value of artist is used twice - once as the form value and once as the display text within an HTML form select element.

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

Lastest 10 Threads - view all

---

Post a Comment or Question

Name

Email

URL (optional)

Title of Comment

Comment
HTML Allowed:
a,code,strong,em

I would like to be e-mailed any response to my comment.

In this section

• Beginning PHP
• Passing data to PHP
• Database Connectivity
• Introducing Functions
• Updating Records
• A Whole Application
• Debugging

Related Reading

• PHP Manual

Related Books

Related Ads