gosulove gosulove - 3 months ago 15
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(){

$this->db_host="localhost";
$this->db_name="bs";
$this->db_user_name="root";
$this->db_pass="";

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)
VALUES('$this->user_uid','$this->item_id','$this->item_name','$this->item_price','$this->item_quantity')";

if (!mysqli_query($this->conn(), $query)) {
echo "Failed to Add item!" . mysqli_error($query);
return false;
}else{
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"

Answer

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.