Building Own Database

PHP
1

I am in the process of going through the sitepoint book titled, “Build Your Own Database Driven Website Using PHP and MySQL” and I have hit a snag. I am putting together a content management system and I want the user to be able to add, edit, or delete players from the database. I got the add function to work perfectly but the Edit and Delete functions are not responding the way should be. I have included my code below.

Controller for the CMS

<?php
include '../includes/magicquotes.inc.php';

if (isset($_GET['add']))
{
    $pagetitle = 'New Player';
    $action = 'addform';
    $number = '';
    $name = '';
    $team = '';
    $height = '';
    $weight = '';
    $id = '';
    $button = 'Add player';
    
    include 'form.html.php';
    exit();
}

if (isset($_GET['addform']))
{
    include '../includes/db.inc.php';
    
    $number = mysqli_real_escape_string($link, $_POST['number']);
    $name = mysqli_real_escape_string($link, $_POST['name']);
    $team = mysqli_real_escape_string($link, $_POST['team']);
    $height = mysqli_real_escape_string($link, $_POST['height']);
    $weight = mysqli_real_escape_string($link, $_POST['weight']);
    $sql = "INSERT INTO roster SET
        number='$number',
        name='$name',
        team='$team',
        height='$height',
        weight='$weight'";
    if(!mysqli_query($link, $sql))
    {
        $error = 'Error adding player to roster';
        include '../error.html.php';
        exit();
    }
    
    header('Location: .');
    exit();
}

if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{
    include '../includes/db.inc.php';
    
    $id = mysqli_real_escape_string($link, $_POST['id']);
    $sql = "SELECT id, number, name, team, height, weight FROM roster WHERE id='$id'";
    $result = mysqli_query($link, $sql);
    if(!result)
    {
        $error = 'Error fetching player details.';
        include '../error.html.php';
        exit();
    }
    $row = mysqli_fetch_array($result);
    
    $pagetitle = 'Edit Player';
    $action = 'editform';
    $number = $row['number'];
    $name = $row['name'];
    $team = $row['team'];
    $height = $row['height'];
    $weight = $row['weight'];
    $id = $row['id'];
    $button = 'Update player';

    //echo 'This is the new code!';
    include 'form.html.php';
    exit();
}

if (isset($_GET['editform']))
{
    include '../includes/db.inc.php';
    
    $id = mysqli_real_escape_string($link, $_POST['id']);
    $number = mysqli_real_escape_string($link, $_POST['number']);
    $name = mysqli_real_escape_string($link, $_POST['name']);
    $team = mysqli_real_escape_string($link, $_POST['team']);
    $height = mysqli_real_escape_string($link, $_POST['height']);
    $weight = mysqli_real_escape_string($link, $_POST['weight']);
    $sql = "UPDATE roster SET
        number='$number',
        name='$name',
        team='$team',
        height='$height',
        weight='$weight'
        WHERE id='$id'";
    if (!mysqli_query($link, $sql))
    {
        $error = 'Error updating player.' . mysqli_error($link);
        include '../error.html.php';
        exit();
    }
    
    header('Location: .');
    exit();
}

if (isset($_POST['action']) and $_POST['action'] == 'Delete')
{
    include '../includes/db.inc.php';
    $id = mysqli_real_escape_string($link, $_POST['id']);
    
    //Delete player from database
    $sql = "DELETE FROM roster WHERE id='$id'";
    if (!mysqli_query($link, $sql))
    {
        $error = 'Error deleting players from roster.';
        include '../error.html.php';
        exit();
    }

    header('Location: .');
    exit();
}

//Display players list
include '../includes/db.inc.php';
$result = mysqli_query($link, 'SELECT id, number, name, team, height, weight FROM roster');
if(!result)
{
    $error = 'Error fetching players from database.';
    include '../error.html.php';
    exit();
}

while ($row = mysqli_fetch_array($result))
{
    $rosters[] = array('id' => $row['id'], 'number' => $row['number'], 'name' => $row['name'], 'team' => $row['team'], 'height' => $row['height'], 'weight' => $row['weight']);
}

include 'roster.html.php';
                                                                 
?>

Form for adding or editing the Database

