Check email exist with php mysqli

Question

I am new to mysqli i wanted to check if email already exists in database with php and mysqli

Here is what i have done so far:

ini.php

<?php
session_start();
require 'connect.php';
include 'user.func.php'; 
?>

connect.php

<?php
$con = new mysqli('host', 'user', 'password', 'db');
if($con->connect_errno > 0){
die('Sorry, We\'re experiencing some connection problems.'); 
}

?>

register.php

<?php
include 'ini.php';
?>

<form action="" method="post">
<p>Username:<br/><input type="text" name="reg_name" maxlength="50"  ></p>
<p>Email:<br/><input type="text" name="reg_email" size="35" maxlength="50"  ></p>
<p>Password:<br/><input type="password" name="reg_password" maxlength="50" ></p>
<p>Re-type Password:<br/><input type="password" name="reg_re_password" maxlength="20"></p>
<p><input type="submit" value="Register" ></p>
</form>

<?php
include 'validate.php';
?>

validate.php

<?php
if(isset ( $_POST['reg_name'],$_POST['reg_email'], $_POST['reg_password'] )) {

$errors = array();
$name = $_POST['reg_name'];
$email = $_POST['reg_email'];
$password = $_POST['reg_password'];
$re_password = $_POST['reg_re_password'];
if(empty($name) || empty($email) || empty($password) || empty($re_password)){
$errors[] = 'All fields are required';
 } else {

if(strlen($name) > 50 || strlen($email) > 50 || strlen($password) > 50) {
 $errors[] = 'One or more field has too long Characters';
}

if(filter_var($email, FILTER_VALIDATE_EMAIL) === FALSE && $password !== $re_password){
$errors[] = 'Enter valid email address and password do not match';
}else{

if(filter_var($email, FILTER_VALIDATE_EMAIL) === FALSE) {
$errors[] = 'Please enter a valid email address';
}

if($password !== $re_password){
$errors[] = 'Both passwords do not match';
}
if(user_exists($email) > 0){
$errors[] = 'That email is already been registered';
}

}

}

if (!empty($errors)) {
foreach ($errors as $errors) {
    echo '<strong>',$errors,'</srtong><br />';
}
}else{
echo 'Registered';
}
}

?> user.func.php

<?php    
function user_exists($email) {

$con = new mysqli('host', 'user', 'password', 'db');
$query = "SELECT * FROM users WHERE email = ?";
$stmt = $con->prepare($query);
$stmt->bind_param("s", $email);

if ($stmt->execute()) {
return $stmt->num_rows;
}
return false;
}
?>

This code shows all the other errors but doesn't check if the email exist. Instead it shows registered even when i enter email that is already in database.

I have database named 'test'. And table 'users' with columns user_id, username, email and password.

Can anyone tell how to code mysqli query for this?

I used this code in mysql

function user_exists($email) {
$email = mysql_real_escape_string($email);
$query = mysql_query("SELECT COUNT('user_id') FROM 'users' WHERE 'email'='$email'");
return (mysql_result($query, 0) == 1) ? true :false;
}

`return $stmt->num_rows` returns an integer, not a strict `true`/`false`. Consider Yogesh's answer. — Passerby, Apr 26 '13 at 07:15
Extending from comments in Yogesh's answer: Do you have a MySQLi connection working in your program? Where did you declare it? — Passerby, Apr 26 '13 at 08:29
And how did you declare the connection object? What's the variable name, and is it accessible in global space? — Passerby, Apr 26 '13 at 08:46
Your edited version at this point seems fine, except that you don't have to `$con=new mysqli(...);` in `user_exists`. Just `global $con;` can work. — Passerby, Apr 26 '13 at 09:02
its says registered and i want it to say That email is already been registered — Kishore, Apr 26 '13 at 09:05

Yogesh Suthar · Answer 1 · 2013-04-26T07:51:13.347

1

Use this condition, because you are returning the number of rows from your query.

