Skip navigation

Beginning PHP, Apache, MySQL Web Development

Chapter 9 Code - Building Databases

Try It Out 1

config.php - Try It Out 1

<?php

define('SQL_HOST','yourhost');
define('SQL_USER','joeuser');
define('SQL_PASS','yourpass');
define('SQL_DB','yourdatabase');

?>

make_table.php - Try It Out 1

<?php
require('config.php');

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());

mysql_select_db(SQL_DB,$conn);

$sql1 =
 "CREATE TABLE IF NOT EXISTS char_main (
  id int(11) NOT NULL auto_increment,
  alias varchar(40) NOT NULL default '',
  real_name varchar(80) NOT NULL default '',
  lair_id int(11) NOT NULL default 0,
  align enum('good','evil') NOT NULL default 'good',
  PRIMARY KEY (id)
 )";

$sql2 =
 "CREATE TABLE IF NOT EXISTS char_power (
  id int(11) NOT NULL auto_increment,
  power varchar(40) NOT NULL default '',
  PRIMARY KEY (id)
 )";

$sql3 =
 "CREATE TABLE IF NOT EXISTS char_power_link (
  char_id int(11) NOT NULL default 0,
  power_id int(11) NOT NULL default 0,
  PRIMARY KEY (char_id, power_id)
 )";

$sql4 =
 "CREATE TABLE IF NOT EXISTS char_lair (
  id int(11) NOT NULL auto_increment,
  zip_id varchar(10) NOT NULL default '00000',
  lair_addr varchar(40) NOT NULL default '',
  PRIMARY KEY (id)
 )";

$sql5 =
 "CREATE TABLE IF NOT EXISTS char_zipcode (
  id varchar(10) NOT NULL default '',
  city varchar(40) NOT NULL default '',
  state char(2) NOT NULL default '',
  PRIMARY KEY (id)
 )";

$sql6 =
 "CREATE TABLE IF NOT EXISTS char_good_bad_link (
  good_id int(11) NOT NULL default 0,
  bad_id int(11) NOT NULL default 0,
  PRIMARY KEY (good_id,bad_id)
 )";

mysql_query($sql1) or die(mysql_error());
mysql_query($sql2) or die(mysql_error());
mysql_query($sql3) or die(mysql_error());
mysql_query($sql4) or die(mysql_error());
mysql_query($sql5) or die(mysql_error());
mysql_query($sql6) or die(mysql_error());
echo "Done.";
?>

Try It Out 2

poweredit.php - Try It Out 2

<?php
require('config.php');

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
   or die('Could not connect to MySQL database. ' . mysql_error());
mysql_select_db(SQL_DB,$conn);

$sql = "SELECT id, power FROM char_power ORDER BY power";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
 while ($row = mysql_fetch_assoc($result)) {
  $pwrlist[$row['id']] = $row['power'];
 }
 $numpwr = count($pwrlist);
 $thresh = 5;
 $maxcols = 3;
 $cols = min($maxcols, (ceil(count($pwrlist)/$thresh)));
 $percol = ceil(count($pwrlist)/$cols);
 $powerchk = '';
 $i = 0;
 foreach ($pwrlist as $id => $pwr) {
  if (($i>0) && ($i%$percol == 0))
     $powerchk .= "</td>\n<td valign='top'>";
  $powerchk .= "<input type='checkbox' name='powers[]'
     value='$id'> $pwr<br />\n";
  $i++;
 }
 $delbutton = " <tr>
  <td colspan=\"$cols\" bgcolor=\"#CCCCFF\" align=\"center\">
   <input type=\"submit\" name=\"action\" value=\"Delete Powers\">
   <font size=\"2\" color=\"#990000\"><br /><br />
   deleting will remove all associated powers
   <br />from characters as well -- select wisely</font>
  </td>
 </tr>";
} else {
 $powerchk = "<div style=\"text-align:center;width:300;
 font-family:Tahoma,Verdana,Arial\">No Powers entered...</div>";
}

?>
<html>
<head>
<title>Add/Delete Powers</title>
</head>
<body>
<img src="CBA_Tiny.gif" align="left" hspace="10">
<h1>Comic Book<br />Appreciation</h1><br />
<h3>Editing Character Powers</h3>
<form action="char_transact.php" method="post" name="theform">
<table border="0" cellpadding="5">
 <tr bgcolor="#FFCCCC">
  <td valign="top"><?php echo $powerchk;?></td>
 </tr>
 <?php echo $delbutton; ?>
 <tr>
  <td colspan="<?php echo $cols;?>" bgcolor="#CCCCFF" align="center">
   <input type="text" name="newpower" value="" size=20>
   <input type="submit" name="action" value="Add Power">
  </td>
 </tr>
