giosal Posted October 26, 2010 Share Posted October 26, 2010 This script add and delete data to the database, it add it correctly but it's not able to delete it. I appreciate somebody more expert to me to help me. thank you. <?php require 'include/db.inc.php'; $db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or die ('Unable to connect. Check your connection parameters.'); mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db)); switch ($_POST['action']) { case 'Add Character': // escape incoming values to protect database $alias = mysql_real_escape_string($_POST['alias'], $db); $real_name = mysql_real_escape_string($_POST['real_name'], $db); $address = mysql_real_escape_string($_POST['address'], $db); $city = mysql_real_escape_string($_POST['city'], $db); $state = mysql_real_escape_string($_POST['state'], $db); $zipcode_id = mysql_real_escape_string($_POST['zipcode_id'], $db); $alignment = ($_POST['alignment'] == 'good') ? 'good' : 'evil'; // add character information into database tables $query = 'INSERT IGNORE INTO comic_zipcode (zipcode_id, city, state) VALUES ("' . $zipcode_id . '", "' . $city . '", "' . $state . '")'; mysql_query($query, $db) or die (mysql_error($db)); $query = 'INSERT INTO comic_lair (lair_id, zipcode_id, address) VALUES (NULL, "' . $zipcode_id . '", "' . $address . '")'; mysql_query($query, $db) or die (mysql_error($db)); // retrieve new lair_id generated by MySQL $lair_id = mysql_insert_id($db); $query = 'INSERT INTO comic_character (character_id, alias, real_name, lair_id, alignment) VALUES (NULL, "' . $alias . '", "' . $real_name . '", ' . $lair_id . ', "' . $alignment . '")'; mysql_query($query, $db) or die (mysql_error($db)); // retrieve new character_id generated by MySQL $character_id = mysql_insert_id($db); if (!empty($_POST['powers'])) { $values = array(); foreach ($_POST['powers'] as $power_id) { $values[] = sprintf('(%d, %d)', $character_id, $power_id); } $query = 'INSERT IGNORE INTO comic_character_power (character_id, power_id) VALUES ' . implode(',', $values); mysql_query($query, $db) or die (mysql_error($db)); } if (!empty($_POST['rivalries'])) { $values = array(); foreach ($_POST['rivalries'] as $rival_id) { $values[] = sprintf('(%d, %d)', $character_id, $rival_id); } // alignment will affect column order $columns = ($alignment = 'good') ? '(hero_id, villain_id)' : '(villain_id, hero_id)'; $query = 'INSERT IGNORE INTO comic_rivalry ' . $columns . ' VALUES ' . implode(',', $values); mysql_query($query, $db) or die (mysql_error($db)); } $redirect = 'list_characters.php'; break; case 'Delete Character': // make sure character_id is a number just to be safe $character_id = (int)$_POST['character_id']; // delete character information from tables $query = 'DELETE FROM c, l USING comic_character c, comic_lair l WHERE c.lair_id = l.lair_id AND c.character_id = ' . $character_id; mysql_query($query, $db) or die (mysql_error($db)); $query = 'DELETE FROM comic_character_power WHERE character_id = ' . $character_id; mysql_query($query, $db) or die (mysql_error($db)); $query = 'DELETE FROM comic_rivalry WHERE hero_id = ' . $character_id . ' OR villain_id = ' . $character_id; mysql_query($query, $db) or die (mysql_error($db)); $redirect = 'list_characters.php'; break; case 'Edit Character': // escape incoming values to protect database $character_id = (int)$_POST['character_id']; $alias = mysql_real_escape_string($_POST['alias'], $db); $real_name = mysql_real_escape_string($_POST['real_name'], $db); $address = mysql_real_escape_string($_POST['address'], $db); $city = mysql_real_escape_string($_POST['city'], $db); $state = mysql_real_escape_string($_POST['state'], $db); $zipcode_id = mysql_real_escape_string($_POST['zipcode_id'], $db); $alignment = ($_POST['alignment'] == 'good') ? 'good' : 'evil'; // update existing character information in tables $query = 'INSERT IGNORE INTO comic_zipcode (zipcode_id, city, state) VALUES ("' . $zipcode_id . '", "' . $city . '", "' . $state . '")'; mysql_query($query, $db) or die (mysql_error($db)); $query = 'UPDATE comic_lair l, comic_character c SET l.zipcode_id = ' . $zipcode_id . ', l.address = "' . $address . '", c.real_name = "' . $real_name . '", c.alias = "' . $alias . '", c.alignment = "' . $alignment . '" WHERE c.character_id = ' . $character_id . ' AND c.lair_id = l.lair_id'; mysql_query($query, $db) or die (mysql_error($db)); $query = 'DELETE FROM comic_character_power WHERE character_id = ' . $character_id; mysql_query($query, $db) or die (mysql_error($db)); if (!empty($_POST['powers'])) { $values = array(); foreach ($_POST['powers'] as $power_id) { $values[] = sprintf('(%d, %d)', $character_id, $power_id); } $query = 'INSERT IGNORE INTO comic_character_power (character_id, power_id) VALUES ' . implode(',', $values); mysql_query($query, $db) or die (mysql_error($db)); } $query = 'DELETE FROM comic_rivalry WHERE hero_id = ' . $character_id . ' OR villain_id = ' . $character_id; mysql_query($query, $db) or die (mysql_error($db)); if (!empty($_POST['rivalries'])) { $values = array(); foreach ($_POST['rivalries'] as $rival_id) { $values[] = sprintf('(%d, %d)', $character_id, $rival_id); } // alignment will affect column order $columns = ($alignment = 'good') ? '(hero_id, villain_id)' : '(villain_id, hero_id)'; $query = 'INSERT IGNORE INTO comic_rivalry ' . $columns . ' VALUES ' . implode(',', $values); mysql_query($query, $db) or die (mysql_error($db)); } $redirect = 'list_characters.php'; break; case 'Delete Selected Powers': if (!empty($_POST['powers'])) { // escape incoming values to protect database-- they should be numeric // values, but just to be safe $powers = implode(',', $_POST['powers']); $powers = mysql_real_escape_string($powers, $db); // delete powers $query = 'DELETE FROM comic_power WHERE power_id IN ("' . $powers . '")'; mysql_query($query, $db) or die (mysql_error($db)); $query = 'DELETE FROM comic_character_power WHERE power_id IN ("' . $powers . '")'; mysql_query($query, $db) or die (mysql_error($db)); } $redirect = 'edit_power.php'; break; case 'Add New Power': // trim and check power to prevent adding blank values $power = trim($_POST['new_power']); if ($power != '') { // escape incoming value $power = mysql_real_escape_string($power, $db); // create new power $query = 'INSERT IGNORE INTO comic_power (power_id, power) VALUES (NULL, "' . $power . '")'; mysql_query($query, $db) or die (mysql_error($db)); } $redirect = 'edit_power.php'; break; default: $redirect = 'list_characters.php'; } header('Location: ' . $redirect); ?> Quote Link to comment https://forums.phpfreaks.com/topic/216887-data-delete/ Share on other sites More sharing options...
Colton.Wagner Posted October 26, 2010 Share Posted October 26, 2010 First off please use code tags for future reference. I think the source of your problem is in the line: power_id IN ("' . $powers . '")'; I have never seen IN used as a part of the DELETE syntax. I found no documentation for it either. Basically you are not testing to see if anything is equal to the power_id column. I think you have just messed up your mysql syntax. Thanks, Colton Wagner Quote Link to comment https://forums.phpfreaks.com/topic/216887-data-delete/#findComment-1126731 Share on other sites More sharing options...
Pikachu2000 Posted October 26, 2010 Share Posted October 26, 2010 The DELETE . . . WHERE . . . IN() syntax will work, but it's intended to be used with more than one value rather than string together multiple ORs. DELETE FROM `table` WHERE `field` IN( 'val1', 'val2', 'val3' ) Quote Link to comment https://forums.phpfreaks.com/topic/216887-data-delete/#findComment-1126735 Share on other sites More sharing options...
sasa Posted October 26, 2010 Share Posted October 26, 2010 change $powers = implode(',', $_POST['powers']); to $powers = implode('", "', $_POST['powers']); Quote Link to comment https://forums.phpfreaks.com/topic/216887-data-delete/#findComment-1126737 Share on other sites More sharing options...
giosal Posted October 26, 2010 Author Share Posted October 26, 2010 Yes thank to your answer I solved. I must precise that I did not write the code, I downloaded the file from the editor and they did'nt give me support because my book it's already out of print. Yhank you Quote Link to comment https://forums.phpfreaks.com/topic/216887-data-delete/#findComment-1126775 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.