Adding updated data using a form to MySQLi

Question

I am new to PHP and appreciate all the help I receive.

OK so I now understand that simply resubmitting a form does not over write any previous data on the database what was ending before resubmitting the for.

I was wondering how I would set it so that the database updates the data that needs updating with the new data.

My form works 100% fine other then a few things I would like to iron out and this is one of them. I have looked around google and everything but I can't make sense of anything that I find as all answers are different and there is not a set answer to my question.

if(isset($_POST["signupbtn"])) {
    if ($log_username) {
        /// getting data from submitted form into local variables
        $x = preg_replace('#[^a-z 0-9]#i', '', $_POST['xbox']);
        $p  = preg_replace('#[^a-z 0-9]#i', '', $_POST['psn']);
        $s = preg_replace('#[^a-z 0-9]#i', '', $_POST['steam']);
        $ip = preg_replace('#[^0-9.]#', '', getenv('REMOTE_ADDR'));
        // DUPLICATE DATA CHECKS FOR GAMER PROFILES
        $sqli = "SELECT username FROM player WHERE xbox='$x' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $x_check = mysqli_num_rows($query);
        // -------------------------------------------
        if ($x_check > 0){ 
            echo "Xbox Gamer-Tag already linked to a user on this website";
            exit();
        } else if (is_numeric($x[0])) {
            echo 'Xbox Gamer-Tag cannot begin with a number';
            exit();
        }
        $sqli = "SELECT username FROM player WHERE psn='$p' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $p_check = mysqli_num_rows($query);
        // -------------------------------------------
         if ($p_check > 0){ 
            echo "PSN User already linked to a user on this website";
            exit();
        } else if (is_numeric($p[0])) {
            echo 'PSN User cannot begin with a number';
            exit();
        }
        $sqli = "SELECT username FROM player WHERE steam='$s' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $s_check = mysqli_num_rows($query);
        // FORM DATA ERROR HANDLING
         if ($s_check > 0){ 
            echo "Steam account already linked to a user on this website";
            exit();
        } else if (is_numeric($s[0])) {
            echo 'Steam account cannot begin with a number';
            exit();
        } else  { $sqli = "INSERT INTO player (id, username, xbox, psn, steam, ip, created, lastupdated, notecheck)        
                VALUES ('$id','$log_username','$x','$p','$s','$ip',NOW(),NOW(),NOW())"; 
                $query = mysqli_query($db_conx, $sqli);
                   echo "Gamer Profiles Updated";
                }   

        exit();

            if (!file_exists("p_player/$log_username")) {
                mkdir("p_player/$log_username", 0755);
            } 


     }


}

As you can see that is my current php what is working fine except the fact that I can't update information that has been submitted.

Thank you for reading this and I hope someone is able to provide me with the knowledge that is need to preform this task.

Thanks once again for your time it has taken for you to read this post.

When exactly would you be "updating" the user? When $s_check is not empty? When $p_check is not empty? At a different time? — newfurniturey
– newfurniturey, Commented Jan 16, 2014 at 3:39
If I understand you correctly, you'd need to check whether the data is equal to what you're wanting to update. Using a WHERE column='data' and if it's not equal to it, update. Correct? You could also use a query inside a conditional statement to check whether the data going in is the same as an existing one. — Funk Forty Niner
– Funk Forty Niner, Commented Jan 16, 2014 at 3:42
@fred yes if the data is not equal to what is already in the database I would like to delete the current data and then add the new data so someone can update there info when and if needed to — Luke G
– Luke G, Commented Jan 16, 2014 at 3:50
You won't have to use DELETE, updating it will just overwrite what's presently in there, IF it needs to be replaced. You could also make use of the REPLACE INTO function. — Funk Forty Niner
– Funk Forty Niner, Commented Jan 16, 2014 at 3:52

newfurniturey · Accepted Answer · 2014-01-16 03:46:55Z

1

