WPII -PHP - Intro

9 - PHP:MySQL - Delete data

We continue building on our code. So far we can insert new records into our table. The next step is to make it so we can delete a record from the table. For this function we will build a total of three pages. One will be the page we've been working on all along, the second will be a page that checks to make sure we want to delete the record, and the third will actually delete the record and send the user back to the first page.

Here's what we're working towards.

copy from just below this line....
<?
///This is the code to get the information from the table so it can be displayed
// You need to do all your database connection work before the html starts
// So first, we define the connection string
//with this syntax: mysql_connect($host, $username, $password)
//This gets us talking to mysql on the server
$conn = mysql_connect("localhost", "username","password") or die("Could not connect to database");

//Here we select the database we want to use with $theConnectionString
mysql_select_db("databaseName") or die("Could not select database");

//Define the SQL statement that gets sent to the database
$query = "SELECT * FROM userInfo";

//Put the results of your SQL statement into $aVariable, in this case $result.
//Note that the variables used in this statement, $query and $conn, are what
//we defined in the previous few steps.
$result = mysql_query($query, $conn);

//You can get the number of returned records this way
$num_rows = mysql_num_rows($result);

//Planning ahead here. When I tell how many record(s) were retrieved, I want proper grammar.
if ($num_rows > 1 || $num_rows == 0)
{
$recordWord = "records";
}
else
{
$recordWord = "record";
}

//Close your connection to the database once you've gotten the information you need
mysql_close($conn);

// From here down within the head we have new code for this exercise

// This is the code to insert a new record via the form
if ($HTTP_SERVER_VARS['QUERY_STRING']<> '') {

//Define the connection string
$conn = mysql_connect("localhost", "username","password") or die("Could not connect to database");

//Connect to "the database" with $theConnectionString
mysql_select_db("databaseName") or die("Could not select database");

//The values from the form get built into the query
$query = "INSERT INTO userInfo VALUES ('','$firstName','$lastName')";

}
?>

<head>
<title>PHP/MySQL</title>
</head>

<body>
<p>Here's some static text, just so we know the page is working.
</p>
<p>

<?
// This shows the records we retrieved in the PHP code in the head of the page.
//If the $result variable has any contents
if ($result){

//Notice the dot used for concatenation, and how you can mix
//variables and literal text strings
echo " You retrieved " . $num_rows . " " . $recordWord . ".";

// Now we let PHP start writing the html for our table
echo "<table border=1>";

// $row gets an array stuffed into it. Each record in our recordset is a row in the array.
// The while statement iterates its way through $row. Each time
// through, if there's another element in the array,
// it executes the code in the function block and uses the values in row[iteration number]
// of the array.
while ($row = mysql_fetch_array($result)) {

// The elements of the array are automatically named for the column they come from in the table
$firstName = $row['firstName'];
$lastName = $row['lastName'];
$id = $row['id'];

// Cycle color for each iteration.
//Note the shorthand if-then notation
$rowColor == "#ffdddd" ? $rowColor = "#ddffdd": $rowColor = "#ffdddd";

//--v Here's the new code for this exercise
// Instead of just writing a page that displays the information in the table,
// we're also going to add a link for each record to a page that will delete the record.
// This is the only change we'll make to the table for this exercise.

//Write the html to put the data of each record in its own table row.
echo "<tr bgcolor = '" . $rowColor . "'>\n<td width='300px'>";

//Write the variables into the table cell
echo $id . " " . $firstName . " " . $lastName ;

// This is the line that adds a link to the delete page
// Notice how the $id variable is concatenated with the URL to the preDelete page with a question mark.
// That builds a query string into the URL and passes that information to the next page.
// Also notice, the page this link leads to is what I call a "preDelete" page. It's the page that asks for confirmation
// of your request before actually deleting the record.
echo "</td><td width='80px' align='middle'><a href='preDelete.php?id=" . $id . "'>delete</a></td>";

//--------------------------------------------------------------------------------------^

// Close the html table row
echo "</tr>\n</td width='30%'>";
}

//If we're done with the while loop, we're done with the table, so write the html to close the table.
echo "</table>";

}
else
{
//If no data was found in the database, give the user feedback explaining the situation.
echo " sorry no data found ";
}
?>

</p>





<form name="form1" id="form1" method="post" action="insert.php">
<table width="300" border="1" cellspacing="0" cellpadding="5">
<tr>
<td width="64"><div align="right">first name:</div>
</td>
<td width="210"><input name="firstName" type="text" id="firstName" /></td>
</tr>
<tr>
<td><div align="right">last name:</div>
</td>
<td><input name="lastName" type="text" id="lastName" /></td>
</tr>
<tr>
<td><div align="right"></div>
</td>
<td> </td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="Submit" value="submit" /></td>
</tr>
</table>
</form>
</body>
</html>
... to just above this line.

PreDelete

Table of Contents