Update MySQL using ID from drop-down-menu

Question

I would like some help with my code. I've already searched answered questions on this site but haven't found anything that works unfortunately. I'm doing a website where you can write, save, delete and update notes. Now I'm trying to fix an update button were you can change a posted note using its unique ID. The update button is on a separate page from the notes, and you choose the ID of posted notes using using a drop-down menu showing the existing IDs in my database table. You choose it, write a new note and then click update.

This is my current code:

        <li id="id_number"><label>ID number: <select name="id_number"> 
            <option value =" ">Select number</option> 
              <?php $query = "SELECT * FROM notes"; 
              $result2 = mysql_query($query); 
              while ($row = mysql_fetch_array($result2)) { 
                $id = $row ['id']; 
                $select = ""; 
                if ($id == $idID2) {
                    $select = "selected" ; 
                    } 
                ?><option <?php print $select ?> value="<?php print $id; ?>"><?php print "$id";     
                ?></option><?php 
                }  
                ?>
          </select> 
          </label></li>
        <li id="note">New note:</li>
        <li id="note"> <textarea rows="10" cols="30" name="note" value="<?php print $note;?>"/></textarea></li>
        <li id="send"><input type="submit" name="submit" value="Update!"/></li>
    </ul>
    </form>

    <?php
        $id= $_POST ['id'];
        $subject= $_POST ['subject'];
        $note= $_POST ['note'];
    ?>

    <?php
    if (isset($_POST['submit'])) {  //validates the data
            $error="0";


            if ( $note == "" ) { 
                        $error++; 
                        $wrong .= "<p> You forgot to write a new note.</p>";
            }


        if ($error > 0) {
            print "<p><strong>You have made these errors:</strong></p>";
            ?>
                <?php print $wrong; ?>


            <?php
        }


    //if no errors  

        else { 
                $note = mysql_real_escape_string($note); 
                $note = htmlspecialchars($note);
                $id = mysql_real_escape_string($id); 
                $id = htmlspecialchars($id);
                $date = date("j/n, Y, H:i");
                $query = ("UPDATE notes SET note='$note' WHERE id='$id'");
                $result = mysql_query($query) or die (mysql_error()); 
                if ($result) { 
                print "<p>Note updated!</p>";
            }
        }   
    }

    ?>

When choosing an existing ID and change the note and clicking update it says "Note updated" but the note in the database remains the same. What am i doing wrong?

You should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). — Matt, Aug 06 '12 at 13:29

score 0 · Accepted Answer · answered Aug 06 '12 at 13:22

0

$id = $_POST['id']

should be

$id = $_POST['id_number']

As far as I can tell, you aren't referring to any input with name "id".

answered Aug 06 '12 at 13:22

DavidS

1,660
1
12
26

score 0 · Answer 2 · edited May 23 '17 at 10:34

Instead of answering "what am I doing wrong?" I'm going to answer "How can I do this right?"

Your interface is set up in a fashion that is not intuitive. The dropdown should contain information about the notes (the ID is sufficient here), and when the user selects a note from the dropdown, your text input should be populated with what's already there. If no note ID is selected from the dropdown and the user clicks "update", the system should insert a new note record (if the text area has content).

So, how do we do this? First let's set up our form:

<form action="" method="post">
    <select id="noteIds" name="noteId">
        <option value=''>Select a Note</option>
        <option value='1'>1</option>
        <option value='2'>2</option>
        .
        .
        .
    </select> <br />
    <textarea name="noteContents" id="noteContents"></textarea><br />
    <input type="submit" value="Update" />
</form>

Now that we have our form set up we can control the behavior of the elements using jQuery:

<script language="javascript" type="text/javascript">
    $("#noteIds").on('change', function() { 
        $.post("getNoteInfo.php", {
                noteId: $(this).val()
            }, function(data) {
                $("#noteContents").html(data);
            }
        );
    });
</script>

The above code will set the output of geNoteInfo.php to the contents of your textarea.

Next let's work on getNoteInfo.php (in pseudo-code).

<?php
    $noteId = $_POST['noteId'];

    $noteText = // retrieve note text from database where note ID = $noteId

    echo $noteText;

Now, when the user clicks Update, check to see that an ID is selected. If the ID was selected, update that row. If no ID has been selected, insert a new row IF there is text in the textarea.

NOTE: You should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article.

Understand exactly what you mean. But I can't get that code to work. I'm quite new with this. — Miranda, Aug 06 '12 at 14:09
Try not to take my code at face value. Instead, try to understand the functionality of what the code does, then implement it to suit your case. If it's a matter of understanding how jQuery works, [here's a tutorial](http://docs.jquery.com/Tutorials). — Matt, Aug 06 '12 at 14:12

Update MySQL using ID from drop-down-menu

2 Answers2