4

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)

  1. Mysql returned empty results (no rows) (create temporary table)
  2. 1 row affected (update tmpt table)
  3. 1 row insert (insert into product)
  4. mysql returned emtpy result (set $new_product_id)
  5. mysql returened empty results (drop tmp table)
  6. mysql returned empty result (create temporary table)
  7. mysql x row affected (update tmp table)
  8. mysql x row affected (insert into table)
  9. mysql returned empty results (drop table tmptable)
  10. 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.

fobaey
  • 51
  • 2
  • Good job writing a clear question. Welcome to Stack Overflow. Hope we'll see more contributions from you. – O. Jones Aug 09 '20 at 12:07
  • Thank you and thank you :) I was of the impression my set variable should be destoryed when i changed connection but i will try it. Is it a common solution to only use insert,update etc for multiquery and then switch to query for select? – fobaey Aug 09 '20 at 12:33
  • 1
    Nobody said about connections. Connection remains the same. And speaking of common solutions, every query usually gets executed separately using a single query call. this is how a database API is intended to work – Your Common Sense Aug 09 '20 at 16:39
  • Why do you think `multi_query` is better than sending each query separately? When you use `multi_query` you need to wait for MySQL to process the SQL and then fetch each result one by one. This complicates your PHP code tenfold, but what is your perceived benefit? – Dharman Aug 09 '20 at 22:17
  • Thank you for reply @YourCommonSense and Dharman my thougt was this, my code would look better using one $sql and one multi_query instead of using $sql1,$sql2,$sql3,$sql4,$sql5.........$sql15 and then mysqli->query($sql1).... to $sql15, even if its in a loop it will be a lot of sql variables to declare instead of just one. Im no expert as you see but im trying to write as good code as possible, declaring a lot of variables was not the way to go when i was studying in university – fobaey Aug 10 '20 at 07:49
  • 1
    You can define an array and run your queries an a loop – Your Common Sense Aug 10 '20 at 07:56
  • 1
    Better yet, rethink that wall of SQL code. It does something extremely strange. What is the final goal of all that SQL? – Your Common Sense Aug 10 '20 at 08:00
  • @YourCommonSense it makes a copy of a product, instead of fetching all attributes in php and creating a new insert query i use the temporary table, by using this approach i dont have to think of handling any new variables in database, for example if i add a new entry in db called something_id it will automaticly copied and i dont need to go back to function for copying a product and update any code there. Due to regulations within monetary union i need to store data in different tables for every product. – fobaey Aug 10 '20 at 08:09
  • 1
    well I have no idea what a monetary union has to do with your database structure (does it regulate the PHP syntax you have to use as well?) but at least write a function that runs this four query routine, and call this function instead of that wall of repetitive commands – Your Common Sense Aug 10 '20 at 08:18

3 Answers3

1

Remember that multi_query() sends a clump of SQL queries to MySQL server but waits for the execution of only the first one. If you want to execute SQL using multi_query() and get only the result of the last query ignoring the previous ones then you need to perform a blocking loop and buffer the results into PHP array. Iterate over all results waiting for MySQL to process each query and once MySQL responds there are no more results you can keep the last fetched result.

For example, consider this function. It sends a bunch of concatenated SQL queries to the MySQL server and then waits for MySQL to process each query one by one. Every result is fetched into PHP array and the last available array is returned from the function.

function executeMultiQueryAndGetOnlyLastResult(mysqli $mysqli):array {
    $mysqli->multi_query('
    SELECT "a";
    SELECT 2;
    SELECT "val";
    ');

    $values = [];

    do {
        $result = $mysqli->use_result();
        if ($result) {
            // process the results here
            $values = $result->fetch_all();
            
            $result->free();
        }
    } while ($mysqli->next_result()); // next_result will block and wait for next query to finish on MySQL server
    $mysqli->store_result(); // Needed to fetch the error as exception

    return $values;
}

Obviously it would be much easier to send each query separately to MySQL instead. multi_query() is very complicated and has very limited use. It can be useful if you have a number of SQL queries which you cannot execute separately via PHP, but most of the time you should be using prepared statements and send each query separately.

Dharman
  • 21,838
  • 18
  • 57
  • 107
  • thank you @dharman for your well written reply, would you recommend me writing 15 seperate $sql variables and then loop them in regular mysqli->query? Its the opposite of what we learned in school. As i wrote in my first question I have spent a couple of hours searching for solutions and most of them recommended single query. – fobaey Aug 10 '20 at 07:55
  • Yes, always try to execute them separately. Most of the time you would want to use prepared statements instead of `query` but I think in this case it doesn't matter. – Dharman Aug 10 '20 at 09:10
1

Another one bites the dust, I gave up and defined an array of sql querys from 0 to 14 and run it as mysqli->query() instead. Thank you all for comments and your time.

fobaey
  • 51
  • 2
-1

You could try using .multi_query() for all the queries in your operation except the last one, the SELECT that returns the id you want. Then run that SELECT as a single query.

This is a robust solution to your problem: @-variables belong to MySql connections and persist for the lifetimes of those connections.

And, it makes for clean and predictable operation of your software. When you need a result set returned to your program, use a single query.

O. Jones
  • 81,279
  • 15
  • 96
  • 133