Chap.10 delete err in edit_power.php

Binayak · November 14th, 2010, 05:20 AM

edit_power.php runs well while browsing but ,When I select powers and try to delete power using ''Delete Selected Powers" button . the page is directed to 'http://localhost/char_transaction.php'. And there I get this message "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on)' at line 3".Nothing is deleted.
I use XAMPP version:1.7.3.
Plz Help Me.

Here is the code for
char_transaction.php

Code:

<?php
require '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);
?>

Code for
edit_power.php

Code:

<html>
<head>
<title>Edit Powers</title>
  <style type="text/css">
   td { vertical-align: top; }
  </style>
 </head>
 <body>
  <img src="logo.jpg" alt="Comic Book Appreciation Site" style="float: left;" />
  <h1>Comic Book<br/>Appreciation</h1>
  <h2>Edit Character Powers</h2>
  <hr style="clear: both;"/>
  <form action="char_transaction.php" method="post">
   <div>
    <input type="text" name="new_power" size="20" maxlength="40" value="" />
    <input type="submit" name="action" value="Add New Power" />
   </div>
<?php
require '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));
$query = 'SELECT power_id, power FROM comic_power ORDER BY power ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));
if (mysql_num_rows($result) > 0) {
    echo '<p><em>Deleting a power will remove its association with any ' .
         'characters as well-- select wisely!</em></p>';
    $num_powers = mysql_num_rows($result);
    $threshold = 5;
    $max_columns = 2;

    $num_columns = min($max_columns, ceil($num_powers/$threshold));
    $count_per_column = ceil($num_powers/$num_columns);

    $i = 0;
    echo '<table><tr><td>';
    while ($row = mysql_fetch_assoc($result)) {
        if (($i > 0) && ($i % $count_per_column == 0)) {
            echo '</td><td>';
        }
        echo '<input type="checkbox" name="powers[]" "value="' .
            $row['power_id'] . '" /> ';
        echo $row['power'] . '<br/>';
        $i++;
    }
    echo '</td></tr></table>';

    echo '<br/><input type="submit" name="action" ' .
        'value="Delete Selected Powers" />';
} else {
    echo '<p><strong>No Powers entered...</strong></p>';
}
?>
   </div>
  </form>
  <p><a href="list_characters.php">Return to Home Page</a></p>
 </body>
</html>

rahul1bamotra · July 26th, 2011, 02:21 AM

hello, plz pzl help me i am also facing same problem in char_transaction.php
file..

July 26th, 2011, 05:29 AM

Hi lol,
it use to be like that cuz of the version of the apache and mysql you using lol.
just wait for some days i it will create the table for you...
I also face that some time cuz i use wampserver.

rahul1bamotra · July 26th, 2011, 05:33 AM

to create table copy this sql code:-

<?php
require '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));

// create the comic_character table
$query = 'CREATE TABLE IF NOT EXISTS comic_character (
character_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
alias VARCHAR(40) NOT NULL DEFAULT "",
real_name VARCHAR(80) NOT NULL DEFAULT "",
lair_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
alignment ENUM("good", "evil") NOT NULL DEFAULT "good",

PRIMARY KEY (character_id)
)
ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the comic_power table
$query = 'CREATE TABLE IF NOT EXISTS comic_power (
power_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
power VARCHAR(40) NOT NULL DEFAULT "",

PRIMARY KEY (power_id)
)
ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the comic_character_power linking table
$query = 'CREATE TABLE IF NOT EXISTS comic_character_power (
character_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
power_id INTEGER UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (character_id, power_id)
)
ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the comic_lair table
$query = 'CREATE TABLE IF NOT EXISTS comic_lair (
lair_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
zipcode_id CHAR(5) NOT NULL DEFAULT "00000",
address VARCHAR(40) NOT NULL DEFAULT "",

PRIMARY KEY (lair_id)
)
ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the comic_zipcode table
$query = 'CREATE TABLE IF NOT EXISTS comic_zipcode (
zipcode_id CHAR(5) NOT NULL DEFAULT "00000",
city VARCHAR(40) NOT NULL DEFAULT "",
state CHAR(2) NOT NULL DEFAULT "",

PRIMARY KEY (zipcode_id)
)
ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the comic_rivalry table
$query = 'CREATE TABLE IF NOT EXISTS comic_rivalry (
hero_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
villain_id INTEGER UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (hero_id, villain_id)
)
ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

echo 'Done.';
?>

TedBronson · October 11th, 2011, 03:44 PM

The problem is an error in the book.

In the edit_powers.php file, during the section for deleting powers, the book has the line:

Code:

echo '<input type="checkbox" name="powers[]" "value="' . $row['power_id'] . '" /> ';

It should be:

Code:

echo '<input type="checkbox" name="powers[]" value="' .$row['power_id'] . '" /> ';

The extra double-quote causes it to send the word "on" instead of the power_id.

thg · January 31st, 2014, 01:59 PM

Thanks for the catch. It would be great if the error found its way to the errata section.

Naeem Ahmed Khan · January 23rd, 2015, 04:21 PM

Quote:

Originally Posted by TedBronson

The problem is an error in the book.

In the edit_powers.php file, during the section for deleting powers, the book has the line:

Code:

echo '<input type="checkbox" name="powers[]" "value="' . $row['power_id'] . '" /> ';

It should be:

Code:

echo '<input type="checkbox" name="powers[]" value="' .$row['power_id'] . '" /> ';

The extra double-quote causes it to send the word "on" instead of the power_id.

Excellent Work.... I was getting annoyed and disheartened until I found you... Thanks

somashekar · March 16th, 2016, 12:52 PM

Hi..Guys i am using wrox php6 mysql n xampp..to get a quick hands on the AMP package..I was held up in in chapter 10..due to this issue..Thanks Guys..will be back with productive input here.