Deleting record from MySQL

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • keeps21
    New Member
    • Jul 2007
    • 23

    #1

    Deleting record from MySQL

    I'm having a bit of trouble creating a page to delete a story from the content table in my database.

    I have checked my DELETE query directly in phpmyadmin and it is working fine.

    I am echoing the query to check that it comes out as expected and it looks fine.

    The problem seems to be that when my code is run the record never gets deleted.

    Meaning that mysql_affected_ rows() is always -1 and therefore an error message - 'Could not delete the story' is always output.

    I just can't figure out why, Any help would be greatly appreciated.

    Thanks.

    Here is my code
    [php]
    <?php
    # Filename - delete_story.ph p
    # Date - 10th August 2007
    # Author - Stephen Hoult
    # Author Email - stephen@hoult.o rg.uk

    // This file allows logged in users to delete a story.

    // Include config file for error management and such
    include('./includes/config.inc.php' );

    // Set page title and include HTML header
    $page_title = 'Delete a Story';
    include('./includes/header.html');


    // If no first_name variable exists, redirect the user.
    if (!isset($_SESSI ON['first_name'])) {

    // Start defining the URL.
    $url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVE R['PHP_SELF']);
    // Check for a trailing slash.
    if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
    $url = substr ($url, 0, -1); // Chop off the slash.
    }

    // Add the page.
    $url .= '/login.php';

    ob_end_clean(); // Delete the buffer.
    header("Locatio n: $url");
    exit(); // Quit the script.

    } else { // First name variable exists - user is logged in

    // Check to see if ID exists
    if ( (isset($_GET['id'])) && (is_numeric($_G ET['id'])) ) {
    $id = $_GET['id'];

    } elseif ( (isset($_POST['id'])) && (is_numeric($_P OST['id'])) ) {
    $id = $_POST['id'];

    } else { // Kill The script
    echo '<p><font color="red" size="+1">That ID does not exist. Please select a Valid ID</font></p>';
    include('./includes/footer.html'); // Include HTML footer
    exit();

    } // End of If ID exists


    // Connect to the database
    require_once('. ./mysql_connect.p hp');

    // Check if the form has been submitted
    if (isset($_POST['submitted'])) {


    if ($_POST['sure'] == 'Yes') { // Delete the story - User selected Yes

    // Make the query
    $query = "DELETE FROM content WHERE id = $id LIMIT 1";
    $result = mysql_query($qu ery); // Run the query


    echo $query; //For debugging purposes
    echo mysql_affected_ rows(); //For debugging purposes


    if (mysql_affected _rows() == 1) { // Query ran ok

    // Print a message

    echo '<p>The story has been deleted.</p>';

    } else { // If it didn't run ok

    echo '<p><font color="red" size="+1">The story could not be deleted. Please try again.</font></p>';

    } // End if query ran ok


    } else { // Wasn't sure about deleting - User selects No
    echo '<p><font color="red" size="+1">The story was not deleted.</font></p>';
    }

    } else { // else the form was not submitted - Show the form


    ?>
    <fieldset><lege nd>Delete a story</legend>
    <p> Are you sure you want to delete this story?</p>

    <form action="delete_ story.php" method="post" >
    <p><input type="radio" name="sure" value="Yes" >Yes</p>
    <p><input type="radio" name="sure" value="No" checked="checke d" >No</p>

    <p><input type="submit" name="submit" value="Submit" /></p>
    <input type="hidden" name="submitted " value="TRUE" />
    <input type="text" name="id" value="<?php $_POST['id'] = $id; echo $_POST['id'] ;?>">

    </form>

    <?php

    } // End of is submitted
    } // End if logged in

    mysql_close(); // Close the database connection
    include('./includes/footer.html'); // Inlcude the HTML footer.
    ?>[/php]
  • Atli
    Recognized Expert Expert
    • Nov 2006
    • 5062

    #2
    Hi.

    Try adding this line if your query fails. It will print out any error that MySQL returns on a failed query.
    [code=php]
    echo mysql_error();
    [/code]

    Comment

    • jx2
      New Member
      • Feb 2007
      • 228

      #3
      [PHP]
      $query = "DELETE FROM content WHERE id = $id LIMIT 1";
      $result = mysql_query($qu ery); // Run the query

      [/PHP]
      well i think this is the line which might cos your problem

      should be:

      id = '$id'

      and is the LIMIT 1 necessary in here?

      regards
      jx2

      Comment

      • keeps21
        New Member
        • Jul 2007
        • 23

        #4
        SOLVED

        The single quotes around the $id variable in the string are unneccesary.

        I added the mysql_error() and recieved the following message:

        Table 'content.conten t' doesn't exist

        Problem was I had included the wrong mysql_connect.p hp file

        This was flagged by the mysql_error() message showing that the query was looking for a database named 'content' instead of the content table on the my_cms database

        Thanks for your help.

        Comment

        • Atli
          Recognized Expert Expert
          • Nov 2006
          • 5062

          #5
          Originally posted by jx2
          [PHP]
          $query = "DELETE FROM content WHERE id = $id LIMIT 1";
          $result = mysql_query($qu ery); // Run the query

          [/PHP]
          well i think this is the line which might cos your problem

          should be:

          id = '$id'

          and is the LIMIT 1 necessary in here?

          regards
          jx2
          Quotes should only be used arround string values (including dates and binary strings), numeric values should not be quoted.

          You should always use LIMIT clause if it is at all possible when deleting or updating data. There is always a possibility that something might go wrong, or that somebody makes it go wrong, and this prevents such an error from whiping out your entire table.

          Comment

          • Atli
            Recognized Expert Expert
            • Nov 2006
            • 5062

            #6
            Originally posted by keeps21
            SOLVED
            ..
            Glad you found your answer.

            Comment

            • jx2
              New Member
              • Feb 2007
              • 228

              #7
              great men :-)

              i didnt notice you did checked it if that was a number :-)

              jx2

              Comment

              Working...