PHP MySQLi prepared statement not inserting data into table

Question

I am designing a website with user login and registration with email verification, and when the user registers an account, it will successfully, and correctly, insert the user record into the database in the "users" table.

But when I try to pull the users id that is created when inserted to insert them into a second table for a roster of all members of the site (this is for a gaming clan), it inserts the user id as 0 in the roster table, regardless of what their id is on the "users" table for that user.

heres my code:

if($insert_stmt = $db->prepare("INSERT INTO users (username, password, email, date, actcode) VALUES (?, ?, ?, ?, ?)"))
    {
        $insert_stmt->bind_param('sssss', $username, $password, $email, $date, $actcode);

        $stmt = $db->prepare("SELECT id FROM users WHERE email = ?");
        if($stmt)
        {
            $stmt->bind_param('s', $email);
            $stmt->execute();
            $stmt->fetch();
            $stmt->store_result();
            $stmt->bind_result($ui);
        }

        $stmt = $db->prepare("INSERT INTO roster (userid, joindate) VALUES (?, ?)");
        if($stmt)
        {
            $stmt->bind_param('ss', $ui, $date);
            $stmt->execute();
        }

        if(!$insert_stmt->execute()) {
            header('Location: error.php?err=Registration failure: INSERT');
        }
    }

for security reasons I have left certain variables and sections of the code omitted that do not have any bearing on this section causing me a headache.

I can't figure out why it is not inserting the newly creating "id" from "users" for that user account into the "roster" table under the "userid" column.

Also, just to test something, I also went and set a session variable

$_SESSION['uid'] = $ui;

directly after the line

$stmt->store_result($ui);

and echoed it on my index.php file, and it shows the session variable as 0 as well.

Are you sure that block of code is even executing? You should add some mysqli_error()s to see if anything is failing. It looks like everything would fail silently. — Mike
– Mike, Commented Apr 30, 2016 at 3:49
Can you add debug statements to your code ? What is the output of print_r($stmt); — Edward
– Edward, Commented Apr 30, 2016 at 4:08

Indrasis Datta · Accepted Answer · 2016-04-30 03:50:24Z

1

You could simply use this function: mysqli::$insert_id instead of writing your second query.

Replace this line:

$stmt = $db->prepare("SELECT id FROM users WHERE email = ?");

With:

$userid = $db->insert_id;

Let me know if this works.

answered Apr 30, 2016 at 3:50

Indrasis Datta

8,6182 gold badges18 silver badges32 bronze badges

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

3 Comments

spencer7593 Over a year ago

This assumes that the `id` column is AUTO_INCREMENT, and the value of the column isn't be set by a BEFORE INSERT trigger for example.

Edward Over a year ago

A user registration table should almost certainly have an ID column that is AUTO_INCREMENT.

Indrasis Datta Over a year ago

Yep, it's pretty obvious that the id column is auto increment. Unless a long character is inserted instead of a number as in enterprise web applications, which I doubt in this case.

Bryan · Accepted Answer · 2016-04-30 05:23:28Z

0

Still wasn't able to get it to work with the the solutions provided, but decided to just merge everything into the users table, which fixed the issue. As far as checking if it is executing, I am putting in some checks to make sure it goes through. Thanks for the help and advise.

answered Apr 30, 2016 at 5:23

Bryan

811 silver badge8 bronze badges

Collectives™ on Stack Overflow

PHP MySQLi prepared statement not inserting data into table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related