How to Check If a Row Exists in MySQL (I.E. Check If Username or Email Exists in MySQL)

How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)

The following are tried, tested and proven methods to check if a row exists.

(Some of which I use myself, or have used in the past).

Edit: I made an previous error in my syntax where I used mysqli_query() twice. Please consult the revision(s).

I.e.:

if (!mysqli_query($con,$query)) which should have simply read as if (!$query).

  • I apologize for overlooking that mistake.

Side note: Both '".$var."' and '$var' do the same thing. You can use either one, both are valid syntax.

Here are the two edited queries:

$query = mysqli_query($con, "SELECT * FROM emails WHERE email='".$email."'");

if (!$query)
{
die('Error: ' . mysqli_error($con));
}

if(mysqli_num_rows($query) > 0){

echo "email already exists";

}else{

// do something

}

and in your case:

$query = mysqli_query($dbl, "SELECT * FROM `tblUser` WHERE email='".$email."'");

if (!$query)
{
die('Error: ' . mysqli_error($dbl));
}

if(mysqli_num_rows($query) > 0){

echo "email already exists";

}else{

// do something

}

You can also use mysqli_ with a prepared statement method:

$query = "SELECT `email` FROM `tblUser` WHERE email=?";

if ($stmt = $dbl->prepare($query)){

$stmt->bind_param("s", $email);

if($stmt->execute()){
$stmt->store_result();

$email_check= "";
$stmt->bind_result($email_check);
$stmt->fetch();

if ($stmt->num_rows == 1){

echo "That Email already exists.";
exit;

}
}
}

Or a PDO method with a prepared statement:

<?php
$email = $_POST['email'];

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_password = 'xxx';
$mysql_dbname = 'xxx';

try {
$conn= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
exit( $e->getMessage() );
}

// assuming a named submit button
if(isset($_POST['submit']))
{

try {
$stmt = $conn->prepare('SELECT `email` FROM `tblUser` WHERE email = ?');
$stmt->bindParam(1, $_POST['email']);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

}
}
catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}

if($stmt->rowCount() > 0){
echo "The record exists!";
} else {
echo "The record is non-existant.";
}


}
?>
  • Prepared statements are best to be used to help protect against an SQL injection.

N.B.:

When dealing with forms and POST arrays as used/outlined above, make sure that the POST arrays contain values, that a POST method is used for the form and matching named attributes for the inputs.

  • FYI: Forms default to a GET method if not explicity instructed.

Note: <input type = "text" name = "var"> - $_POST['var'] match. $_POST['Var'] no match.

  • POST arrays are case-sensitive.

Consult:

  • http://php.net/manual/en/tutorial.forms.php

Error checking references:

  • http://php.net/manual/en/function.error-reporting.php
  • http://php.net/manual/en/mysqli.error.php
  • http://php.net/manual/en/pdo.error-handling.php

Please note that MySQL APIs do not intermix, in case you may be visiting this Q&A and you're using mysql_ to connect with (and querying with).

  • You must use the same one from connecting to querying.

Consult the following about this:

  • Can I mix MySQL APIs in PHP?

If you are using the mysql_ API and have no choice to work with it, then consult the following Q&A on Stack:

  • MySql php: check if Row exists

The mysql_* functions are deprecated and will be removed from future PHP releases.

  • It's time to step into the 21st century.

You can also add a UNIQUE constraint to (a) row(s).

References:

  • http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
  • http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
  • How to check if a value already exists to avoid duplicates?
  • How add unique key to existing table (with non uniques rows)

check if username and email already exists in database

First of all, session_start() is called 2 times.
Remove the repeated call inside if ($password == $password2) {

You code is also missing a ; and some } (for properly closing your if conditions)

Now the solution:

Before you can process Database query's result, you need to connect to a DB and execute appropriate SQL command, only then you will get the result you want. Your code is missing this process.

Check my comments in your code below↓ and then check again in corrected code

