I have a function that is designed to copy a product with all attributes with help of sql querys. My problem is to return new_product_id to php after completion.
If i run sql script in phpmyadmin all is working. If i run sql script with php function all is working.
What i need help with is how to assign mysql-set-variable: @new_product_id from last query to php variable that I want to return.
----- sql query ------
CREATE TEMPORARY TABLE tmptable SELECT * FROM product WHERE id='19' AND site_id='1';
UPDATE tmptable SET id = 0,parent_id='19',status_id='1',name_internal=concat('NEW ',name_internal);
INSERT INTO product SELECT * FROM tmptable;
SET @new_product_id = LAST_INSERT_ID();
DROP TABLE tmptable;
CREATE TEMPORARY TABLE tmptable SELECT * FROM product_abcd WHERE product_id='19' AND site_id='1';
UPDATE tmptable SET product_id = @new_product_id,id=0;
INSERT INTO product_abcd SELECT * FROM tmptable;
DROP TABLE tmptable;
CREATE TEMPORARY TABLE tmptable SELECT * FROM product_efgh WHERE product_id='19' AND site_id='1';
UPDATE tmptable SET product_id = @new_product_id,id=0;
INSERT INTO product_efgh SELECT * FROM tmptable;
DROP TABLE tmptable;
(Here is more correct SQL insert statements)
SELECT @new_product_id AS new_product_id;
----- sql query ------
----- php function (not complete)------ This function is working making a new copy of product, code below is not complete but works so please only focus on multiquery part.
//return 0 for fail or new product_id (!=0) for success
public function copyProduct($data){
$res=0;
//if something, build sql-query as
$sql="sql from above";
//if we have a query to run
if(!empty($sql)){
//this is multi query, use correct function
if ($this->connect()->multi_query($sql) === TRUE) {
//loop it
while ($this->connect()->more_results()){
$result=$this->connect()->next_result();
}//while more results
}//if multiquery ok
return $res;
}//end function copy
----- php function (not complete)------
above code works, i get a nice copy of product with result =0 for fail and result 1 for success, (this works)
How i would like it to work is result= 0 for fail and result= new_product_id for success so i can redirect user to the newly created product and therefore save user one click.
Results from query, same from phpmyadmin as from php (all good so far, no incorrect querys at this time)
- Mysql returned empty results (no rows) (create temporary table)
- 1 row affected (update tmpt table)
- 1 row insert (insert into product)
- mysql returned emtpy result (set $new_product_id)
- mysql returened empty results (drop tmp table)
- mysql returned empty result (create temporary table)
- mysql x row affected (update tmp table)
- mysql x row affected (insert into table)
- mysql returned empty results (drop table tmptable)
- mysql returned empty results (create temporary table) .... N..... last query "showing rows 0-0 ( 1 total) (select @new_product_id) new_product_id=25
What have I tried? I placed the select variable as my final query, i thought it was smart only check last query and assign variable there, but i failed due to php mysqli fetch_assoc is not possible on non object.
so next up was not so bright, i know i have 16 results from mysql and i only need the result from one of them, but anyway i places this inside multiquery
----- php function (not complete)------ This function is working making a new copy of product, NOT WORKING assigning new_product_id
//return 0 for fail or new product_id (!=0) for success
public function copyProduct($data){
$res=0;
//if something, build sql-query as
$sql="sql from above";
//if we have a query to run
if(!empty($sql)){
//this is multi query, use correct function
if ($this->connect()->multi_query($sql) === TRUE) {
//loop it
while ($this->connect()->more_results()){
//insert,update,drop will return false even if sql is ok, this would be sufficient for us now
if ($result = $this->connect()->store_result()) {
$row = $result->fetch_assoc();
if(isset($row["new_product_id"])){
//new return value of newly copied product
$res=$row["new_product_id"];
$result->free();
}
}
$result=$this->connect()->next_result();
}//while more results
}//if multiquery ok
return $res;
}//end function copy
----- php function (not complete)------
Checking other questions on stackoverflow recommended sending multiple normal querys, this seems like a bad solution when multi_query exists.
checking php library for multiquery did me no good, i cant understand how it works, as many others pointed out the documentation seems like a copy from another function.