CODEWITHSUNDEEP
phpmysqlimysql-insert-idinsert-id
Cedric Woo
Cedric Woo

Reputation: 39

Value of insert_id keeps returning 0

I'm trying to create a register form where I wish to let the user know the value of his ID. I'm able to successfully register the user but the insert_id keeps returning 0 even though there's multiple rows in the database.

Database

public function database()
{
    $this->servername = "localhost";
    $this->username = "root";
    $this->password = "";
    $this->dbname = "dba3";

    $conn = new mysqli($this->servername, $this->username,$this->password,$this->dbname);
    
    if($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $this->tablename = "User";
    $checktable = $conn->query("SHOW TABLES LIKE '$this->tablename'");
    $table_exists = $checktable->num_rows >= 1;

    if(!$table_exists) {
        $sql = "CREATE TABLE $this->tablename( 
                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                firstname VARCHAR(30) NOT NULL,
                lastname VARCHAR(30) NOT NULL,
                phone VARCHAR(20) NOT NULL,
                email VARCHAR(50) NOT NULL,
                type VARCHAR(20) NOT NULL)";

        if($conn->query($sql)===TRUE){
            echo "Table sucessfully created";
        } else {
            echo "Error creating table: " . $conn->error;
        }
    }
    return $conn;
    $conn->close();
}

PHP Code

public function checkEmpty($fname, $lname, $num, $email, $gettype)
{
    $this->fname = $fname;
    $this->lname = $lname;
    $this->num = $num;
    $this->email = $email;
    $this->gettype = $gettype;
    $sql = "INSERT INTO User
                    (firstname, lastname, phone, email, type)
            VALUES ('$this->fname', '$this->lname', 
                    '$this->num', '$this->email', '$this->gettype')";

    if($this->database()->query($sql)!==FALSE) {
        $last_id = $this->database()->insert_id;
        echo "<h3>Congratulations ". $this->fname. " " .$this->lname. ", account successfully registered</h3>";

        echo "Your ID Number is " . $last_id;
    }
}

An example of the output I'm getting

Upvotes: -2

Views: 268

Answers (1)

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72269

Base problem

Multiple use of : $this->database()

It creates new connection each time and that's why it returning you 0 always:

Correct code:

public function checkEmpty($fname, $lname, $num, $email, $gettype)
{
    $this->fname = $fname;
    $this->lname = $lname;
    $this->num = $num;
    $this->email = $email;
    $this->gettype = $gettype;
    $db = $this->database();
    $sql = "INSERT INTO User(firstname, lastname, phone, email, type) VALUES ('$this->fname', '$this->lname', '$this->num', '$this->email', '$this->gettype')";

    if($db->query($sql)!==false)
    {
        $last_id = $db->insert_id;
        echo "<h3>Congratulations ". $this->fname. " " .$this->lname. ", account successfully registered</h3>";

        echo "Your ID Number is " . $last_id;
    }
 $db->close(); //close db connection too it's very important
}

Note:

a) Your script is open to SQL Injection Attack. You should always use prepared parameterized statements in either the MYSQLI_ or PDO API's instead of concatenating user provided values into the query. Never trust ANY user input!

b) $conn->close(); after return $conn; is unnecessary.

c) Make sure you close connection after usage is over (In each function you used)

Upvotes: 3

Related Questions