I'm not precisely sure "where" in your code you want to perform the actual update since you have several spots where it detects an existing account; however, the overall goal to be achieved can be done with a straightforward UPDATE clause.

Let's assume, for instance, you have the user's id that you want to update. Your MySQL query would then look like UPDATE player SET ... WHERE id = ?. If we want to port that to PHP with the variables you're using, you would use:

$stmt = $mysqli->prepare('
    UPDATE player
    SET xbox = ?, psn = ?, steam = ?, ip = ?, lastupdated = NOW()
    WHERE id = ?
');
$stmt->bind_param('ssssi', $x, $p, $s, $ip, $id);
$stmt->execute();
$stmt->close();

The above creates a prepared statement and binds all appropriate variables.

If you don't know a user's id but you want to update their info when, for instance, they submit a duplicate Steam account ($s), you could use something similar to:

$stmt = $mysqli->prepare('
    UPDATE player
    SET xbox = ?, psn = ?, ip = ?, lastupdated = NOW()
    WHERE steam = ?
');
$stmt->bind_param('ssss', $x, $p, $ip, $s);
$stmt->execute();
$stmt->close();

This is also portable for cases for both xbox and psn; however, I would recommend creating actual user-tracking so other users can't inadvertently overwrite a different user's info (if it will be public-facing, that is).

answered Jan 16, 2014 at 3:46

newfurniturey

38.6k10 gold badges99 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Luke G Over a year ago

I will not have a problem with splitting up the forms and making 3 separate forms for 3 separate inputs but I would still get the same problem as the username, ip and dates would not get updated. I am not 100% bothered where the updates take place or whether I need to do 3 to start with as I will be able to changed things about and have a play if I know what I need to add to be able to preform the update in the first place. Thank you for your reply

Luke G Over a year ago

I will know the username that is trying to update the profile so I am guessing I will be able to use this instead of using the ID

newfurniturey Over a year ago

@LukeG Correct, it will work the same way with WHERE username = ? instead =]

Luke G Over a year ago

I am guessing I do not put the ? and would actually put the variable that contains the active users username.

newfurniturey Over a year ago

@LukeG My examples use prepared statements; the ? is dynamically populated when you would call $stmt->execute();. If you want to not use prepared statements, I suggest you take a look at mysqli_real_escape_string() to prevent SQL Injection.

|

Funk Forty Niner · Accepted Answer · 2014-01-16 04:02:58Z

0

I took this from one of my scripts, and is basically done like the following:

The $_POST['query_var']; would come from your form input, resembling:

<input type="text" name="query_var">

Then, the SQL part:

$con=mysqli_connect("xxx","xxx","xxx","xxx");
$query= mysqli_real_escape_string($con, $_POST['query_var']);
$query_check = $con->query("SELECT * FROM `your_table` WHERE `column_to_check` = '". $query ."' "); 

if (mysqli_num_rows($query_check) > 0) 
{ 
echo "Data is the same";
}

else{
// do something else
}

answered Jan 16, 2014 at 4:02

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

24 Comments

Luke G Over a year ago

Thank you for posting. I believe the else is where I would execute the UPDATE function?

Luke G Over a year ago

Would this run instead of using INSERT or would I still have to use INSERT if they was entering submitting data for the fist time and if I still need to use INSERT how would I execute that instead of executing the UPDATE function. I told you I was new to it all

Funk Forty Niner Over a year ago

I suggest that you work with a "copy" of a new code file, then try it out in conjunction with what you have. However, it could work by itself, depending if you're just checking the one column at a time. @LukeG

Luke G Over a year ago

OK I will see if I can get my head around it all lol. Thanks for answering once again

Luke G Over a year ago

once last question before I kill myself with getting it to work. I believe I am not tied down to using query_var as a name for each field am I.

|

Collectives™ on Stack Overflow

Adding updated data using a form to MySQLi

2 Answers 2

6 Comments

24 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

24 Comments

Your Answer

Sign up or log in

Post as a guest

Related