Updating a form entry in php/mysql with checkboxes?

Question

How can I allow the user submitting a form, to update his entry on "re-submission" for example

12345678910 (unique id) , submitted the form with selections,
12345678910 , re-submitted with new selections

what's the function responsible for "automatically" updating such kind of form entries.

I know that I can use a check if the entry exists, but how do I update it if it exists and insert it in a new row if it doesn't ...

function checkstudentid($studentid)
{
    $con = connectvar();
    mysql_select_db("database1", $con);
    $result = mysql_query(
        "SELECT * FROM table WHERE studentid='$studentid' LIMIT 1");

    if(mysql_fetch_array($result) !== false)
        ....
    // I want to add the entry here since it doesn't exist...with checkboxes 

// else , I want to update if it already exists

   }

Now I'm also not completely positive if the above code will work...but this is what I have for starters, if there is any other way or if the method I'm using is "wrong" , I would appreciate the heads up...or if what I'm trying to is even possible (the way I'm doing it)...

NOTES

I only have one php file which the form submits to.
I am not using a login/registration system
I do not want to display all the data in a table using HTML, just an "automatic" update if the studentid already exists in the table

there is function for "automatic". PHP provides a basic set of tools, and it's your job to use those toosl in various combinations/sequences to accomplish your project. You're already using ALL of those tools with your sample code above, you just have to actually learn how those tools are working. And note that you're most likely vulnerable to [sql injection attacks](http://bobby-tables.com) — Marc B, Sep 19 '14 at 15:57
I would say that you could use MySQL's `ON DUPLICATE KEY` and flag the `studentid` column, that way it'll auto update it for you. — Ohgodwhy, Sep 19 '14 at 15:59
@Ohgodwhy , would that also update the choices in the new entry (checkboxes) ? I mean if the new entry matches a studentid that already exists it would replace it, but what about the (selections) column ? — Azizi, Sep 19 '14 at 16:05
Sure it would. You're writing the code for it, so why not? [Have a look here](http://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert) — Ohgodwhy, Sep 19 '14 at 16:06
Only one problem, I'm not exactly typing my database entries, I'm using a form to fill in the data, does that change anything ? Or as long as the mysql column is flagged it would update regardless of how the data is entered? @Ohgodwhy — Azizi, Sep 19 '14 at 16:10
@MarcB , so there is no reason why I should not include an update function inside an else ? if so...is there a general update function I can use or is it a "custom" function. — Azizi, Sep 19 '14 at 16:13
there is no "update function". there's mysql_query() which sends queries to the database. it's not PHP's job to update/delete/insert for you. PHP has no idea what a database is. It just provides some functions which YOU call and pass in the appropriate things. We are also not here to tell you how to structure your code. — Marc B, Sep 19 '14 at 16:15
Regarding comments about sql, take a look at this [tutsplus article](http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059). It's a really good primer for good sql practices. I think it actually makes for neater and more readable code too. — br3nt, Sep 19 '14 at 16:19

burmat · Accepted Answer · 2014-09-19T16:24:23.367

If I were using a deprecated method to interact with a database, I would probably just do this:

<?php
    function checkstudentid($studentid) {
        $con = connectvar();
        mysql_select_db("database1", $con);
        $result = mysql_query(
            "SELECT * FROM table WHERE studentid='$studentid' LIMIT 1");

        $query = '';
        if (mysql_num_rows($result) > 0) {
            $query = "UPDATE table SET column1='$value_one', column2='$value_two' WHERE studentid='$studentid'";
        } else {
            $query = "INSERT INTO table VALUES('$new_id', '$value_one', '$value_two')";
        }

        if (mysql_query($query)) {
            return true;
        } else {
            return false;
        }
    }
?>

But then again, I would use PDO to interact with the DB.

Here is a simple PDO example (you just have to write the function to return the connection):

<?php
    function checkstudentid($studentid) {

        $update = false;

        $dbh = formPDOConnection();
        $query = "SELECT studentid FROM table WHERE studentid=:id";
        $stmt = $dbh->prepare($query);
        $stmt->bindValue(':id', $studentid, PDO::PARAM_STR);
        if ($stmt->execute()) {
            if ($stmt->rowCount()) {
                $update = true;
            }
        } else {
            return 'failure to execute query';
        }

        // if we just need to update
        if ($update) {
            $update = "UPDATE table SET value1=:v1, 
                value2=:v2 WHERE studentid=:id";
            $stmt = $dbh->prepare($update);
            $stmt->bindValue(':id', $studentid, PDO::PARAM_STR);
            $stmt->bindValue(':v1', $value_one, PDO::PARAM_STR);
            $stmt->bindValue(':v2', $value_two, PDO::PARAM_STR);
        } else {
            $insert = "INSERT INTO table VALUES(:id,:v1,v2)";
            $stmt = $dbh->prepare($insert);
            $stmt->bindValue(':id', $new_id, PDO::PARAM_STR);
            $stmt->bindValue(':v1', $value_one, PDO::PARAM_STR);
            $stmt->bindValue(':v2', $value_two, PDO::PARAM_STR);
        }

        return $stmt->execute();
    }
?>

Save yourself a headache and stop using mysql_*

I think this answers it , PDO it shall be... its gonna take me a while to update the full code , but I'll mark this as an answer. thanks — Azizi, Sep 19 '14 at 16:28

score 1 · Answer 2 · answered Sep 19 '14 at 16:23

You can use INSERT... ON DUPLICATE KEY UPDATE... on your mysql code instead use the logic in your PHP.

Here's a sample:

INSERT INTO `category` (`id`, `name`) VALUES (12, 'color')
  ON DUPLICATE KEY UPDATE `name` = 'color';

Reference: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Updating a form entry in php/mysql with checkboxes?

2 Answers2