My Code says it is updating, but it is not
Posted: Wed Jul 22, 2009 11:42 pm
Should be simple, but I am shuffling things around and not getting it.
Very small table shown below. php code is attached.
mysql> DESCRIBE pet;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| petID | int(10) unsigned | | PRI | NULL | auto_increment |
| petType | varchar(255) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
php Code follows:
<?php
// Set the page title and include the HTML header and any db scripts.
$page_title = 'pet - delete';
include ('./head.inc');
require_once('db_config_inc.php');
?>
<table width="90%" border="0" cellspacing="2" cellpadding="4" align="center" class="wrap">
<tr bgcolor="#333333">
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td bgcolor="#FFFFFF"> ! </td>
<td width="100%"> <font color="#CCCCCC"> <b>Update Pet List</b></font></td>
</tr>
</table></td>
</tr>
</table>
<table width="90%" border="0" cellspacing="4" cellpadding="4" align="center">
<tr>
<td width="70%" valign="top"> <p><b>Update A DB Record</b></p>
<hr />
<p>
<?php
#######################################################
# This starts off with the showing what will happen if the $_POST variable named Submit is true, thus showing
# that the form has been submitted successfully and then deleting records.
#
# It must be said, there are several ways to skin this cat when it comes to deleting records. You can show a form with an input box
# and delete based off the name a user submits. You could do it off of checkboxes, as I've done here. It might be a personal preference
# as to what method you'd use in your project, but the checkbox is quite elegant, so I used that.
#
# The data I've choosen to ID our db records is the ID variable. We know that the ID variable will be a unquie determinate of
# a database record. You could use a presidents name, but it gets tricky when you're deleting George W and is dad George. If we
# name the checkbox the name of the db ID, then we know we're in good shape for a clean delete process based purely on numeric
# instead of the lesser string.
#
#
# right off the bat, i want to unset the submit variable the comes across from the $_POST superglobal because I don't want to
# use that data in my foreach loop, which will tackle all the dirty work for the db deletion for each db row. So with that
# var deleted, I can go ahead and perform a foreach loop for each item in the $_POST array, which contains all
# the names of the checkboxes that have been checked off for deleted.
#
#
# mysql_query("DELETE FROM presidents WHERE ID = $name") or
# die('Could not delete.');
#
# This handle deleting all the records that have been checked. Its a very simple SQL delete script. I would suggest try adding in a LIMIT
# set the mysql_query to a var and check to see if the query returned true. The key point in this DELETE script is
# that i'm deleting the row of the DB where the row ID is equal the the name of the checkbox.
# so if the checkbox named 00001 was checked to be deleted, then the DB ID row 00001 would be deleted. Not bad!
#######################################################
if(isset($_POST['Submit'])){
unset($_POST['Submit']);
foreach($_POST as $petType=>$value)
{
mysql_query("UPDATE pet SET petID = $petType Where petID = PetType") or die('Could not update.');
//mysql_query("UPDATE pet SET petType = $petType WHERE petID = PetType") or die('Could not update.');
echo 'record has been update<br>';
}// end of for each loop
// LETS SHOW THE RECORDS AGAIN TO CONFIRM DB RECORD HAS BEEN DELETED. SORT BASED ON ID DESENDING
// ON page 143 you can learn to sort based on a variety of options. Try sorting based on last name or first name or
// do it based on year!
$newquery = "SELECT petID, petType FROM pet ORDER BY petID DESC";
$newresult = @mysql_query ($newquery); // Run the query.
if ($newresult) { // If it ran OK, display the records.
echo '<table align="center" cellspacing="2" cellpadding="2"><tr><td align="left"><b>Pet</b></td></tr>';
// Fetch and print all the records.
//
// Note: what is returned by fetch_array is obviously an array! Reemeber, if you want to echo out
// data contained in a row, be sure to use PHP's array snytax. In this case I'm setting it to a variable
// called $row. I know that $row is an array. If I access each element of the array, I'll have the
// entire contents of that row. But to select just ONE element from the array, just use your array key.
// In this example, $row[2] will give me the startdate of the presdident based on my query above. Remember
// array's start at 0. So, $row[0] is actually the ID.
while ($row = mysql_fetch_array($newresult, MYSQL_NUM)) {
echo "<tr><td align=\"left\">$row[1]</td></tr>\n";
}
echo '</table>';
}
}else{ // ELSE STATEMENT FOR SUBMIT FORM
$query = "SELECT petID, petType FROM pet";
$result = @mysql_query ($query); // Run the query.
if ($result) {
// If it ran OK, display the records.
// Remember, we're creating a form to show the db record and handle the checkboxes.
//I'm going to escape out of PHP here cause trying to slash escape is cumbersome sometimes. It doesn't
// ever hurt to just escape out of PHP if you'd this that that it'd be quicker just in HTML. See
// and example of escaping using a slash in the while loop below.
?>
<form name="form1" form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<?php
echo '<table align="left" cellspacing="2" cellpadding="2">
<tr><td align="left"><b>Pet</b></td><td align="left"><b>Update</b></td></tr>';
// Fetch and print all the records.
//
// This is very much the same as above. I'm just not sorting and i'm going to add in a checkbox and make the name
// of that checkbox the ID of the row.
//
//
//
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
echo "<tr><td align=\"left\">$row[1]</td><td align=\"left\"><td align=\"left\"><input name=\"petType\" type=\"text\" value=\"\"></td></tr>\n";
}
echo '</table>';
// i'm creating a nother table to position the submit button. It would be good practice to let your CSS doc handle
// the form style and positioning. Something to think about for web-standards sake.
echo '<table align="center" cellspacing="2" cellpadding="2"><tr><td align="left">';
echo "<input type=\"submit\" name=\"Submit\" value=\"update records!\" class=\"butt\" alt=\"Sign up for the course! Well send you a link via email.\"/>";
echo '</td></tr></table>';
echo '</form>';
mysql_free_result ($result); // Free up the resources.
} else { // If it did not run OK.
echo '<p>The pet could not be displayed due to a system error. We apologize for any inconvenience.</p><p>' . mysql_error() . '</p>';
}
mysql_close(); // Close the database connection.
}//END OF SUBMIT IF \ ELSE CONDITIONAL
?>
</p>
</td>
</tr>
</table>
<?php
include ('./foot.inc'); // Include the HTML footer.
?>
Very small table shown below. php code is attached.
mysql> DESCRIBE pet;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| petID | int(10) unsigned | | PRI | NULL | auto_increment |
| petType | varchar(255) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
php Code follows:
<?php
// Set the page title and include the HTML header and any db scripts.
$page_title = 'pet - delete';
include ('./head.inc');
require_once('db_config_inc.php');
?>
<table width="90%" border="0" cellspacing="2" cellpadding="4" align="center" class="wrap">
<tr bgcolor="#333333">
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td bgcolor="#FFFFFF"> ! </td>
<td width="100%"> <font color="#CCCCCC"> <b>Update Pet List</b></font></td>
</tr>
</table></td>
</tr>
</table>
<table width="90%" border="0" cellspacing="4" cellpadding="4" align="center">
<tr>
<td width="70%" valign="top"> <p><b>Update A DB Record</b></p>
<hr />
<p>
<?php
#######################################################
# This starts off with the showing what will happen if the $_POST variable named Submit is true, thus showing
# that the form has been submitted successfully and then deleting records.
#
# It must be said, there are several ways to skin this cat when it comes to deleting records. You can show a form with an input box
# and delete based off the name a user submits. You could do it off of checkboxes, as I've done here. It might be a personal preference
# as to what method you'd use in your project, but the checkbox is quite elegant, so I used that.
#
# The data I've choosen to ID our db records is the ID variable. We know that the ID variable will be a unquie determinate of
# a database record. You could use a presidents name, but it gets tricky when you're deleting George W and is dad George. If we
# name the checkbox the name of the db ID, then we know we're in good shape for a clean delete process based purely on numeric
# instead of the lesser string.
#
#
# right off the bat, i want to unset the submit variable the comes across from the $_POST superglobal because I don't want to
# use that data in my foreach loop, which will tackle all the dirty work for the db deletion for each db row. So with that
# var deleted, I can go ahead and perform a foreach loop for each item in the $_POST array, which contains all
# the names of the checkboxes that have been checked off for deleted.
#
#
# mysql_query("DELETE FROM presidents WHERE ID = $name") or
# die('Could not delete.');
#
# This handle deleting all the records that have been checked. Its a very simple SQL delete script. I would suggest try adding in a LIMIT
# set the mysql_query to a var and check to see if the query returned true. The key point in this DELETE script is
# that i'm deleting the row of the DB where the row ID is equal the the name of the checkbox.
# so if the checkbox named 00001 was checked to be deleted, then the DB ID row 00001 would be deleted. Not bad!
#######################################################
if(isset($_POST['Submit'])){
unset($_POST['Submit']);
foreach($_POST as $petType=>$value)
{
mysql_query("UPDATE pet SET petID = $petType Where petID = PetType") or die('Could not update.');
//mysql_query("UPDATE pet SET petType = $petType WHERE petID = PetType") or die('Could not update.');
echo 'record has been update<br>';
}// end of for each loop
// LETS SHOW THE RECORDS AGAIN TO CONFIRM DB RECORD HAS BEEN DELETED. SORT BASED ON ID DESENDING
// ON page 143 you can learn to sort based on a variety of options. Try sorting based on last name or first name or
// do it based on year!
$newquery = "SELECT petID, petType FROM pet ORDER BY petID DESC";
$newresult = @mysql_query ($newquery); // Run the query.
if ($newresult) { // If it ran OK, display the records.
echo '<table align="center" cellspacing="2" cellpadding="2"><tr><td align="left"><b>Pet</b></td></tr>';
// Fetch and print all the records.
//
// Note: what is returned by fetch_array is obviously an array! Reemeber, if you want to echo out
// data contained in a row, be sure to use PHP's array snytax. In this case I'm setting it to a variable
// called $row. I know that $row is an array. If I access each element of the array, I'll have the
// entire contents of that row. But to select just ONE element from the array, just use your array key.
// In this example, $row[2] will give me the startdate of the presdident based on my query above. Remember
// array's start at 0. So, $row[0] is actually the ID.
while ($row = mysql_fetch_array($newresult, MYSQL_NUM)) {
echo "<tr><td align=\"left\">$row[1]</td></tr>\n";
}
echo '</table>';
}
}else{ // ELSE STATEMENT FOR SUBMIT FORM
$query = "SELECT petID, petType FROM pet";
$result = @mysql_query ($query); // Run the query.
if ($result) {
// If it ran OK, display the records.
// Remember, we're creating a form to show the db record and handle the checkboxes.
//I'm going to escape out of PHP here cause trying to slash escape is cumbersome sometimes. It doesn't
// ever hurt to just escape out of PHP if you'd this that that it'd be quicker just in HTML. See
// and example of escaping using a slash in the while loop below.
?>
<form name="form1" form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<?php
echo '<table align="left" cellspacing="2" cellpadding="2">
<tr><td align="left"><b>Pet</b></td><td align="left"><b>Update</b></td></tr>';
// Fetch and print all the records.
//
// This is very much the same as above. I'm just not sorting and i'm going to add in a checkbox and make the name
// of that checkbox the ID of the row.
//
//
//
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
echo "<tr><td align=\"left\">$row[1]</td><td align=\"left\"><td align=\"left\"><input name=\"petType\" type=\"text\" value=\"\"></td></tr>\n";
}
echo '</table>';
// i'm creating a nother table to position the submit button. It would be good practice to let your CSS doc handle
// the form style and positioning. Something to think about for web-standards sake.
echo '<table align="center" cellspacing="2" cellpadding="2"><tr><td align="left">';
echo "<input type=\"submit\" name=\"Submit\" value=\"update records!\" class=\"butt\" alt=\"Sign up for the course! Well send you a link via email.\"/>";
echo '</td></tr></table>';
echo '</form>';
mysql_free_result ($result); // Free up the resources.
} else { // If it did not run OK.
echo '<p>The pet could not be displayed due to a system error. We apologize for any inconvenience.</p><p>' . mysql_error() . '</p>';
}
mysql_close(); // Close the database connection.
}//END OF SUBMIT IF \ ELSE CONDITIONAL
?>
</p>
</td>
</tr>
</table>
<?php
include ('./foot.inc'); // Include the HTML footer.
?>