INSERT INTO SQL php Function does not work

Question

I have managed to write a php script that checks if a username already exists in the database and only adds a new user if it does not already exist.

This is my php script:

<?php
require "init.php";

if(isset($_POST['username']) && isset($_POST['forename']) && isset($_POST['surname']) && isset($_POST['password'])){
    $username = $_POST['username'];
    $forename = $_POST['forename'];
    $username = $_POST['surname'];
    $password = $_POST['password'];

    $stmt = "SELECT username FROM users WHERE username = ?";
    $result = $dbcon -> prepare($stmt);
    $result->bind_param('s', $username);
    $result->execute();
    $result->bind_result($username);

    if($result->fetch()){
        echo "Can't add new user as it already exists!";
    }
    else{
        $stmt_two = "INSERT INTO users (username, forename, surname, password) 
                VALUES(?, ?, ?, ?)";
        $result_two = $dbcon -> prepare($stmt_two);
        $result_two->bind_param('ssss', $username, $forename, $surname, $password);
        $result_two->execute();
        $result_two->close();
        echo json_encode("Success");
    }
}
?>

I believe the records are not being inserted or being inserted intermittently due to the fact that I have more than one prepared statement. If I just do the INSERT INTO statement on its' own with the SELECT FROM statement - the records are added almost instantly.

Why is this and what is wrong with my code?

Thanks

Just to mention, you don't need to check if there is already a register of this username using PHP. You can set the username column with a UNIQUE constraint. The query will fail if they try to add the same username again. So your code will be simpler. — Phiter
– Phiter, Commented Feb 10, 2016 at 1:09
what do you mean slowly? how slow? i don't think this chunk of code can eat up too much resources, and just use either COUNT() or ->num_rows, its just two prepared statements, won't take that much time, how long does it take to do these things in your environment anyway? 15-30 seconds? — Kevin
– Kevin, Commented Feb 10, 2016 at 1:09
@Ghost It doesn't even insert the values into the database :(. It works fine when I don't have the select statement. Maybe you can help - I am just trying to add a username IF and ONLY IF it does not exist. Could I not do it in one statement? — Samuel Georgeszusz
– Samuel Georgeszusz, Commented Feb 10, 2016 at 1:12
@SamuelGeorgeszusz there you go, it doesn't work, not slow, those two things are different. just use ->num_rows > 0 by the way, isset can take multiple arguments, isset(x, y, z, ...) — Kevin
– Kevin, Commented Feb 10, 2016 at 1:16
@Ghost I still don't understand how to fix this error. If you don't mind, could you please edit my code — Samuel Georgeszusz
– Samuel Georgeszusz, Commented Feb 10, 2016 at 1:20

Kevin · Accepted Answer · 2016-02-10 02:22:30Z

4

Just as I have said in the comments, don't over complicate and just check the number of rows found. No need to fetch anything. You're just checking if that user exists anyway.

$stmt = "SELECT username FROM users WHERE username = ?";
$result = $dbcon->prepare($stmt);
$result->bind_param('s', $username);
$result->execute();
$result->store_result();

if($result->num_rows() > 0) { // if it exists

} else {
    // make your insertions
}

And another note:

isset can take multiple arguments:

if(isset($_POST['username'], $_POST['forename'], $_POST['surname'], $_POST['password'])) {
    // and so on
}

Edit: Another flavor (using COUNT() of MySQL):

$stmt = "SELECT COUNT(username) FROM users WHERE username = ?";
$result = $dbcon->prepare($stmt);
$result->bind_param('s', $username);
$result->execute();
$result->bind_result($count);
$result->fetch();

if($count > 0) { // exists

} else {
    // do something else
}

edited Feb 10, 2016 at 2:22

answered Feb 10, 2016 at 1:34

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

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

5 Comments

Samuel Georgeszusz Over a year ago

PERFECT!!! It works - and therefore I have given you an upvote and the accepted answer. Could you please explain why my code was not working before if you don't mind

Kevin Over a year ago

@SamuelGeorgeszusz it doesn't make sense to fetch anything, much easier to check the number of rows yielded, and always don't forget to invoke ->store_result() after execution.

Samuel Georgeszusz Over a year ago

Thanks, what does ->store_result() mean and why do I need to invoke it? I also noticed you didn't close() the connection - is there any reason why? I'm just curious

Kevin Over a year ago

@SamuelGeorgeszusz doesn't really need a closure for that prepared statement, after all its done, your script is done. you'll need to use ->store_result(), here's a pertinent topic on that php.net/manual/en/mysqli-stmt.num-rows.php

Samuel Georgeszusz Over a year ago

Thanks, this means a lot. I'm actually starting to like php! +1

Collectives™ on Stack Overflow

INSERT INTO SQL php Function does not work

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related