PHP/MySQL: Deleting data from DB

Question

This is a little more complicated than the title may lead you to believe. I have a function that queries my DB as soon as a user enters the page. The query then returns the id and note_name (these are the names in the columns in my DB). I can get the notes to display just fine, where I'm having trouble is deleting them. I am very confused on how to dynamically tell the server which note is currently being selected.

Let me know if I have supplied enough information to solve this problem, I am removing unnecessary code to save space. All code is in the same .php script.

notes.php

select_notes() function

// query DB for pre-existing notes
function select_notes() {
  include('includes/connection.php');
  
  $username = $_SESSION['username'];
  // make READ query to the db and return the results
  $query  = "SELECT `id`, `note_name` FROM `notes` WHERE `username`='$username'";
  $result = mysqli_query($conn, $query);
  
  if (mysqli_num_rows($result) > 0) {
    // create empty array for future use
    $note_id   = [];
    $note_name = [];
    
    while ($row = mysqli_fetch_assoc($result)) {
      // push all the data from $row into $note_name array
      array_push($note_id, $row['id']);
      array_push($note_name, $row['note_name']);
    }
    
    // close connection and return array containing note details from DB
    mysqli_close($conn);
    
    return [ $note_id, $note_name ];
  } else {
    mysqli_close($conn);
    echo '<p>No notes available</p>';
  }
}

Where select_notes() is being called in the HTML

<div class="saved-notes col-10 offset-1 col-md-4 offset-md-0">
          <header class="text-center">
            <h2>Saved Notes</h2>
          </header>
          <div class="pt-2">
            <form action="<?php htmlspecialchars($_SERVER['PHP_SELF']); ?>" class="form-inline px-1" method="POST">
              <input class="form-control mb-1 mr-1" name="note_name" type="text">
              <input class="btn btn-success btn-sm" name="create_note" type="submit" value="Save">
            </form>
            
            <?php
            
              if ($_POST['create_note']) {
                insert_note();
              }
              
              list($note_id, $note_name) = select_notes();
              
              foreach (select_notes() as $note) {
                foreach ($note as $value) {
                  echo '
                        <form action="notes.php" class="single-note-form" method="POST">
                          <button name="edit_note" type="submit">'.$value.'</button>
                          <button class="text-danger" name="delete_note" type="submit">&times;</button>
                        </form>
                  ';
                }
              }
              
            ?>
            
          </div>
        </div><!-- col -->

Answer 1

In your Delete form, you must embed the id of the note, you have selected in the query

<form action="notes.php" class="single-note-form" method="POST">
    <button name="edit_note" type="submit">'.$value.'</button>
    <button class="text-danger" name="delete_note" type="submit">&times;</button>
    //include a hidden field containing the records ID
    <input type="hidden" name="note_id" value="?" />
</form>

You will find the structure of you return array to be to limiting. Currently you have 2 arrays one with the note text ( I presume ) and one with the note ids. I would combine this into a single row like this ( inside select_notes):

 $notes = [];
 while ($row = mysqli_fetch_assoc($result)){
   $notes[] = ['id' => $row['id'], 'note_name' => $row['note_name']];
 }
 return $notes;

This way you will have a multi-dimension array, much like what the database returns, Then in your HTML:

foreach (select_notes() as $note) {
   echo '
<form action="notes.php" class="single-note-form" method="POST">
    <button name="edit_note" type="submit">'.$note['note_name'].'</button>
    <button class="text-danger" name="delete_note" type="submit">&times;</button>
  <input type="hidden" name="note_id" value="'.$note['id'].'" />
</form>';
}

Or some other way of getting note_id into the form.

If you need a list of the ids, it's very simple to pull it out of the multi-dimensional array using $ids = array_column($notes, 'id'), so you lose nothing by using a combined structure. And in fact it makes the code, cleaner, shorter and more concise.

http://php.net/manual/en/function.array-column.php

But anyway, once it's embedded in the form, it becomes simple as it's passed with the post request. Then it's just a matter of $_POST['note_id'] when you do the delete.

You should also use prepared statements in your query, even if it's session data and you think it's clean. Who knows what will change down the line and you may introduce a way for that value to be modified by a user, then it's open season. SQL Injection prevention should always be done. It takes very little effort to do and then you have piece of mind knowing your code is good.

One last thing:

$query  = "SELECT `id`, `note_name` FROM `notes` WHERE `username`='$username'";

I would say using the username is probably going to cause you grief at some point. This should really use the id from the user table. Now your usernames may be unique, but from a purely performance standpoint, using a numeric index is way faster then strings. And, well it just sort of bugs me...

Answer 2

What I understand from your question is that hte user logs in, and is sent to this new page, where PHP code sends back one or more notes associated with their username.

You display these notes on the page, but you want to know how to delete a note.

PHP runs once, then stops. It leaves off with the note(s) displayed on the page. Now, you need a way to (1) trap the user interaction (clicking on a note) and (2) send instruction to delete the note from the database.

A great way to do this is to use javascript/jQuery to trap the user click event, and (2) AJAX to send/receive data to/from another PHP file on the server (not to worry, much simpler than it sounds). The PHP file will receive the information and run a MySQL instruction to delete the appropriate note.

Here are some simple AJAX examples so you have an idea how it works.

https://stackoverflow.com/a/17974843/1447509

Basically, your code will look something like this:

$(document).on('click', '.user-note', function(){
  var tmp = $(this).attr('id'); //assuming the id contains the note_id and you can isolate that, perhaps with .split()
  $.ajax({
    type: 'post',
     url: 'name_of_php_file_on_your_server.php',
    data: 'note_id=' +tmp+ '&what2do=delete'
  }).done(function(d){
    alert(d); //displays whatever the above PHP file echo's back to javascript
  });
});