<?php include_once('../includes/helpers.inc.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php htmlout($pagetitle); ?></title>
</head>

<body>
<h1><?php htmlout($pagetitle); ?></h1>
<form action="?<?php htmlout($action); ?>" method="post">
    <div>
        <label for="number">Number: <input type="text" name="number" id="number" value="<?php htmlout($number); ?>"></label>
    </div>
    <div>
        <label for="name">Name: <input type="text" name="name" id="name" value="<?php htmlout($name); ?>"></label>
    </div>
    <div>
        <label for="team">Team: <input type="text" name="team" id="team" value="<?php htmlout($team); ?>"></label>
    </div>
    <div>
        <label for="height">Height: <input type="text" name="height" id="height" value="<?php htmlout($height); ?>"></label>
    </div>
    <div>
        <label for="weight">Weight: <input type="text" name="weight" id="weight" value="<?php htmlout($weight); ?>"></label>
    </div>
    <div>
        <input type="hidden" name="id" value="<?php htmlout($id); ?>">
        <input type="submit" value="<?php htmlout($button); ?>">
    </div>
</form>
</body>
</html>

Page to display updated Database

<?php include_once('../includes/helpers.inc.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Manage Roster</title>
</head>

<body>
<h1>Manage Roster</h1>
<p><a href="?add">Add new player</a></p>
    <?php foreach ($rosters as $roster): ?>
        <form action="" method="post">
        <table width="600" border="1px">
            <tr>
                <td width="50px">Number</td>
                <td width="200px">Name</td>
                <td width="150px">Team</td>
                <td width="50px">Height</td>
                <td width="50px">Weight</td>
                <td width="100px">Action</td>
            </tr>
            <tr>
                <td><?php htmlout($roster['number']); ?></td>
                <td><?php htmlout($roster['name']); ?></td>
                <td><?php htmlout($roster['team']); ?></td>
                <td><?php htmlout($roster['height']); ?></td>
                <td><?php htmlout($roster['weight']); ?></td>
                <td><input type="submit" name="action" value="Edit">
                <input type="submit" name="action" value="Delete"></td>
            </tr>
        </table>
        </form>
    <?php endforeach; ?>
<p><a href="..">Return to Admin Area</a></p>
</body>
</html>

Again thank you in advance. You can view/test the results here…

http://www.harberwildcatbaseball.com/admin/roster/

2

In your edit code add

var_dump($row)

after

$row = mysqli_fetch_array($result);

and see what it outputs

Also your forms are using both $_GET and $_POST which shouldn’t really be done as i remember reading thats its bad structure design and poor syntax.

3

I tried to add the var_dump where you suggested and it gave no diffrent results. If you test the form at the link I posted, the form comes up when called and seems to submit but does not update the information in the database.

Any other ideas. By the way thank you for for trying I really do appreciate it.

4

I don’t have the book in question but glancing at your code for editing i noticed this:

$id = mysqli_real_escape_string($link, $_POST['id']);

But your form doesn’t have any input called ‘id’ when viewing the Roster table. When you click edit it just loads the page w/o posting anything more than the action value.

5

The input for the id is set has hidden as shown below. Yes, it is suppose to post the editted information once it is clicked but for some reason it is just reloading the table…

<input type="hidden" name="id" value="<?php htmlout($id); ?>">
6 
<title>Manage Roster</title>
</head>

<body>
<h1>Manage Roster</h1>
<p><a href="?add">Add new player</a></p>
    <?php foreach ($rosters as $roster): ?>
        <form action="" method="post">
        <table width="600" border="1px">
            <tr>
                <td width="50px">Number</td>
                <td width="200px">Name</td>
                <td width="150px">Team</td>
                <td width="50px">Height</td>
                <td width="50px">Weight</td>
                <td width="100px">Action</td>
            </tr>
            <tr>
                <td><?php htmlout($roster['number']); ?></td>
                <td><?php htmlout($roster['name']); ?></td>
                <td><?php htmlout($roster['team']); ?></td>
                <td><?php htmlout($roster['height']); ?></td>
                <td><?php htmlout($roster['weight']); ?></td>
                <td><input type="submit" name="action" value="Edit">
                <input type="submit" name="action" value="Delete"></td>
            </tr>
        </table>
        </form>
    <?php endforeach; ?>
<p><a href="..">Return to Admin Area</a></p>
</body>
</html>

On this form your missing the hidden field for ID, without which the php code won’t get a match when it searches the database as it won’t have an ID to search for

7

OMG! Duh thank you so much Phoenix that fixed them both.