Check If Row Exists in the Database Before Inserting

How to check if row exists or not before insertion?

This is the recommended way to use rawQuery():

String selectQuery = "SELECT * FROM " + TABLE_TEAM + " WHERE " + TEAM_TABLE_NAME + " = ?";
Cursor c = db.rawQuery(selectQuery, new String[] {name});

so you pass name as a parameter to the query and avoid the risk of sql injection.

In your code you only check if the Cursor object is null and not if it contains any rows which is the right thing to do.

So return the result of getCount() > 0 like this:

return c.getCount() > 0;

This will return true if the name already exists in the table.

As for the no such column error, this is something different.

First make sure that TEAM_TABLE_NAME is the correct name of the column and if it is then maybe you made changes to the names of the columns tha are not reflected to the database.

So uninstall the app from the device so the database is deleted and then rerun to recreate the database with the new names of the columns.

Stored procedure to find out if row exists before insertion

Why use procedural logic? Make it fully set based. Of course you need to explain where @SS_ID comes from since it doesn't exist in your code snippet.

INSERT INTO SS_ApplicationStatus (CustomerID, EventType, EventDateTime, Comments, EmployeeID, ApplicationDate)
    SELECT @CustomerID, @EventType, @EventDateTime, @Comments, @EmployeeID, @ApplicationDate
    WHERE NOT EXISTS (SELECT 1 FROM [dbo].[SS_CustomerCard] WHERE SS_ID = @SS_ID);

Note in SQL Server:

the keyword is EXISTS not EXIST
you don't quote parameters, so it should be @SS_ID not '@SS_ID'

check if identical row exists before insert into database table

The correct way to do this is to have the database do the check. That is, let the database maintain the integrity of the data.

So, first create a unique index:

create unique index unq_t_userid_connectionid on tbl_current_userconnections(user_id, connection_id);

Then, any attempt to insert an existing value would result in an error. If you don't want an error, then use:

INSERT INTO tbl_current_userconnections(user_id, connection_id) 
    VALUES ('$activeID', '$crewmemberID'),
           ('$crewmemberID', '$activeID')
    ON DUPLICATE KEY UPDATE user_id = VALUES(user_id);

The ON DUPLICATE KEY does nothing to the data, but it prevents an error from being reported.

Why is this better? The problem with doing the check in the application is that two users might attempt to create the same link(s) at the same time. This can result in a race condition where the IF passes for both users, so both insert the data. That is one important reason why doing the logic in the database is important.

Also, you should learn to use parameterized queries and not pass values directly into query statements. Your approach is dangerous and can lead to unexpected syntax errors.

Check if record exists then insert new row in database table?

I don't know/work-with coldfusion so not sure I'm reading the logic correctly ...

if record does not exist in table1 but
record does exit in contact then
insert a row into inter_work_tbl

The general T-SQL query would look like (note: mixing T-SQL with references to the coldfusion variables):

insert into inter_work_tbl

      (user_id
      ,first_name
      ,last_name
      ,password)

select '#session.user_id#',
       c.fname,
       c.lname,
       '#password#'

from   contact c

where  c.userid = #session.user_id#
and    not exists(select  1
                  from    table1 t
                  where   t.user_id = c.userid)

mysql/mariadb, is it a bad idea to check if row exists by trying to insert a new row and checking for errors?

If the hashed message has to be unique, create a key on that column with the UNIQUE constrain: so there won't be two rows with the same hash.

Then, when you insert a new row modify your query with the following:

INSERT INTO table SET message='$message', hashed_message='$hashed_message'
ON DUPLICATE KEY id=id;

This will perform an insert if the hashed_message is unique. Otherwise will not do any update.

If you want to update something in case of duplicate your query will become:

INSERT INTO table SET message='$message', hashed_message='$hashed_message'
ON DUPLICATE KEY UPDATE message='$updated_message'

just to make an example.

Note that this method won't raise any exception in case of duplicate values: you need extra logic if you need to perform actions in your frontend in case of duplicates (i.e. message shown to the user).

More details here

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>";

How do I check if name exists in table before inserting into MySQL

You can simplify your code a bit - the last select-statement is redundant, as you already have the information before the query. You should also be using a prepared statement instead of using real_escape_string().

<?php 

$stmt = $conn->prepare("SELECT name FROM areas WHERE idCampus = ?");
$stmt->bind_param("s", $_POST["campus_area_fk"]);
$stmt->execute();
$exists = $stmt->fetch();
$stmt->close();

if ($exists)
    echo "Area name already exists";
} else {
    $stmt = $conn->prepare("INSERT INTO areas (name, idCampus) VALUES (?, ?)");
    $stmt->bind_param("ss", $_POST["area_name"], $_POST["campus_area_fk"]);
    $stmt->execute();

    echo json_encode(['area_name_retrieve' => $_POST["area_name"], 'area_id_retrieve' => $stmt->insert_id]);
    $stmt->close();
}

