If statements for registering in mysql

Question

In my app, this PHP code should check for existing usernames and if one exists, don't create a new row in the database. No matter what, the app still creates new rows in the MySQL database.

Here is the code for checking:

<?php 

//$password = "password";
//$username = "username";
require "conn.php";
$password = $_POST["password"];
$username = $_POST["username"];
//$url_id = mysql_real_escape_string($_GET['username']);
$sql = "SELECT * FROM UserData WHERE username ='$username'";
$result = mysql_query($sql);

if(mysql_num_rows($result) >0){
   break;
}else{
   $stmt = $conn->prepare("INSERT INTO UserData (username,password) VALUES (:username,:password)");
$params = array(
    ':username' => $username,
    ':password' => $password
);
$stmt->execute($params);
}

?>

you **MUST NOT** use `mysql_xxx` functions which are deprecated since php5.5 (more than 3 years ago) and removed since PHP7 because of security issues (see http://stackoverflow.com/q/12859942/3992945). Please use `mysqli_xxx` or `PDO` instead http://php.net/manual/en/mysqlinfo.api.choosing.php. — ᴄʀᴏᴢᴇᴛ, Aug 03 '17 at 08:13
This is the wrong way to go about this to begin with. You want to make the username field UNIQUE in your database scheme, and then check if an error violating this constraint occurs when you try and insert a new record. — CBroe, Aug 03 '17 at 08:14
Check both the contents of `$sql` and the return value of `mysql_num_rows($result)`. Also the break statement doesn't really belong here; it's for use in loops. But it doesn't do anything at all, which is probably what you want. Also the advice from CROZET about using modern/supported mysql libraries is definitely something to heed — Lorna Mitchell, Aug 03 '17 at 08:15
You are mixing mysql and mysqli functions and also you mix object and procedural code — ᴄʀᴏᴢᴇᴛ, Aug 03 '17 at 08:17

score 0 · Answer 1 · answered Aug 03 '17 at 08:16

0

$results = $mysqli->query("SELECT * FROM UserData WHERE username ='$username'");
if ($results) { 
    if($results->num_rows === 0)
    {
        $stmt = $conn->prepare("INSERT INTO UserData (username,password) VALUES (:username,:password)");
        $params = array(
            ':username' => $username,
            ':password' => $password
        );
        $stmt->execute($params);
    }
}
?>

You should use mysqli and not mysql as it is deprecated
Secondly, you should check if($results->num_rows === 0) and do your INSERT

answered Aug 03 '17 at 08:16

Milan Chheda

8,159
3
20
35

But it crashes when it runs and doesn't add new rows. – Egor Cherkashin Aug 03 '17 at 08:27
What do you mean by _crashes_? And did you check `echo $results->num_rows`? Is that equal to 0? – Milan Chheda Aug 03 '17 at 08:29
We implemented your code into an Android app, and it crashes when there are no examples of previous usernames that match the current one. – Egor Cherkashin Aug 03 '17 at 08:33
Okay, so you can change it to `mysqli_query()` and check. Also, check the logs for errors. – Milan Chheda Aug 03 '17 at 08:40
Please be more specific. I am a beginner. – Egor Cherkashin Aug 03 '17 at 08:44
if ($result = $mysqli->query("SELECT * FROM UserData WHERE username ='$username' ", MYSQLI_USE_RESULT)){} – Egor Cherkashin Aug 03 '17 at 08:51
Yes, that's correct and I hope you have done proper mysql connection using `$mysqli = new mysqli("localhost", "my_user", "my_password", "world");` – Milan Chheda Aug 03 '17 at 08:52
What is the importance of "$mysqli = new mysqli("localhost", "my_user", "my_password", "world");". We used a separate php file name conn.php – Egor Cherkashin Aug 03 '17 at 08:56
That code connects your PHP to MySQL, you can have it separate file and include it in all remaining files. – Milan Chheda Aug 03 '17 at 08:57
conn.php code: PDO::ERRMODE_EXCEPTION)); if ($conn) { echo "Connected successfully"; } if (!$conn) { echo "no"; } ?> – Egor Cherkashin Aug 03 '17 at 08:58

If statements for registering in mysql

1 Answers1