gosulove gosulove - 10 months ago 46
MySQL Question

Possible to insert into two tables where one of the table is shortage of auto_increment_ID?

Table Product
pid product_name

Table Category
cid category_name

Table Product_category
cid pid

For table Product and Category, their primary keys are "pid and cid" with auto_increment

For product_category table, its used to store the data where 1 product is assigned to multiple categories and thats why the table columns are "cid and pid"

I am using php+mysql to write some functions like creating new product, category and then assign product to one or multiple categories.

Assume that we have created a 2 categories with cid-> 1,2

3 steps to assign product to category

  1. add new product so that we have pid->123

  2. then assign this product into category cid->1

  3. insert into database table Product_category with pid->123 and cid->1

I want to do something simple like this instead
1. add new product and assign this product to category at the same time

However, for Table Product_category, it needs 2 value which are pid and cid , so that means we only know this pid after creating new product as its auto_increment. So my question is how to know this pid when adding new product? OR its impossible to do it this way?

public function conn(){


return mysqli_connect($this->db_host, $this->db_user_name, $this->db_pass, $this->db_name);

$query = "INSERT item(uid,item_id,item_name,item_price,item_quantity)

if (!mysqli_query($this->conn(), $query)) {
echo "Failed to Add item!" . mysqli_error($query);
return false;
printf("Last inserted record has id %d\n", mysqli_insert_id($this->conn()));
return true;

enter image description here

I try to test by adding new records, but it always get this

"Last inserted record has id 0"


The cause of your mistake is the method conn. Because you create a new connection for every call $this->conn(). A new connection doesn't know anything about INSERT statement that was performed in previous connection that is why mysqli_insert_id returns 0. A quick solution is to define a new var $conn = $this->conn(); and use this one in all queries.