Beware that this can cause race-conditions - you should instead put the name as a unique constraint, and try to insert it without doing any select query first. Then check against the errorcode you got back to see if it existed or not.

How to test is row exists in the database

Here are a few things you could do to make it work:

Prevent SQL injection

As this is an important issue, and the suggested corrections provided below depend on this point, I mention it as the first issue to fix:

You should use prepared statements instead of injecting user-provided data directly in SQL, as this makes your application vulnerable for SQL injection. Any dynamic arguments can be passed to the SQL engine aside from the SQL string, so that there is no injection happening.

Reduce the number of queries

You do not need to first query whether the user has already a bio record. You can perform the update immediately and then count the records that have been updated. If none, you can then issue the insert statement.

With the INSERT ... ON DUPLICATE KEY UPDATE Syntax, you could further reduce the remaining two queries to one. It would look like this (prepared):

INSERT INTO user_bio(age, studying, language,
                     relationship_status, username, about_me) 
         VALUES (?, ?, ?, ?, ?, ?)
  ON DUPLICATE KEY 
  UPDATE studying = VALUES(studying), 
         language = VALUES(language), 
         relationship_status = VALUES(relationship_status), 
         about_me = VALUES(about_me);

This works only if you have a unique key constraint on username (which you should have).

With this statement you'll benefit from having the data modification executed in one transaction.

Also take note of some considerations listed in the above mentioned documentation.

NB: As in comments you indicated that you prefer not to go with the ON DUPLICATE KEY UPDATE syntax, I will not use it in the suggested code below, but use the 2-query option. Still, I would suggest you give the ON DUPLICATE KEY UPDATE construct a go. The benefits are non-negligible.

Specify the columns you insert

Your INSERT statement might have failed because of:

the (empty) string value you provided for what might be an AUTO_INCREMENT key, in which case you get an error like:

Incorrect integer value: '' for column 'id'
a missing column value, i.e. when there are more columns in the table than that you provided values for.

It is anyway better to specify explicitly the list of columns in an INSERT statement, and to not include the auto incremented column, like this:

INSERT INTO user_bio(age, studying, language,
                     relationship_status, username, about_me) 
VALUES (?, ?, ?, ?, ?, ?)

Make sure you get notified about errors

You might also have missed the above (or other) error, as you set your error reporting options only after having executed your queries. So execute that line before doing any query:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

And also add there:

error_reporting(E_ALL);
ini_set('display_errors', 1);

In a production environment you should probably pay some more attention to solid error reporting, as you don't want to reveal technical information in the client in such an environment. But during development you should make sure that (unexpected) errors do not go unnoticed.

Do not store HTML entities in the database

It would be better not to store HTML entities in your database. They are specific to HTML, which your database should be independent of.

Instead, insert these entities (if needed) upon retrieval of the data.
In the below code, I removed the calls to htmlentities, but you should then add them in code where you SELECT and display these values.

Separate view from model

This is a topic on its own, but you should avoid echo statements that are inter-weaved with your database access code. Putting status in variables instead of displaying them on the spot might be a first step in the right direction.

Suggested code

Here is some (untested) code which implements most of the above mentioned issues.

// Calls to htmlentities removed:
$about_me = trim(strip_tags(@$_POST['biotextarea']));
$new_studying = trim(strip_tags(@$_POST['studying']));
$new_lang = trim(strip_tags(@$_POST['lang']));
$new_rel = trim(strip_tags(@$_POST['rel']));
// Set the error reporting options at the start
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
if (isset($_POST['update_data'])) {
    // Do not query for existence, but make the update immediately 
    $update_stmt = mysqli_prepare ($connect,
            "UPDATE user_bio 
             SET    studying = ?, 
                    language = ?, 
                    relationship_status = ?, 
                    about_me = ? 
             WHERE  username = ?");
    mysqli_stmt_bind_param($update_stmt, "sssss", 
            $new_studying, $new_lang, $new_rel, $about_me, $username);
    mysqli_stmt_execute($update_stmt);
    $num_updated_rows = mysqli_stmt_affected_rows($update_stmt);
    mysqli_stmt_close($update_stmt);
    if ($num_updated_rows === 0) {
        $insert_stmt = mysqli_prepare ($connect,
                "INSERT INTO user_bio(age, studying, language,
                             relationship_status, username, about_me) 
                 VALUES (?, ?, ?, ?, ?, ?)");
        mysqli_stmt_bind_param($insert_stmt, "isssss", 
                $age, $new_studying, $new_lang, $new_rel, $username, $about_me);
        mysqli_stmt_execute($insert_stmt);
        mysqli_stmt_close($insert_stmt);
    }
    // Separate section for output
    $result = $num_updated_rows ? "Details updated successfully!"
                                : "Details added successfully!";
    echo "  <div class='details_updated'><p>$result</p></div>";
}