Select then Insert PHP query

Question

Ho can I check the database first if a user exists then use a insert statement if it does not. The code currently only executes the select statement.

<?php
    include_once('includes/dbconn.php');

    if (isset($_POST['submitted'])) {
        $fname = $_POST['fname'];
        $lname = $_POST['lname'];
        $user = $_POST['user'];
        $pass = $_POST['pass'];

        $query_check_user = "SELECT username FROM Users WHERE username = '$user'";
        $query = "INSERT INTO Users(firstname, lastname, username, password) VALUES ('$fname','$lname','$user','$pass')";

        if (mysqli_query($dbconn, $query_check_user)) {
            echo "user already exists";
            mysqli_close($dbconn);
        }else{
            mysqli_query($dbconn, $query);
        }
    }
?>

mysqli_query returns false only on failure, not just because it returns an empty set. — Federico klez Culloca
– Federico klez Culloca, Commented Mar 23, 2018 at 8:32
Please also look up prepared statements (your queries are prone to SQL injection). — Jeto
– Jeto, Commented Mar 23, 2018 at 8:33
Performing a check if a record exists with SELECT is bad because it's not accurate information. By the time you're done checking, another process can insert that record and you can end up with 2 records that are the same. To fight this problem, we use unique constraints, we simply insert and if database reports duplicate key error then we know a record exists. — N.B.
– N.B., Commented Mar 23, 2018 at 8:34
@Flocke it's not a case of "does it happen", it's the case of creating a system where there are no holes such as this one. It might not happen today, tomorrow, in 6 months. What if it happens in 2 years? However, the interesting bit is that creating a unique constraint makes it easier for us as we have to type way, way less code. And it's bulletproof, forever. Regarding soft-deletes, you can always create a unique constraint out of (username, is_deleted) or hash (username, is_deleted) and save that as unique constraint. — N.B.
– N.B., Commented Mar 23, 2018 at 8:58

Oleksandr Khomutetsky · Accepted Answer · 2018-03-23 08:58:35Z

0

<?
    include_once('includes/dbconn.php');

    if (isset($_POST['submitted'])) {
        $fname = $_POST['fname'];
        $lname = $_POST['lname'];
        $user = $_POST['user'];
        $pass = $_POST['pass'];

        //Query for count
        $query_check_user = "SELECT count(*) as total FROM Users WHERE username = '$user'";
        $query = "INSERT INTO Users(firstname, lastname, username, password) VALUES ('$fname','$lname','$user','$pass')";
        //Execute query for count
        $result = mysqli_query($dbconn, $query_check_user);
        //Fetch result
        $data = mysqli_fetch_assoc($result);
        //Check if count >0
        if ($data['total']>0) {
            echo "user already exists";
            mysqli_close($dbconn);
        }else{
            mysqli_query($dbconn, $query);
        }
    }
?>

edited Mar 23, 2018 at 8:58

answered Mar 23, 2018 at 8:38

Oleksandr Khomutetsky

665 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joshua Over a year ago

Your answer is vulnerable against sql injection attacks. Please teach proper techniques.

user2589992 Over a year ago

this is the output 0 if ($data['total']>0) { echo "user already exists"; mysqli_close($dbconn); }else{ mysqli_query($dbconn, $query); } } ?>

saqib kifayat · Accepted Answer · 2018-03-23 08:55:21Z

-2

you can use mysqli_num_rows(); to check the number if result if it is greater then 0 then user exist else insert user data.

my example :

include_once('includes/dbconn.php');

    if (isset($_POST['submitted'])) {
        $fname = $_POST['fname'];
        $lname = $_POST['lname'];
        $user = $_POST['user'];
        $pass = $_POST['pass'];

        $query_check_user = "SELECT username FROM Users WHERE username = '$user'";
        $query_result = mysqli_query($query_check_user);
        $query = "INSERT INTO Users(firstname, lastname, username, password) VALUES ('$fname','$lname','$user','$pass')";

        if (mysqli_num_rows($query_result) > 0) {
            echo "user already exists";
            mysqli_close($dbconn);
        }else{
            mysqli_query($dbconn, $query);
        }
    }

answered Mar 23, 2018 at 8:55

saqib kifayat

1662 silver badges14 bronze badges

2 Comments

user2589992 Over a year ago

does not work. adds a user even if the username exists

saqib kifayat Over a year ago

check your query on phpmyadmin or show me your db structure of table username

Avos · Accepted Answer · 2018-03-23 08:47:33Z

-3

as I get from your question is, you want to insert the user if the user doesn't exist, right?

$query_check_user = "SELECT username FROM Users WHERE username = '$user'";
$b = mysqli_query($dbconn,$query_check_user);
$a = mysqli_num_rows($b);
if($a<0):
   mysqli_query(dbconn, "INSERT INTO Users(firstname, lastname, username, password) VALUES ('$fname','$lname','$user','$pass')");
endif;

edited Mar 23, 2018 at 8:47

answered Mar 23, 2018 at 8:39

Avos

2410 bronze badges

3 Comments

Joshua Over a year ago

Your answer is vulnerable against sql injection attacks. Please teach proper techniques.

niyou Over a year ago

you are trying to insert a new user if the user already exists - might be wrong

niyou Over a year ago

that edit does not help - number of rows wont be < 0

Collectives™ on Stack Overflow

Select then Insert PHP query

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related