</table>
</form>
<a href="charlist.php">Return to Home Page</a>
</body>
</html>

charlist.php - Try It Out 2

<?php
require('config.php');

$ord = $_GET['o'];
if (is_numeric($ord)){
 $ord = round(min(max($ord, 1), 3));
} else {
 $ord = 1;
}
$order = array(
   1 => 'alias ASC',
   2 => 'name ASC',
   3 => 'align ASC, alias ASC'
);

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());
mysql_select_db(SQL_DB,$conn);

$sql = "SELECT c.id, p.power FROM char_main c JOIN char_power p JOIN
   char_power_link pk ON c.id = pk.char_id AND p.id = pk.power_id";

$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
 while ($row = mysql_fetch_assoc($result)) {
  $p[$row['id']][] = $row['power'];
 }
 foreach ($p as $key => $value) {
  $powers[$key] = implode(", ", $value);
 }
}

$sql = "SELECT c.id, n.alias FROM char_main c JOIN char_good_bad_link
   gb JOIN char_main n ON (c.id = gb.good_id AND n.id = gb.bad_id)
   OR (n.id = gb.good_id AND c.id = gb.bad_id)";

$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
 while ($row = mysql_fetch_assoc($result)) {
  $e[$row['id']][] = $row['alias'];
 }
 foreach ($e as $key => $value) {
  $enemies[$key] = implode(", ", $value);
 }
}
$table = "<table><tr><td align=\"center\">No characters currently
     exist.</td></tr></table>"
?>

 <html>
 <head>
 <title>Comic Book Appreciation</title>
 </head>
 <body>
 <img src='CBA_Tiny.gif' align='left' hspace='10'>
 <h1>Comic Book<br />Appreciation</h1><br />
 <h3>Character Database</h3>

<?php
$sql = "SELECT id, alias, real_name AS name, align
   FROM char_main ORDER BY ". $order[$ord];

$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
 $table = "<table border='0' cellpadding='5'>";
 $table .= "<tr bgcolor='#FFCCCC'><th>";
 $table .= "<a href='" . $_SERVER['PHP_SELF'] . "?o=1'>Alias</a>";
 $table .= "</th><th><a href='" . $_SERVER['PHP_SELF'] . "?o=2'>";
 $table .= "Name</a></th><th><a href='" . $_SERVER['PHP_SELF'];
 $table .= "?o=3'>Alignment</a></th><th>Powers</th>";
 $table .= "<th>Enemies</th></tr>";

 // build each table row
 while ($row = mysql_fetch_assoc($result)) {
  $bg = ($bg=='F2F2FF'?'E2E2F2':'F2F2FF');
  $pow = ($powers[$row['id']]==''?'none':$powers[$row['id']]);
  $ene = ($enemies[$row['id']]==''?'none':$enemies[$row['id']]);
  $table .= "<tr bgcolor='#" . $bg . "'><td><a href='charedit.php?c="
      . $row['id'] . "'>" . $row['alias']. "</a></td><td>"
      . $row['name'] . "</td><td align='center'>" . $row['align']
      . "</td><td>" . $pow . "</td><td align='center'>" . $ene
      . "</td></tr>";
 }

 $table .= "</table>";
 $table = str_replace('evil', '<font color="red">evil</font>', $table);
 $table = str_replace('good', '<font color="darkgreen">good</font>',
    $table);

}
echo $table;
?>
<br /><a href="charedit.php">New Character</a> &bull;
<a href="poweredit.php">Edit Powers</a>
</body>
</html>

charedit.php - Try It Out 2

<?php
require('config.php');

$char = $_GET['c'];
if ($char == '' || !is_numeric($char)) $char='0';
$subtype = "Create";
$subhead = "Please enter character data and click '$subtype
   Character.'";
$tablebg = '#EEEEFF';

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());
mysql_select_db(SQL_DB,$conn);

$sql = "SELECT id, power FROM char_power";
$result = mysql_query($sql);
if (mysql_num_rows($result) > 0) {
 While ($row = mysql_fetch_assoc($result)) {
  $pwrlist[$row['id']] = $row['power'];
 }
}

$sql = "SELECT id, alias FROM char_main WHERE id != $char";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
 $row = mysql_fetch_assoc($result);
 $charlist[$row['id']] = $row['alias'];
}

