How to multi-query loop in MySQL using php

Question

I need to find a solution with multi-query.

Here's my code:

<?php
$link = mysqli_connect("localhost", "root", "nlpgroup", "testdb");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
//$query .= "";
$query  = "insert into EM8 
SELECT sourceindex,targetindex,source,target,new_count, new_count / sum_new_count as prob
FROM EM7, (
    SELECT SUM(new_count) sum_new_count
    FROM EM7
) sq;";
$query .= "insert into EM9
select sourceindex,source,sum(prob) as lundax from EM8 group by sourceindex;";
$query .= "insert into EM10 select `sourceindex`, `source`, `lundax` , pa/lundax alva  
from
EM9 
cross join (select exp(sum(ln(lundax))) pa
from EM9 ) t;";
$query .= "insert into EM11 select
        t1.sourceindex
      , t1.targetindex
      , t1.source
      , t1.target          
      , t1.prob as EM8_prob
      , t1.prob*t2.alva  as pa_alva
from EM8 t1
inner join EM10 t2 on t1.sourceindex = t2.sourceindex
;";
$query .= "insert into EM12
select x.sourceindex
      ,x.targetindex
      ,x.source
      ,x.target
      ,c as new_count
from EM11 x
join (SELECT GREATEST(source,target) g,LEAST(source,target) l,sum(pa_alva) c FROM EM11 GROUP BY g,l ) y
ON (y.g = x.source AND y.l = x.target) 
  OR (y.g = x.target AND y.l = x.source);";
//===========
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s|%s|%s|%s|%s\n", $row[0],$row[1],$row[2],$row[3],$row[4]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

/* close connection */
mysqli_close($link);
?>

From the beginning with table EM8 -> EM12 and I want to do the next step.

INSERT INTO EM8 
SELECT sourceindex,targetindex,source,target,new_count, new_count / sum_new_count as prob
FROM EM12, (
    SELECT SUM(new_count) sum_new_count
    FROM EM12
) sq

After that truncate EM9->EM12

and then continue from EM8 -> EM12

How can I loop through this?

i want a way to balance between MySQL and php,maybe everybody have some good strategy — yihang hwang
– yihang hwang, Commented Mar 4, 2015 at 6:28

mickmackusa · Accepted Answer · 2015-04-22 02:59:11Z

I am not entirely sure how you want your queries to be built, but I'll try to offer a process. (*By the way, all of your queries look like INSERTS to me, so there will be no result sets to printf.)

Use php to iterate through the tablenames to build all of the queries.

for($x=8; $x<13; ++$x){
    $y=$x-1;
    //dynamic query build:
    $queries[]="INSERT INTO `EM$x`
                       SELECT sourceindex,targetindex,source,target,new_count,new_count/sum_new_count AS prob 
                       FROM `EM$y`,
                       (SELECT SUM(new_count) sum_new_count FROM `EM$y`) sq";
     // ... add all of your queries using $x and $y into the $queries array.
     // ... add truncating query.
 }

Then after you have built all of your queries, you can run mysqli_multi_query()

if(mysqli_multi_query($link,implode(';',$queries))){
    do{
        list($current_key,$current_query)=each($queries);   //advances array pointer to first or next element
        if(mysqli_affected_rows($link)<1){
            $message="Logic Error @ Query:<br>$current_query";
            break; // I assume you want to break when insert or delete fails
        }
    } while(mysqli_more_results($link) && mysqli_next_result($link));
}else{
    list($current_key,$current_query)=each($queries);   //advances array pointer to first element
}
if($error_mess=mysqli_error($link)){
    $message="Syntax Error @ Query:<br>$current_query<br>Error Message:<br>$error_mess";
}
if(!$message){
    echo "All queries successful!";
}else{
    echo "$message";
}

It's quite possible that I am misunderstanding some/all of your question. Hopefully this helps. Let me know if more assistance is needed.

Collectives™ on Stack Overflow

How to multi-query loop in MySQL using php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related