Check If SQL Row Exists with PHP

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 row exists, The most efficient way?

By best I guess you mean consuming the least resources on both MySQL server and client.

That is this:

      SELECT COUNT(*) count FROM table WHERE id=1

You get a one-row, one-column result set. If that column is zero, the row was not found. If the column is one, a row was found. If the column is greater that one, multiple rows were found.

This is a good solution for a few reasons.

COUNT(*) is decently efficient, especially if id is indexed.
It has a simple code path in your client software, because it always returns just one row. You don't have to sweat edge cases like no rows or multiple rows.
The SQL is as clear as it can be about what you're trying to do. That's helpful to the next person to work on your code.

Adding LIMIT 1 will do nothing if added to this query. It is already a one-row result set, inherently. You can add it, but then you'll make the next person looking at your code wonder what you were trying to do, and wonder whether you made some kind of mistake.

COUNT(*) counts all rows that match the WHERE statement. COUNT(id) is slightly slower because it counts all rows unless their id values are null. It has to make that check. For that reason, people usually use COUNT(*) unless there's some chance they want to ignore null values. If you put COUNT(id) in your code, the next person to work on it will have to spend some time figuring out whether you meant anything special by counting id rather than *.

You can use either; they give the same result.

Check if a row exists using old mysql_* API

This ought to do the trick: just limit the result to 1 row; if a row comes back the $lectureName is Assigned, otherwise it's Available.

function checkLectureStatus($lectureName)
{
    $con = connectvar();
    mysql_select_db("mydatabase", $con);
    $result = mysql_query(
        "SELECT * FROM preditors_assigned WHERE lecture_name='$lectureName' LIMIT 1");

    if(mysql_fetch_array($result) !== false)
        return 'Assigned';
    return 'Available';
}

how to check if a record exists in Mysql table

You are mixing mysql and mysqli

Use mysqli_query

Check if a row exists in database and if not display a message

if you want to show different links based on if client_cv has a value or not, try this

<?php
    $result = mysqli_query($mysqli, "SELECT client_cv FROM formular_client WHERE id = '$id'"); 
    if (mysqli_num_rows($result) > 0){
        $rows = mysqli_fetch_array($result);
        if (strlen($rows["client_cv"]) > 0){
            echo "<a href='../cv/" . $rows['client_cv'] . "' target='_blank' class='btn btn-lg btn-primary'><span class='glyphicon glyphicon-download-alt'></span> Descarca CV</a>";
        } else {
            echo "<a href='#' target='_blank' class='btn btn-lg btn-primary'><span  class='glyphicon glyphicon-download-alt'></span> Fara CV</a>";
        }
    } else {
        echo "No record found"
    }
?>

Check if row exists in the database before inserting

It's better to set a constraint on your columns to prevent duplicate data instead of checking and inserting.

Just set a UNIQUE constraint on imdbid:

ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);

The reason for doing this is so that you don't run into a race condition.

There's a small window between finishing the check, and actually inserting the data, and in that small window, data could be inserted that will conflict with the to-be-inserted data.

Solution? Use constraints and check $DBH->error() for insertion errors. If there are any errors, you know that there's a duplicate and you can notify your user then.

I noticed that you are using this, $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. In this case, you don't need to check ->error() because PDO will throw an exception. Just wrap your execute with try and catch like this:

$duplicate = false;

try {
    $STH->execute();
} catch (Exception $e) {
    echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
    $duplicate = true;
}

if (!$duplicate)
    echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";

Simple mysql Query to check if row exist

There are a lot of ways of doing this really but if you arnt going to use any more information then weither or not the user has liked it doing select * is a bad idea. The reason why is that you are asking the database to return the value of every column in that table.

Assuming its a small database its probably not a problem no but as your database gets bigger you are puting more load on it then you need you should try and only select the columns you need and intend to use. Ok in this case the userid is probably indexed and its only one row, but if you get in the habit of doing it here you may do it else where as well.

try this instead.

$userid=$_COOKIE['userid'];
$sql = "SELECT count(user_id) as total FROM likes WHERE `user_id`='{$userid}'"; 
$query = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($query);

if( $row ['total'] > 0){
    echo "unlike";
}
else{
    echo "like";
}

This way we are just getting the total. simple and elegant

WordPress and PHP | Check if row exists in database if yes don't insert data

fetch data using this code

$query = "SELECT * FROM {$wpdb->prefix}table WHERE column = 1";
echo $query;
$results = $wpdb->get_results($query);

and then you know what to do...

check if the row exists or not in the database

the answer was to use the count function like that :

public function postIndex(Request $request){

        $id=$request->input('id');
        $user_id=Auth::user()->id;
        $product_id =$request['product_id'];

        $wishlist=DB::table('wishlist')
                    ->where('user_id','=',$user_id)
                    ->where('product_id','=',$product_id)
                    ->count();

        if($wishlist > 0){
            return redirect('wishlist');
        }
        else{
            DB::table('wishlist')
              ->insert(['id'=>$id,'user_id'=>$user_id,'product_id'=>$product_id]);
            return redirect('wishlist');
        }
}