www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Delete Record from MySQL Database

  1. #1
    Join Date
    Apr 2013
    Posts
    23

    Delete Record from MySQL Database

    I am building a website that on a page prints each book record in the database. I have a Delete button and an Update button associated with each record in the database.

    I am currently trying to write the code for the Delete button. Unfortunately, it is not working out. I want to know if someone can help.

    Here is the relevant code from the html file.

    Code:
    while ($row = mysqli_fetch_array($result)) 
     {
    
     echo '<div id="inputNote">';
    
    $time = strtotime($row['date']);
    
    echo '<div id = "infoNote">';
    echo '<div id = "titleauthorNote">';
    echo '<h3>' . htmlspecialchars($note['title'], ENT_QUOTES, 'UTF-8') . '</h3>';
    echo '<p>' . htmlspecialchars($note['author'], ENT_QUOTES, 'UTF-8') . '</p>';
    
    echo '</div>';
    echo '</div>';
    
    $book_id_number = htmlspecialchars($note['book_id_number'], ENT_QUOTES, 'UTF-8');
    
    echo '</div>' ;
    echo '<div id = "publisherNote">' ;
    echo '<p>' . 'Published on' . " " . htmlspecialchars(date('F j, Y,', $time),    
    ENT_QUOTES, 'UTF-8') . 
    " " . 'by' . " " . htmlspecialchars($note['publisher'], ENT_QUOTES, 'UTF-8') . '</p>';
    echo '</div>';
    echo '<div id = "deletedForm">';
    echo '<form name="formDelete" method="post" id="formDelete" action="control.php">'; 
    echo '<input type="submit" value="Update" name="myUpdate" id="myUpdate" style = "width:  
    100px" required>';
    echo '<input type="submit" value="Delete" name="myDelete" id="$book_id_number" style = "width: 
    100px" required>';
    echo '</form>';
    echo '</div>';
    echo '</div>';
    echo '<hr>' ;
    
    echo '</div>';
    }
    ?>

    Here is the code from my php file.

    Code:
    else if (isset($_POST['myDelete']))
    {
    $id_number = mysqli_real_escape_string($link, $_POST['id']);    
    
    $sql="DELETE FROM readbooks WHERE book_id_number = '$id_number'";
    
    if (!mysqli_query($link, $sql))   
    
    {   
    
    $error = 'Error with submission: ' . mysqli_error($link);   
    
    exit();   
    
      } 
    }

  2. #2
    Join Date
    Apr 2013
    Posts
    23
    Actually, I got mixed up. Here is the relevant code from the html.

    Code:
    $book_id_number = htmlspecialchars($note['book_id_number'], ENT_QUOTES, 'UTF-8');
    
    echo '<form name="formDelete" method="post" id="formDelete" action="control.php">'; 
    echo '<input type="submit" value="Update" name="myAdd" id="myAdd">';
    echo '<input type="submit" value="Delete" name="myDelete" id="myDelete">';
    echo '<input type="hidden" name="id" value="$book_id_number>';

  3. #3
    Join Date
    Apr 2013
    Posts
    23
    ***Actually, I got mixed up. Here is the relevant code from the html.

    Code:
    $book_id_number = htmlspecialchars($note['book_id_number'], ENT_QUOTES, 'UTF-8');
    
    echo '<form name="formDelete" method="post" id="formDelete" action="control.php">'; 
    echo '<input type="submit" value="Update" name="myAdd" id="myAdd">';
    echo '<input type="submit" value="Delete" name="myDelete" id="myDelete">';
    echo '<input type="hidden" name="id" value="$book_id_number>';
    
    echo '</form>';

  4. #4
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,360
    Ok, question...

    Do you really want to delete the data or just hide it so it appears to be deleted but in fact you still have the data just in case you really need to rollback to that earlier data or undo an update?

    I also suggest that you try to cut down on code a bit by using the HEREDOC method of outputting a large chink of HTML with PHP variables.

    As a rough idea, something like this
    PHP Code:
    while ($row mysqli_fetch_array($result)) {
    $time strtotime($row['date']);
    $title htmlspecialchars($note['title'], ENT_QUOTES'UTF-8');
    $author htmlspecialchars($note['author'], ENT_QUOTES'UTF-8');
    $book_id_number htmlspecialchars($note['book_id_number'], ENT_QUOTES'UTF-8');
    $published htmlspecialchars(date('F j, Y,'$time), ENT_QUOTES'UTF-8');
    $publisher htmlspecialchars($note['publisher'], ENT_QUOTES'UTF-8');
    echo <<<HEREDOC
    <div id="inputNote">
        <div id = "infoNote">
        <div id = "titleauthorNote">
            <h3>
    $title</h3>
            <p>
    $author</p>
        </div>
        </div>
    </div>

    <div id = "publisherNote">
        <p>Published on 
    $published by $publisher</p>
    </div>

    <div id = "deletedForm">
        <form name="formDelete" method="post" id="formDelete" action="control.php">
            <input type="submit" value="Update" name="myUpdate" id="myUpdate" style = "width:100px" required>
            <input type="submit" value="Delete" name="myDelete" id="
    $book_id_number" style = "width:100px" required>
        </form>
    </div>

    </div>
    <hr>
    </div>';
    HEREDOC;

    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  5. #5
    Join Date
    Apr 2013
    Posts
    23
    \\.\,

    Yes, I am trying to delete the record from the database when I click on the Delete button.

  6. #6
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    780
    So you said it isn't working, but you didnt' give any reason why you think it is not. Are you getting an error message from mysqli? Are you getting any php errors otherwise? Do you actually KNOW that the delete query is being attempted??

    Do you have php error checking turned on?
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  7. #7
    Join Date
    May 2014
    Posts
    75
    I would change:

    HTML Code:
    echo '<input type="hidden" name="id" value="$book_id_number>';
    to

    HTML Code:
    echo '<input type="hidden" name="id" value="$book_id_number">';
    Taking note of the " just before the >.

  8. #8
    Join Date
    Apr 2013
    Posts
    23
    Gravy,

    I corrected the error, but it made no difference.

  9. #9
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    780
    You must of missed my first response so I'll try again.

    What error message(s) are you getting from either PHP or mysqli? If you are not getting one, then how do you know it is not working? Perhaps it isn't even getting to that logic.

    1 = add a check for an error on each of your mysqli function calls.
    2 - add php error checking to the top of your script WHENEVER you are developing one.

    oops! I just noticed that you have got a check for an error on your query call - BUT you never send it out to the client.

    Change:
    Code:
    $error = 'Error with submission: ' . mysqli_error($link);
    to be:
    Code:
    echo 'Error with submission: ' . mysqli_error($link);
    Last edited by ginerjm; 07-08-2014 at 01:19 PM.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  10. #10
    Join Date
    Apr 2013
    Posts
    23
    Quote Originally Posted by ginerjm View Post
    You must of missed my first response so I'll try again.

    What error message(s) are you getting from either PHP or mysqli? If you are not getting one, then how do you know it is not working? Perhaps it isn't even getting to that logic.

    1 = add a check for an error on each of your mysqli function calls.
    2 - add php error checking to the top of your script WHENEVER you are developing one.

    oops! I just noticed that you have got a check for an error on your query call - BUT you never send it out to the client.

    Change:
    Code:
    $error = 'Error with submission: ' . mysqli_error($link);
    to be:
    Code:
    echo 'Error with submission: ' . mysqli_error($link);

    I modified the error checking, but I still do not get any error message on the web page. Before this, I tried using the Delete button and I clicked it in order to delete all the records in the database, and it worked.

    So, I think there has to be something wrong with the book_id category.

  11. #11
    Join Date
    Apr 2013
    Posts
    23
    Quote Originally Posted by NorrinRadd View Post
    I modified the error checking, but I still do not get any error message on the web page. Before this, I tried using the Delete button and I clicked it in order to delete all the records in the database, and it worked.

    So, I think there has to be something wrong with the book_id category.

    I think, if I remember well, I set up the query in the following way:

    Code:
    else if (isset($_POST['myDelete']))
    {
    	$ids = mysqli_real_escape_string($link, $_POST['id']);    
    
    	$sql="DELETE FROM readbooks WHERE book_id = '$ids'";
      
      
       if (!mysqli_query($link, $sql))   
    
      {   
    
        $error = 'Error with submission: ' . mysqli_error($link);   
    
        include 'php/error.html.php'; 
    	
        exit();   
    
      } 
    }

  12. #12
    Join Date
    Apr 2013
    Posts
    23
    I keep making these mistakes. Is there any way to edit previously made posts?

    In any case, here is the previous code for the Delete button that deleted all the records in the database.

    Code:
    else if (isset($_POST['myDelete']))
    {
    	
    	$sql="DELETE FROM readbooks";
      
      
       if (!mysqli_query($link, $sql))   
    
      {   
    
        $error = 'Error with submission: ' . mysqli_error($link);   
    
        include 'php/error.html.php'; 
    	
        exit();   
    
      } 
    }

  13. #13
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    780
    If that delete of all records works, then you have something wrong with your where clause. Why not try it again and echo out the sql statement before executing it and see what it looks like.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  14. #14
    Join Date
    Apr 2013
    Posts
    23
    I did not really understood your comment, but I tried anyway, and I do not think that it worked.

    Also, I tried something else. I looked up the book_id number of a record in the phpMyAdmin database - I am using a WAMP server - and assigned it to the query. After I clicked the Delete for a random record, the record with the specific book_id_number was erased.

    I think that the error has to be with book_id_number category. I have the book_id_number as an auto-increment. However, since I have had to add and erase multiple records, the records are not all separated by an increment of 1. Currently, my first record has a book_id_number of 22. Another record of number 26 is followed by a book_id_number of 35.

    Do you think that this might be the cause of the problem?

  15. #15
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    780
    This is what I want to see:
    Code:
    $sql="DELETE FROM readbooks WHERE book_id = '$ids'";
    echo "Query is: $sql<br>";
    This will show us exactly what you are attempting to execute.


    PS - Why do you keep making mistakes posting? Aren't you simply doing a copy and paste of EXISTING code that you attempted to run? You're not re-typing code into the forum, are you? Because that is the worst way to show us what is happening because it is NOT what you ran, but merely an attempt to replicate it.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles