PHP/mySQLi update values where user exists

Question

We'll get to the point...

I have a simple form (2 of them) that relies off the previous filled out.

The intention of these forms are to sign, post to db, validate email. After the user validates their email their permission will change to be able to see the next form.

These forms work great, and everything is functional in exception to this last bit.

I am having difficulty with the form applying the values to the db table when there is existing user.

What I would like to do is only have it update the keys for that user where users session-ed API key =$API AND form_ica_initials is NULL in the roster table. If it does then will INSERT INTO

Here is what I have cleaned up. (originally wrote for the first phase of the forms to be filled out, trying to tweak to work for last half of forms)

    if (empty($_POST['initials'])) { $error[] = 'You must enter your initials in every box below.'; } 
        else { $initials = $_POST['initials']; }

$API = $_SESSION['API'];

    if (empty($error)) { 
      $query_verify_form = "SELECT * FROM roster WHERE API ='$API'";
      $result_verify_form = mysqli_query($dbc, $query_verify_form);
    if (!$result_verify_form) { 
        echo ' Database Error Occured ';
        }
    if (mysqli_num_rows($result_verify_form) == 0) { 
              $form_icaauth = md5(uniqid(rand(), true));
              error_reporting(E_ALL);
              $query_insert_user = "UPDATE `roster` 
                                                ( 
                                                `fullname`, `form_ica_initials`, `form_icaauth`,`form_ica_ip`
                                                ) 
                                        VALUES ( 
                                                '$fullname', '$initials', '$form_icaauth','$DOCSIGNEDBYIP'
                                                )
                                        ";

         $result_insert_user = mysqli_query($dbc, $query_insert_user);
    if (!$result_insert_user) {
        echo 'Query Failed ';
        }
    if (mysqli_affected_rows($dbc) == 1) {
            ...
        echo '<br><center><div class="success">...</div>';
            } 
            else { 
                  echo '<center><div class="error">...</div></center>';
                 }
            } 
            else {
                  echo  '<center><div class="warning" >...</div></center>';
        }
    } 
    else {
         echo '<center><div class="info"> <ol>';
         foreach ($error as $key => $values) {
                                             echo ' <li>' . $values . '</li>';
                                             }
         echo '</ol></div></center>';
        }

    mysqli_close($dbc); //Close the DB Connection

}

If I change the if (mysqli_num_rows($result_verify_form) == 0) { to ==1 It will post the values to the table by creating a new record, and not update the existing users fields as specified. However, by doing that it will circumvent the errors that I have structured.

I know my way around PHP a bit... but having difficultly with this one

I'm not fully understanding your exact problem but it sounds like what you want can be solved using another query construct (if you are using MySQL?). You can do `INSERT INTO TABLE ... ON DUPLICATE KEY UPDATE`, which will only insert if the primary (or composite) key does not exist, otherwise it will update the matching row: http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — Jeremy Harris, Dec 18 '15 at 00:47
I understand what you're suggesting, and it makes sense, however that still doesnt really solve my `if(mysqli_num_rows($results_verify_form) ==0)` — levi, Dec 18 '15 at 01:00
2 things. First, there's a missing quote here `echo '
...
` if it's part of your code, modify your question as it's throwing off Stack's syntax highlighting. Second, you say "update the existing users fields", but I don't see an `UPDATE table`, I only see an `INSERT INTO`. @LeviZoesch — Funk Forty Niner, Dec 18 '15 at 12:13
I believe the query works great, its my if statements that are causing me havoc @Fred-ii- — levi, Dec 18 '15 at 23:30
I am also in chat if you wouldnt mind assisting me :) http://chat.stackoverflow.com/rooms/11/php @Fred-ii- — levi, Dec 19 '15 at 03:39

score 1 · Accepted Answer · answered Dec 19 '15 at 04:40

I was able to get it to work with the following.

if (empty($error)) { 
      $query_verify_form = "SELECT * FROM roster WHERE API='$API'  AND form_ica_initials IS NULL";
      $result_verify_form = mysqli_query($dbc, $query_verify_form);

    if (mysqli_num_rows($result_verify_form) == 1) { 
              $form_icaauth = md5(uniqid(rand(), true));
              error_reporting(E_ALL);
              $query_insert_user = "UPDATE roster SET fullname='$fullname', form_ica_initials='$initials', API='$API', form_icaauth='$form_icaauth', form_ica_ip='$DOCSIGNEDBYIP'";

         $result_insert_user = mysqli_query($dbc, $query_insert_user);
    if (!$result_insert_user) {
        echo '<center><div class="error">Query Failed </div></center>';
        }

First I had to change if (mysqli_num_rows($result_verify_form) == 1) from 0 to 1 to return Yes we've found that record.

I then had to change the INSERT INTO ... VALUES to UPDATE ... SET. I added also added AND form_ica_initials IS NULL to validate that the user hasn't completed this form yet. IF they have, then we'd prompt with a message to check their email. If they havent then we'd run the UPDATE

PHP/mySQLi update values where user exists

1 Answers1