As said, I recommend using the PDO extension. But if you choose to use mysqli instead, then use the object-oriented mysqli instead of the procedural one. On php.net each mysqli function is presented in both ways.
The password should be strongly encrypted. My recommendation: the password_hash function - either with the PASSWORD_BCRYPT option (a constant defining the Blowfish hashing algorithm), or with the PASSWORD_ARGON2I option (constant defining the Argon2 hashing algorithm and introduced as of PHP 7.2.0). So, you should first save new user credentials in the form of a password hash (a string of minimum 60 aleatory characters) - in the users table. With a code similar to this one:
signup.php:
$username = $_POST['username'];
$password = $_POST['password'];
// Create a hash from a posted password.
$passwordHash = password_hash($password, PASSWORD_BCRYPT);
$sql = 'INSERT INTO users (username, password) VALUES (?, ?)';
$statement = $connection->prepare($sql);
$statement->bind_param('ss', $username, $passwordHash);
$statement->execute();
//...
For a clear view regarding your question here's an extended example of a login page - using my own naming and coding conventions. How you decide to adapt it, e.g. to process the results, is up to your logical scheme and system - I'm not familiar with Android. The code contains a server-side credentials validation part too. For proper error reporting see this article. Don't forget to change the db credentials.
connection.php:
<?php
/*
* This page contains the code for creating a mysqli connection instance.
*/
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception).
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/*
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
*/
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
login.php:
<?php
require 'connection.php';
/*
* ================================
* Operations upon form submission.
* ================================
*/
if (isset($_POST['submit'])) {
/*
* =======================
* Read the posted values.
* =======================
*/
$username = isset($_POST['username']) ? $_POST['username'] : '';
$password = isset($_POST['password']) ? $_POST['password'] : '';
/*
* ===========================
* Validate the posted values.
* ===========================
*/
// Validate the username.
if (empty($username)) {
$errors[] = 'Please provide a username.';
} /* Other validations here using elseif statements */
// Validate the password.
if (empty($password)) {
$errors[] = 'Please provide a password.';
} /* Other validations here using elseif statements */
/*
* ======================
* Check the credentials.
* ======================
*/
if (!isset($errors)) { // No errors yet.
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT username, password
FROM users
WHERE username = ?
LIMIT 1';
/*
* Prepare the SQL statement for execution.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to prepare(). The first
* argument of bind_param() is a string that contains one
* or more characters which specify the types for the
* corresponding bind variables.
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$statement->bind_param('s', $username);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
$statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
/*
* Fetch the credentials into an associative array.
* If no record is found, the operation returns NULL.
*/
$credentials = $result->fetch_array(MYSQLI_ASSOC);
if (isset($credentials) && $credentials) { // Record found.
$fetchedUsername = $credentials['username'];
$fetchedPasswordHash = $credentials['password'];
/*
* Compare the posted username with the one saved in db and the posted
* password with the password hash saved in db using password_hash.
*
* @link https://secure.php.net/manual/en/function.password-verify.php
* @link https://secure.php.net/manual/en/function.password-hash.php
*/
if (
$username === $fetchedUsername &&
password_verify($password, $fetchedPasswordHash)
) {
header('Location: welcome.html');
exit();
} else {
$errors[] = 'Invalid credentials. Please try again.';
}
} else {
$errors[] = 'No credentials found for the given user.';
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Demo - Login</title>
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#username').focus();
});
function validateForm() {
return true;
}
</script>
<style type="text/css">
body {
padding: 30px;
}
label {
display: block;
font-weight: 400;
}
input[type="text"],
input[type="password"] {
display: block;
margin-bottom: 20px;
}
button {
display: block;
padding: 7px 10px;
background-color: #8daf15;
color: #fff;
border: none;
}
.messages {
margin-bottom: 20px;
}
.messages .error {
color: #c00;
}
</style>
</head>
<body>
<div class="messages">
<?php
if (isset($errors)) {
foreach ($errors as $error) {
?>
<div class="error">
<?php echo $error; ?>
</div>
<?php
}
}
?>
</div>
<div class="form-login">
<form name="credentials" action="" method="post" onsubmit="return validateForm();">
<label for="username">Username:</label>
<input type="text" id="username" name="username" value="<?php echo isset($username) ? $username : ''; ?>">
<label for="password">Password:</label>
<input type="password" id="password" name="password" value="<?php echo isset($password) ? $password : ''; ?>">
<button type="submit" name="submit" value="submit">
Submit
</button>
</form>
</div>
</body>
</html>
The table structure used for testing:
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(100) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The data used for testing:
This data would be saved by running the signup.php code. Each password hash here (e.g. each value of the password column) is the encrypted representation of the corresponding value of the username column. For example, the first hash represents the string (e.g. the password) "demo1".
INSERT INTO `users` (`id`, `username`, `password`)
VALUES
(1, 'demo1', '$2y$10$ZzULeTfsMwBj6DwpsfzxPu0irOrkL.l7rkimPkpcojL4RAMLwEZkW'),
(2, 'demo2', '$2y$10$bpLOz4ur4wdVs4RN9ZatGekmynMhgOAdkwBchRLAf2t8hwc9Kkh7K');