if(user_exits($register_email) > 0){

Edit

function user_exists($email) {
    $mysqli = new mysqli("host", "my_user", "my_password", "your_db");
    $query = "SELECT COUNT(*) AS num_rows FROM users WHERE email = ?";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("s", $email);

    if ($stmt->execute()) {
        return $stmt->num_rows;
    }
    return false;
}

edited Apr 26 '13 at 07:51

answered Apr 26 '13 at 07:14

Yogesh Suthar

30,424
18
72
100

@Kishore Should `user_exits($register_email)` be `user_exists($email)`? – Passerby Apr 26 '13 at 07:21
@Kishore user_exi **S** ts, not user_exits. Is that a typo? – Passerby Apr 26 '13 at 07:26
@YogeshSuthar i get these two errors Notice: Use of undefined constant USER_TABLE - assumed 'USER_TABLE' in F:\XAMPP\htdocs\reg_test\user.func.php on line 19 Fatal error: Using $this when not in object context in F:\XAMPP\htdocs\reg_test\user.func.php on line 21 – Kishore Apr 26 '13 at 07:35
@Kishore is `USER_TABLE` defined using `define()`? – Yogesh Suthar Apr 26 '13 at 07:37
@Kishore you can't use `$this` without using `class`. In your code it should be your `mysqli` object. – Yogesh Suthar Apr 26 '13 at 07:40
@YogeshSuthar can you tell briefly what should i do? – Kishore Apr 26 '13 at 07:42
@Kishore Despite that error, you function will still not work, because you're querying `COUNT(*)`, which will always return one row (the total count), so `$stmt->num_rows` will always be `1`, even if the count is `0`. You'll either need to get the result and check the value, or query `SELECT * FROM table WHERE email=?`. – Passerby Apr 26 '13 at 07:43
@Passerby i changed my query as you said and what should i do – Kishore Apr 26 '13 at 07:47
@YogeshSuthar it says registered but i already have that email address in database – Kishore Apr 26 '13 at 07:54
@Kishore Try some debugging like what `user_exists` returns when email is present and when not. – Yogesh Suthar Apr 26 '13 at 07:57
@YogeshSuthar i have added the code i used in mysql in my question – Kishore Apr 26 '13 at 08:15
@Passerby help for this query – Kishore Apr 26 '13 at 08:25

score 1 · Answer 2 · edited May 23 '17 at 12:18

1

I could tell how to do it with PDO

<?php
function user_exists($email) {
    $query = "SELECT 1 FROM " . USER_TABLE . " WHERE email = ?";
    $stmt = $this->_db->prepare($query);
    $stmt->execute(array($email));
    return (bool)$stmt->fetchColumn();
}

Though I see no point in strict checking

if(user_exits($register_email))

would be more than enough

Also, I don't understand where did you get that $register_email variable. Do you have error reporting on?

By the way, you need an established PDO connection for this. This code won't work with mysqli

edited May 23 '17 at 12:18

Community

1
1

answered Apr 26 '13 at 07:14

Your Common Sense

156,878
40
214
345

It is $email not $register_email i changed it but no result – Kishore Apr 26 '13 at 07:24
it's because you are using mysqli, not PDO. And don't have error reporting on – Your Common Sense Apr 26 '13 at 07:25
it shows errors if no fields are filled but doesnt check email exists – Kishore Apr 26 '13 at 07:27
That's what you did wrong. It should be not 0 but E_ALL and in php.ini it have to be `error_reporting = E_ALL` – Your Common Sense Apr 26 '13 at 08:17
i dont want the code to show NOTICE or FATAL ERROR it needs to check the database and say "That email is already been registered". – Kishore Apr 26 '13 at 08:20
if you don't want to **show** it, set `display_errors = 0` instead. But while you are the only user of the site, you have to see errors. otherwise you will be unable to make your code work at all. So, set both `error_reporting = E_ALL` and `display_errors = 1` for a while. When your site goes live, change it to `display_errors = 0` and `log_errors = 1`. But `error_reporting` have to be **always** E_ALL – Your Common Sense Apr 26 '13 at 08:49

Check email exist with php mysqli

2 Answers2

Linked

Related