if ($password == $password2) {
session_start(); // remove this repeated call
$password = ($password);
$sql="select * from account_info where (username='$username' or email='$email')";
if (mysqli_num_rows($res) > 0) { // $res isn't defined
$row = mysqli_fetch_assoc($res);
if ($username==$row['username'])
{
$_SESSION['message'] = "Username je vec registrovan";
}
else($email==$row['email']){ // `else` doesn't work this way, use `elseif`
$_SESSION['message'] = "Email je vec registrovan" // ; missing
}



Corrected Code:

if (isset($_POST['register_btn'])) {
$name = mysqli_real_escape_string($db, $_POST['name']);
$lastname = mysqli_real_escape_string($db, $_POST['lastname']);
$username = mysqli_real_escape_string($db, $_POST['username']);
$email = mysqli_real_escape_string($db, $_POST['email']);
$password = mysqli_real_escape_string($db, $_POST['password']);
$password2 = mysqli_real_escape_string($db, $_POST['password2']);

if ($password == $password2) {
$password = ($password);
$sql = "SELECT * FROM users WHERE (username='$username' OR email='$email')";
$res = mysqli_query($db, $sql); // you were calling $res but it wasn't defined; this connects to the DB and executes SQL and then assigns the result
if (mysqli_num_rows($res) > 0) {
$row = mysqli_fetch_assoc($res);
if ($username == $row['username']) {
$_SESSION['message'] = "Username je vec registrovan";
} elseif ($email == $row['email']) { // changed `else` to `elseif` to include the condition, `else` doesn't accept conditional checks
$_SESSION['message'] = "Email je vec registrovan"; // added ;
}
} else {
$sql = "INSERT INTO users (name, lastname, username, email, password) VALUES ('$name', '$lastname', '$username', '$email', '$password')";
if (mysqli_query($db, $sql)) {
// New record inserted
$_SESSION['message'] = "Sada si ulogovan";
$_SESSION['message'] = $username;
header("location: login.php");
} else {
echo("Error: " . mysqli_error($db));
}
}
} // required to close the password checking condition
else {
$_SESSION['message'] = "Ne podudaraju se lozinke!";
}
}

Suggestions:

  1. Use prepared statement instead of directly passing user provided input into SQL
    (critical, your current code is vulnerable to SQL injection)
  2. Use an IDE that supports PHP and offers syntax highlighting (Atom, Visual Studio Code, PhpStorm etc.)

How to Check if value exists in a MySQL table

With php you can use inbuilt mysqli_num_rows() function, php documentation

mysqli_result::$num_rows -- mysqli_num_rows — Gets the number of rows in a result

$q = "select name from table where name = '4'";
$r = $mysqli->query($q);
if ($r->num_rows > 0) {
echo "exists";
} else {
echo "not exists";
}

How to check for existence of a row with particular value on one of the columns in MySQL/PHP?

You need to fetch the row from the query result.

$row = mysqli_fetch_row($result);
if ($row[0]) {
echo "A row with current date does exist";
} else {
echo "A row with current date does not exist";
}

BTW, you're checking for an ID, not a date, so the messages don't seem to match the query.

You also had a typo in your code

if ($result = 1)

is assigning the variable, not testing it; comparisons use ==.

How to detect if a user exists in a database

You don't need a loop, since the query returns at most one row. If the user exists it will return that row, otherwise there won't be any rows, and your loop is never entered.

$row = mysqli_fetch_assoc($result);
if ($row) {
echo "The user exists";
} else {
echo "The user does not exist";
}

If you don't actually need the information about the user, there's no need to fetch it at all. Just fetch the count of existing rows.

$query = "SELECT COUNT(*) AS count FROM users WHERE username = ? OR email = ?";
$stmt = $db->prepare($query);
$stmt->bind_param("ss", $username, $email);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if ($row['count'] > 0) {
echo "The user exists";
} else {
echo "The user does not exist";
}


Related Topics



Leave a reply



Submit