PHP code for inserting non-duplicate values into MySQL

Question

I have basic PHP/MySQL experience, having taken an introductory class. My knowledge is literally limited to the following PHP codes:

if(!($stmt = $mysqli->prepare...)
if(!($stmt->bind_param...)
if(!$stmt->execute...)

I'm currently trying to write a program that allows a user to enter a new password, and checks the password against existing passwords in the database.

Here is what I have:

<?php
foreach($Password){
    $dupesql = "SELECT PasswordID Passwords WHERE (Password = '$Password')";
    $duperaw = mysql_query($dupesql);
    if(mysql_num_rows($duperaw){
        echo nl2br("$Password has already been used \n");
    }
    else{
        echo "Password added \n";
    }
}
?>

I got the code from this post: Check for duplicates before inserting

I'm not sure if the code itself has problems or if I need to add anything else to my PHP code to get this working, as it's currently producing an "Error 500".

Please use password_hash() for storing passwords, judging from the code you've provided you store your passwords as clear text which you should never ever do. — mazedlx
– mazedlx, Commented Nov 20, 2016 at 18:49
you're not mixing mysql_ and mysqli_ together are you? You going to check comments/answers given or should we stand around here till there's some activity on your part? 500 error is a server error; check your logs. Post & go/leave answers aren't well-accepted here. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 20, 2016 at 19:53
and that foreach($Password) of yours; doesn't do much really. Have you RTM on that? php.net/manual/en/control-structures.foreach.php I take it not. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 20, 2016 at 19:55
Thank you for all the comments, I didn't realize I had to sit online and respond to comments right away. It's only been 2 hours, and I am returning to my post to respond to comments. First, this is just a small prototype and it's only a piece of a larger project, so I'm not storing actual passwords and clear text should work. Second, I'm not sure what RTM means, as I explained before, I copied this code from another post's accepted answer. After reading the answer below, I did change all mysql commands to mysqli commands. — Aisha Ashwal
– Aisha Ashwal, Commented Nov 20, 2016 at 20:59

Community · Accepted Answer · 2017-05-23 11:45:58Z

1

MySQL extension is deprecated and probably you have PHP 7.0 from where it is removed. Rewrite your code to MySQLi or PDO. Check this question on how to convert to MySQLi: How could I change this mysql to mysqli?

Also, your code just doesn't add a password (never). Probably you expect to add it before the "Password Added" message, but be aware: the solution you want to use is not ideal, because there is a risk of race condition between checking the password for existence and adding it. This means that it is possible to add a password twice.

To solve this problem, you might want to use transactions. More details are covered in this question: PHP + MySQL transactions examples

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Nov 20, 2016 at 18:41

Denis V

3,4081 gold badge30 silver badges41 bronze badges

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

5 Comments

Funk Forty Niner Over a year ago

I fear you may have fallen into a rather deep rabbit hole on this question, one that doesn't make much sense neither, especially that foreach of theirs. See the comments I left under their question also.

Denis V Over a year ago

@Fred-ii- Well, yes, you are probably right. But I'll keep my answer here, since it might be useful for others. At least in terms of mentioning race condition problem.

Funk Forty Niner Over a year ago

*sigh* - yeah I'm afraid so there Denis. Oh well, they have enough to go on with what's been given in your answer and comments.

Aisha Ashwal Over a year ago

I read the links you provided. Essentially, I should just changed all codes involving "sql" to "sqli" right?

Denis V Over a year ago

@AishaAshwal your code contains other issues. It was already mentioned that foreach is written syntactically incorrectly + the password is actually never saved. Also, error 500 can mean anything - check your server logs for the exact problem.

Aisha Ashwal · Accepted Answer · 2016-11-20 22:01:22Z

I decided to go an entirely different route, which is to set the Password column as unique.

Then I did a simple INSERT that would prompt an error if the user attempts to add a duplicate:

<?php
        if(!($stmt = $mysqli->prepare("INSERT INTO Heroes(HeroName, FirstName, LastName, Age, HomeCountry, RoleID) VALUES (?,?,?,?,?,?)"))){
            echo "Prepare failed: "  . $stmt->errno . " " . $stmt->error;
        }
        if(!($stmt->bind_param("sssisi",$_POST['HeroName'],$_POST['FirstName'],$_POST['LastName'],$_POST['Age'],$_POST['HomeCountry'],$_POST['RoleID']))){
            echo "Bind failed: "  . $stmt->errno . " " . $stmt->error;
        }
        if(!$stmt->execute()){
            echo "Execute failed: "  . $stmt->errno . " " . $stmt->error;
        } else {
            echo "Added " . $stmt->affected_rows . " row to Heroes.";
        }
?>

Collectives™ on Stack Overflow

PHP code for inserting non-duplicate values into MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related