if ($char != '0') {
 $sql = "SELECT c.alias, c.real_name AS name, c.align, l.lair_addr
    AS address, z.city, z.state, z.id AS zip FROM char_main c,
    char_lair l, char_zipcode z WHERE z.id = l.zip_id AND
    c.lair_id = l.id AND c.id = $char";
 $result = mysql_query($sql) or die(mysql_error());
 $ch = mysql_fetch_assoc($result);

 if (is_array($ch)) {
  $subtype = "Update";
  $tablebg = '#EEFFEE';
  $subhead = "Edit data for <i>" . $ch['alias'] . "</i> and click
     '$subtype Character.'";

  $sql = "SELECT p.id FROM char_main c JOIN char_power p
     JOIN char_power_link pk ON c.id = pk.char_id
     AND p.id = pk.power_id WHERE c.id = $char";
  $result = mysql_query($sql) or die(mysql_error());
  if (mysql_num_rows($result) > 0) {
   While ($row = mysql_fetch_assoc($result)) {
    $powers[$row['id']] = 'selected';
   }
  }

  // get list of character's enemies
  $sql = "SELECT n.id FROM char_main c JOIN char_good_bad_link gb
     JOIN char_main n ON (c.id = gb.good_id AND n.id = gb.bad_id)
     OR (n.id = gb.good_id AND c.id = gb.bad_id) WHERE
     c.id = $char";
  $result = mysql_query($sql) or die(mysql_error());
  if (mysql_num_rows($result) > 0) {
   While ($row = mysql_fetch_assoc($result)) {
    $enemies[$row['id']] = 'selected';
   }
  }
 }
}
?>

<html>
<head>
<title>Character Editor</title>
</head>
<body>
<img src='CBA_Tiny.gif' align='left' hspace='10'>
<h1>Comic Book<br />Appreciation</h1><br />
<h3><?php echo $subhead;?></h3>

<form action='char_transact.php' name='theform' method='post'>
<table border='0' cellpadding='15' bgcolor='<?php echo $tablebg;?>'>
 <tr>
  <td>Character Name:</td>
  <td><input type='text' name='alias' size='41'
   value='<?php echo $ch['alias'];?>'
 >
  </td>
 </tr>
 <tr>
  <td>Real Name:</td>
  <td><input type='text' name='name' size='41'
   value='<?php echo $ch['name'];?>'
 >
  </td>
 </tr>
 <tr>
  <td>Powers:<br /><font size=2 color='#990000'>
   (Ctrl-click to<br />select multiple<br />powers)</font>
  </td>
  <td>
   <select multiple='multiple' name='powers[]' size='4'>
   <?php
    foreach ($pwrlist as $key => $value) {
     echo "<option value='$key' " . $powers[$key] .
        ">$value</option>\n";
    }
   ?>
   </select>
  </td>
 </tr>

 <tr>
  <td>Lair Location:<br /><font size=2 color='#990000'>
   (address,<br />city, state, zip)</font>
  </td>
  <td><input type='text' name='address' size='41'
   value='<?php echo $ch['address'];?>'
 ><br />
   <input type='text' name='city' value='<?php echo $ch['city'];?>'
 >
   <input type='text' name='state' size='2'
   value='<?php echo $ch['state'];?>' >
   <input type='text' name='zip' size='10'
   value='<?php echo $ch['zip'];?>' >
  </td>
 </tr>

 <tr>
  <td>Alignment:</td>

  <td><input type='radio' name='align' value='good'<?php
     echo($ch['align']=='good' ? ' checked="checked"' : '');?>>
   good<br />
   <input type='radio' name='align' value='evil'<?php
     echo($ch['align']=='evil' ? ' checked="checked"' : '');?>>
   evil
  </td>
 </tr>

<?php if (is_array($charlist)) { ?>
 <tr>
  <td>Enemies:<br /><font size=2 color='#990000'>
   (Ctrl-click to<br />select multiple<br />enemies)</font>
  </td>
  <td>
   <select multiple='multiple' name='enemies[]' size='4'>";
<?php
 foreach ($charlist as $key => $value) {
  echo "<option value='$key' ".$enemies[$key].">$value</option>\n";
 }
?>
   </select>
  </td>
 </tr>
<?php }?>
 <tr>
  <td colspan=2>
   <input type='submit' name='action'
   value='<?php echo $subtype;?> Character'>
   <input type="reset">
<?php if ($subtype == "Update"){?>
   &nbsp;&nbsp;&nbsp;&nbsp;
   <input type='submit' name='action' value='Delete Character'>
<?php }?>
  </td>
 </tr>
</table>
<input type='hidden' name='cid' value='<?php echo $char;?>'>
</form>
<a href='charlist.php'>Return to Home Page</a>
</body>
</html>

char_transact.php - Try It Out 2

<?php
require('config.php');
foreach($_POST as $key => $value) {
 $$key = $value;

}

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());
mysql_select_db(SQL_DB,$conn);

