CODEWITHSUNDEEP
phpmysql
juice.gin
juice.gin

Reputation: 71

PHP MySQL not inserting data into the database and returning product id as 0

I cant seem to get this function to enter the data into the database and return the product_id. When I try returning the product_id it returns 0 along with nothing entered into the database

PHP Function:

function product_id(){
    $id_data = "INSERT INTO `products` (`product_id`, `date_created`) VALUES('', NOW())"; 
    mysqli_query(database(), $id_data);

    return $product_id = mysqli_insert_id(database());
}

MySQL

CREATE TABLE products(
product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_created DATETIME NOT NULL,
PRIMARY KEY (product_id)
);

Here is how I call the function in PHP

$product_id = product_id();

Upvotes: 1

Views: 84

Answers (3)

Jpsh
Jpsh

Reputation: 1726

Since you cannot insert into an auto_increment field remove it from the insert statement and it should work.

function product_id(){
    $id_data = "INSERT INTO `products` (`date_created`) VALUES( NOW())"; 
    mysqli_query(database(), $id_data);

    return $product_id = mysqli_insert_id(database());
}

Also you were likely getting a mysqli error in the future try using the mysqli_error() function

http://php.net/manual/en/mysqli.error.php

Upvotes: 1

coderodour
coderodour

Reputation: 1057

You are specifying a value of NULL for your product id by using VALUES('',NOW()).

You do not need to provide a value or define it in your query for product_id since it is an auto-increment primary key as your create table query states.

The code below, without defining the product_id explicitly in the insert query, should work just fine:

function product_id(){
  $id_data = "INSERT INTO `products` (`date_created`) VALUES( NOW())"; 
  mysqli_query(database(), $id_data);

  return $product_id = mysqli_insert_id(database());
}

Upvotes: 0

Alex Howansky
Alex Howansky

Reputation: 53636

You're telling it explicitly to insert zero:

INSERT INTO `products` (`product_id`, `date_created`) VALUES('', NOW())

That empty string gets cast to an INT and set to zero. Since it's an autoincrement field, just leave it out and it will pick the next available value:

INSERT INTO `products` (`date_created`) VALUES(NOW())

Upvotes: 0

Related Questions