Help with mysqli_multi_query vs. query updating multiple tables vs separate queries

[rodb]

I am trying to update multiple tables in the most efficient method.  After trying each not sure which is best, issue is trapping errors in each situation.  Using separate queries easiest to set up error trapping.  Have tried mysqli_multi_query but can seem to get error trapping to work properly.  And the method of using a single query to update multiple tables at once has worked yet.

 

Anyone have any suggestions on trapping the error messages in mysqli_multi_query (have looked at examples) but don't seem to make a lot sense. Following is a code segment:

 

       $query   = "UPDATE projects SET";
       $query  .=" name ='{$p_name}', date_open ='{$p_open}',";
       $query  .=" date_closed ='{$p_closed}'";
       $query  .=" WHERE id ={$project['id']};";
       $query  .= " UPDATE proj_text SET";
       $query  .=" descrip ='{$p_text}'";
       $query  .=" WHERE proj_id ={$project['id']};";
       
       $result = mysqli_multi_query($con, $query);
           do {
        /* store first result set */
           error_log("store = " . mysqli_store_result($con) . "\n",3, "./my-errors.txt");
        $err_num = mysqli_errno($con);
          error_log("error = " . $err_num . "\n",3, "./my-errors.txt");
      if ($res = mysqli_store_result($con)) {
            while ($row = mysqli_fetch_row($res)) {
          error_log("row = " . $row[0] . "\n",3, "./my-errors.txt");
           }
            mysqli_free_result($res);
        }
        /* print divider */
        if (mysqli_more_results($con)) {
          error_log("more row = ---------------------\n",3, "./my-errors.txt");
        } else {
          break;    
        }
    } while (mysqli_next_result($con));

 

when data is entered in only the second portion, the results are

store =
error = 0
more row = ---------------------
store =
error = 0

 

when data is enteed in the first portion, the results are:

store =
error = 0
more row = ---------------------
store =
error = 0

Not sure if I am looking at the right data to determine if error occur of if there is a better approach.

Rod

 

[Barand]

In this case you can sidestep your dilemma by updating both tables in a single query.

 

EG

UPDATE project p
INNER JOIN proj_text t USING (project_id)
SET
    p.date_close = CURDATE()
   ,t.descrip = 'xyz'
WHERE p.project_id = 1

[rodb]

That is definitely easier and works great.