switch ($action) {
 case "Create Character":
  $sql = "INSERT IGNORE INTO char_zipcode (id, city, state)
   VALUES ('$zip', '$city', '$state')";
  $result = mysql_query($sql) or die(mysql_error());

  $sql = "INSERT INTO char_lair (id, zip_id, lair_addr)
   VALUES (NULL, '$zip', '$address')";
  $result = mysql_query($sql) or die(mysql_error());
  if ($result) $lairid = mysql_insert_id($conn);

  $sql = "INSERT INTO char_main (id, lair_id, alias, real_name, align)
   VALUES (NULL, '$lairid', '$alias', '$name', '$align')";
  $result = mysql_query($sql) or die(mysql_error());
  if ($result) $charid = mysql_insert_id($conn);

  if ($powers != "") {
   $val = "";
   foreach ($powers as $key => $id) {
    $val[] = "('$charid', '$id')";
   }
   $values = implode(',', $val);
   $sql = "INSERT IGNORE INTO char_power_link (char_id, power_id)
    VALUES $values";
   $result = mysql_query($sql) or die(mysql_error());
  }

  if ($enemies != '') {
   $val = "";
   foreach ($enemies as $key => $id) {
    $val[] = "('$charid', '$id')";
   }
   $values = implode(',', $val);
   if ($align = 'good') {
    $cols = '(good_id, bad_id)';
   } else {
    $cols = '(bad_id, good_id)';
   }
   $sql = "INSERT IGNORE INTO char_good_bad_link $cols
    VALUES $values";
   $result = mysql_query($sql) or die(mysql_error());
  }

  $redirect = 'charlist.php';
  break;

 case "Delete Character":
  $sql = "DELETE FROM char_main, char_lair USING char_main m,
   char_lair l WHERE m.lair_id = l.id AND m.id = $cid";
  $result = mysql_query($sql) or die(mysql_error());

  $sql = "DELETE FROM char_power_link WHERE char_id = $cid";
  $result = mysql_query($sql) or die(mysql_error());

  $sql = "DELETE FROM char_good_bad_link WHERE good_id = $cid
   OR bad_id = $cid";
  $result = mysql_query($sql) or die(mysql_error());

  $redirect = 'charlist.php';
  break;
 case "Update Character":
  $sql = "INSERT IGNORE INTO char_zipcode (id, city, state)
   VALUES ('$zip', '$city', '$state')";
  $result = mysql_query($sql) or die(mysql_error());

  $sql = "UPDATE char_lair l, char_main m SET l.zip_id='$zip',
   l.lair_addr='$address', alias='$alias', real_name='$name',
   align='$align' WHERE m.id = $cid AND m.lair_id = l.id";
  $result = mysql_query($sql) or die(mysql_error());

  $sql = "DELETE FROM char_power_link WHERE char_id = $cid";
  $result = mysql_query($sql) or die(mysql_error());

  if ($powers != "") {
   $val = "";
   foreach ($powers as $key => $id) {
    $val[] = "('$cid', '$id')";
   }
   $values = implode(',', $val);
   $sql = "INSERT IGNORE INTO char_power_link (char_id, power_id)
    VALUES $values";
   $result = mysql_query($sql) or die(mysql_error());
  }

  $sql = "DELETE FROM char_good_bad_link WHERE good_id = $cid OR
   bad_id = $cid";
  $result = mysql_query($sql) or die(mysql_error());

  if ($enemies != '') {
   $val = "";
   foreach ($enemies as $key => $id) {
    $val[] = "('$cid', '$id')";
   }
   $values = implode(',', $val);
   if ($align == 'good') {
    $cols = '(good_id, bad_id)';
   } else {
    $cols = '(bad_id, good_id)';
   }
   $sql = "INSERT IGNORE INTO char_good_bad_link $cols
    VALUES $values";
   $result = mysql_query($sql) or die(mysql_error());
  }

  $redirect = 'charlist.php';
  break;

 case "Delete Powers":
  if ($powers != "") {
   $powerlist = implode(',', $powers);

   $sql = "DELETE FROM char_power WHERE id IN ($powerlist)";
   $result = mysql_query($sql) or die(mysql_error());

   $sql = "DELETE FROM char_power_link
    WHERE power_id IN ($powerlist)";
   $result = mysql_query($sql) or die(mysql_error());
  }

  $redirect = 'poweredit.php';
  break;

 case "Add Power":
  if ($newpower != '') {
   $sql = "INSERT IGNORE INTO char_power (id, power)
    VALUES (NULL, '$newpower')";
   $result = mysql_query($sql) or die(mysql_error());
  }

  $redirect = 'poweredit.php';
  break;

 default:

  $redirect = 'charlist.php';
  break;
}
header("Location: $redirect");
?>