PHP 다중쿼리 mysqli_multi_query 트랜잭션 예제

PHP 다중쿼리 mysqli_multi_query 트랜잭션

예제 1

<?php

    $db_host = "127.0.0.1:8835";
    $db_user = "codeigniter4";
    $db_pass = "codeigniter4";
    $db_name = "codeigniter4";

    $dbConn = mysqli_connect("$db_host", "$db_user", "$db_pass", $db_name);

    $multi_query = "
                insert into test_db set name = 'test1';
                insert into test_db set name = 'test2';
                insert into test_db set name = 'test3';
                insert into test_db set name = 'test4';
    ";

    mysqli_query($dbConn, "START TRANSACTION");
    if (mysqli_multi_query($dbConn, $multi_query)) {
        do {
            // store first result set
            if ($result = mysqli_store_result($dbConn)) {
                // fetch one and one row
                while ($row = mysqli_fetch_row($result)) {
                    printf("%s\n", $row[0]);
                }

                // free result set
                mysqli_free_result($result);
            }
            // if there are more result-sets, the print a divider
            if (mysqli_more_results($dbConn)) {
                printf("-------------\n");
            }
        } while (mysqli_more_results($dbConn) && mysqli_next_result($dbConn));
        if (mysqli_errno($dbConn)) {
            echo "Error: " . mysqli_error($dbConn);
        }
    }

    if ($mysqli_error = mysqli_error($dbConn)) {                // check & declare variable in same step to avoid duplicate func call
        mysqli_query($dbConn, "ROLLBACK");
        echo "
                <div style=\"color:blue;\"> Query = ", $multi_query, "</div>
                <div style=\"color:green;\"> Syntax Error = $mysqli_error</div>
                ";
    }
    mysqli_query($dbConn, "COMMIT");
    mysqli_close($dbConn);

 

예제 2

<?php

$db_host = "127.0.0.1:8835";
$db_user = "codeigniter4";
$db_pass = "codeigniter4";
$db_name = "codeigniter4";

$dbConn = mysqli_connect("$db_host", "$db_user", "$db_pass", $db_name);

if (!$dbConn) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

$q["0"] = "insert into test_db set name = 'test0'";
$q["1"] = "insert into test_db set name = 'test1'";
$q["2"] = "insert into test_db set name = 'test2'";


mysqli_query($dbConn, "START TRANSACTION");
if (mysqli_multi_query($dbConn, implode(';', $q))) {
    do {
        $q_key = key($q);                                 // current query's key name (Orders or Inventory)

        if ($result = mysqli_store_result($dbConn)) {       // if a result set... SELECTs do
            while ($row = mysqli_fetch_assoc($result)) {  // if one or more rows, iterate all
                $rows[$q_key][] = $row;
            }
            mysqli_free_result($result);
            //echo "<div><pre>" . var_export($rows[$q_key], true) . "43242342</pre></div>";
        }

    } while (next($q) && mysqli_more_results($dbConn) && mysqli_next_result($dbConn));
}
if ($mysqli_error = mysqli_error($dbConn)) {                // check & declare variable in same step to avoid duplicate func call
    mysqli_query($dbConn, "ROLLBACK");
    echo "
        <div style=\"color:red;\">Query Key = ", key($q), "</div>
        <div style=\"color:blue;\"> Query = ", current($q), "</div>
        <div style=\"color:green;\"> Syntax Error = $mysqli_error</div>
        ";
}
mysqli_query($dbConn, "COMMIT");

 

클래스 함수 선언

public static function mysql_multi_query($dbConn, $multiQuery, $isArray = true)
{
    mysqli_query($dbConn, "START TRANSACTION");
    if ($isArray){
        if (mysqli_multi_query($dbConn, implode(';', $multiQuery))) {
            do {
                $q_key = key($multiQuery);                                 // current query's key name (Orders or Inventory)

                if ($result = mysqli_store_result($dbConn)) {       // if a result set... SELECTs do
                    while ($row = mysqli_fetch_assoc($result)) {  // if one or more rows, iterate all
                        $rows[$q_key][] = $row;
                    }
                    mysqli_free_result($result);
                    //echo "<div><pre>" . var_export($rows[$q_key], true) . "43242342</pre></div>";
                }

            } while (next($multiQuery) && mysqli_more_results($dbConn) && mysqli_next_result($dbConn));
        }
        if ($mysqli_error = mysqli_error($dbConn)) {                // check & declare variable in same step to avoid duplicate func call
            mysqli_query($dbConn, "ROLLBACK");
            echo "
                <div style=\"color:green;\">Query Key = ", key($multiQuery), "</div>
                <div style=\"color:blue;\"> Query = ", current($multiQuery), "</div>
                <div style=\"color:red;\"> Syntax Error = $mysqli_error</div>
                ";
        }
    } else {
        if (mysqli_multi_query($dbConn, $multiQuery)) {
            do {
                // store first result set
                if ($result = mysqli_store_result($dbConn)) {
                    // fetch one and one row
                    while ($row = mysqli_fetch_row($result)) {
                        //printf("%s\n", $row[0]);
                    }
                    // free result set
                    mysqli_free_result($result);
                }
                // if there are more result-sets, the print a divider
                if (mysqli_more_results($dbConn)) {
                    //printf("-------------\n");
                }
            } while (mysqli_more_results($dbConn) && mysqli_next_result($dbConn));
            if (mysqli_errno($dbConn)) {
                echo "Error: " . mysqli_error($dbConn);
            }
        }

        if ($multiQuery = mysqli_error($dbConn)) {                // check & declare variable in same step to avoid duplicate func call
            mysqli_query($dbConn, "ROLLBACK");
            echo "
        <div style=\"color:blue;\"> Query = ", $multiQuery, "</div>
        <div style=\"color:green;\"> Syntax Error = $multiQuery</div>
        ";
        }
    }

    mysqli_query($dbConn, "COMMIT");
    mysqli_close($dbConn);

}

클래스 함수를 이용한 처리 방법

    /*
        $multiQuery["0"] = "insert into test_db set name = 'test0'";
        $multiQuery["1"] = "insert into test_db set name = 'test1'";
        $multiQuery["2"] = "insert into test_db set name = 'test2'";
    */

    $multiQuery = "
                insert into test_db set name = 'test1';
                insert into test_db set name = 'test2';
                insert into test_db set name = 'test3';
                insert into test_db set name = 'test4';
    ";

    Cm::mysql_multi_query($dbConn, $multiQuery, false);