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);
        }
    }
?>

Possible duplicate of [check if SQL row exists with PHP](https://stackoverflow.com/questions/6620019/check-if-sql-row-exists-with-php) — Vishnu Bhadoriya, Mar 23 '18 at 08:32
`mysqli_query` returns `false` only on failure, not just because it returns an empty set. — Federico klez Culloca, Mar 23 '18 at 08:32
Please also look up prepared statements (your queries are prone to SQL injection). — Jeto, Mar 23 '18 at 08:33
mysqli_query returns a mysqli_result object for SELECT-queries and your task is to check if that object contains at least one row. — Flocke, Mar 23 '18 at 08:34
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., Mar 23 '18 at 08:34
@N.B. Does that happens in a real world application where users registers and both queries (SELECT and INSERT) are ecxecuted that close? Theoretically YES, but your users have to do that by purpose and need a lot of luck. Therefore, I don't use unique constr. for usernames. Maybe a user deletes his account (and you only mark him as deleted in the db) then new users can't use that username. — Flocke, Mar 23 '18 at 08:40
@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., Mar 23 '18 at 08:58

Oleksandr Khomutetsky · Answer 1 · 2018-03-23T08:58:35.583

<?
    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);
        }
    }
?>

Your answer is vulnerable against sql injection attacks. Please teach proper techniques. — Joshua, Mar 23 '18 at 08:42
this is the output 0 if ($data['total']>0) { echo "user already exists"; mysqli_close($dbconn); }else{ mysqli_query($dbconn, $query); } } ?> — user2589992, Mar 23 '18 at 09:09

score -2 · Answer 2 · answered Mar 23 '18 at 08:55

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);
        }
    }

check your query on phpmyadmin or show me your db structure of table username — saqib kifayat, Mar 23 '18 at 09:11

Avos · Answer 3 · 2018-03-23T08:47:33.043

-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 '18 at 08:47

answered Mar 23 '18 at 08:39

Avos

24
10

1

Your answer is vulnerable against sql injection attacks. Please teach proper techniques. – Joshua Mar 23 '18 at 08:42
you are trying to insert a new user if the user already exists - might be wrong – niyou Mar 23 '18 at 08:42
1

that edit does not help - number of rows wont be < 0 – niyou Mar 23 '18 at 08:45

Select then Insert PHP query

3 Answers3