Delete items from a database - receiving errors on testing

gwh · January 8, 2010

Hi everyone,

I'm trying to create some code that will delete categories from a categories table but only if the category isn't already assigned to an item in another table.

I came up with the following code in a controller file:

<?php

if (isset($_POST['action']) and $_POST['action'] == 'Delete')
{
    include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';
    $id = mysqli_real_escape_string($link, $_POST['catID']);

    // Get items with certain category
    $sql = "SELECT itemID FROM items WHERE catID='$id'";
    $result = mysqli_query($link, $sql);
    if (!$result)
    {
        $error = 'Error getting list of items to delete.';
        include 'error.html.php';
        exit();
    }

    // assume that no match has been found
    $recordsExist = false;

    // check whether recordset found any matches
    if ($result > 0) {

        $recordsExist = true;
         include 'category_delete.html.php';
        
     }
        else {
            
        // Delete the category
        $sql = "DELETE FROM categories WHERE id='$id'";
        if (!mysqli_query($link, $sql))
            {
                $error = 'Error deleting category.';
                include 'error.html.php';
                exit();
            }
        }
        
    header('Location: .');
    exit();
}

// Display category list
include $_SERVER['DOCUMENT_ROOT'] . '/includes/dbAdmin.inc.php';
$result = mysqli_query($link, 'SELECT catID, category FROM categories');
if (!$result)
{
    $error = 'Error fetching categories from database!';
    include 'error.inc.html.php';
    exit();
}

while ($row = mysqli_fetch_array($result))
{
    $categories[] = array('catID' => $row['catID'], 'category' => $row['category']);
}

include 'categories.html.php';
?>

It displays the list of categories well however when I click one of the delete buttons, I get the following errors:

Notice: Object of class mysqli_result could not be converted to int in /Applications/MAMP/htdocs/Kelly_Country_2010/admin/catalogue/categories/index.php on line 22

Parse error: syntax error, unexpected ';' in /Applications/MAMP/htdocs/Kelly_Country_2010/admin/catalogue/categories/category_delete.html.php on line 16

index.php is the controller file I've shown above and line 22 is the following line:

if ($result > 0) {

The following is the category_delete.html.php template file that's included after the code checks whether the recordset found any matches:

<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/includes/helpers.inc.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
    <title>Manage Categories</title>
    <meta http-equiv="content-type"
                content="text/html; charset=utf-8"/>
</head>
    <body>
        <h1>Delete Category</h1>
        
        <p>
    <?php
if ($recordsExist) {
  echo '<p class="warning">'. htmlout($category['category'] . ' has dependent records. Can\'t be deleted.</p>';
  }
else {
?>
</p>
<p class="warning">Please confirm that you want to delete the following record. This operation cannot be undone. </p>

        
                <ul>
                <li>
                    <form action="" method="post">
                        <div>
                            <?php htmlout($category['category']); ?>
                            <input type="hidden" name="catID" value="<?php
                                    echo $category['catID']; ?>"/>
                            <input type="submit" name="delete" value="Confirm deletion"/>
                        </div>
                    </form> 
    </li>
        </ul><?php } ?>
        <p><a href="..">Return to category list</a></p>
    </body>
</html>

Line 16 that's mentioned in the error is as follows:

echo '<p class="warning">'. htmlout($category['category'] . ' has dependent records. Can\'t be deleted.</p>';

I wondered if anyone knows what the errors are about and what I need to do to get this working?

Appreciate any advice.

cags · January 8, 2010

if ($result > 0) {

...should be...

if (mysqli_num_rows($result) > 0) {

...because mysqli_query returns a resource object not a number, this code checks if any rows were found.

JAY6390 · January 8, 2010

This line

htmlout($category['category']

should be

htmlout($category['category'])

in your category_delete.html.php file

gwh · January 8, 2010

Thanks for the replies,

I made the two corrections but now when I test by clicking one of the delete buttons, it comes up with the following errors:

Notice: Undefined variable: category in /Applications/MAMP/htdocs/new_site/admin/catalogue/categories/category_delete.html.php on line 16

and

Warning: Cannot modify header information - headers already sent by (output started at /Applications/MAMP/htdocs/new_site/includes/output.html.php:10) in /Applications/MAMP/htdocs/new_site/admin/catalogue/categories/index.php on line 40

Line 16 in category_delete.html.php is:

echo '<p class="warning">'. htmlout($category['category']) . ' has dependent records. Can\'t be deleted.</p>';

...and line 40 in index.php is:

header('Location: .');

Do you know what's happening?

cags · January 8, 2010

The first error is probably because you are attempting to use the array $category which I don't see defined anywhere, perhaps you meant $categorries? Very difficult to say for sure without knowing all your code. As for the second problem 'Cannot modify header', this error is so common there is a 'sticky' for it.

gwh · January 8, 2010

I think that at the following point in index.php (above the include 'category_delete.html.php'; statement) is where I need to define a variable.

	// check whether recordset found any matches
if (mysqli_num_rows($result) > 0) {

	$recordsExist = true;

 	include 'category_delete.html.php';

	 }

The following SQL (which is already in the file) selects the itemID from the items table but only where catID (which is the foreign key in the items table for the category in the categories table) is equivalent to $id:

$sql = "SELECT itemID FROM items WHERE catID='$id'";

This is $id

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

So, since the above query only selects the itemID and not the category itself, does this mean I need to create another query that does this? If yes, I'd need the category to be the same as $_POST['catID') since that's the category being deleted and also the name of the variable that needs to go in the category_delete.html.php file.

The following is the code from the template file that lists the categories. This code and the two previous code files I've already posted constitute the application at this point:

<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/includes/helpers.inc.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Manage Categories</title>
<meta http-equiv="content-type"
			content="text/html; charset=utf-8"/>
</head>
<body>
	<h1>Manage Categories</h1>
	<p><a href="?add">Add new category</a></p>
	<ul>
		<?php foreach ($categories as $category): ?>
			<li>
				<form action="" method="post">
					<div>
						<?php htmlout($category['category']); ?>
						<input type="hidden" name="catID" value="<?php
								echo $category['catID']; ?>"/>
						<input type="submit" name="action" value="Edit"/>
						<input type="submit" name="action" value="Delete"/>
					</div>
				</form>
			</li>
		<?php endforeach; ?>
	</ul>
</body>
</html>

Appreciate any further help.

gwh · January 8, 2010

I came up with the following:

$sql = "SELECT category FROM categories WHERE catID='$id'";

$category = $sql;

I put this above the include statement in index.php and then in category_delete.html.php I have the following line:

<?php htmlout($category); ?>

When I test, the error is no longer there but instead of the name of the category, the browser is outputting:

SELECT category FROM categories WHERE catID='1'

Any ideas?

cags · January 8, 2010

Well I'm not sure what exactly htmloutput() does, but that's exactly what I'd expect it to output. You are assigning that value to a string variable called $sql, then setting the value of $category equal to that variable. You need to actually run the query using mysqli_query to fetch a resource object then use one of the mysqli_fetch functions to get the actual data from that resource object (much like is done elsewhere in your code).

gwh · January 8, 2010

I'll try that - thanks for the help.

Sign In

Delete items from a database - receiving errors on testing

Recommended Posts

gwh

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

JAY6390

Link to comment

Share on other sites

gwh

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

gwh

Link to comment

Share on other sites

gwh

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

gwh

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information