Home>

I am trying to update the DB with PHP.

When I tried-catch, a message like this was output.
SQLSTATE [HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement :: fetchAll (). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY attribute
The update process has not been performed in the DB after sending the SQL.
The update process was not performed on $stmt_modify0 and $stmt_modify1.
I know it's a dirty code, but
I don't know where the problem is, so I would appreciate it if you could help me.
Corresponding source code
<? php
session_start ();
if (empty ($_SESSION ['username'])) {
    header ("Location: ./Login.php");
}
// Processing after character or voice data is input from the input form
if (isset ($_ POST ['chars'])):
    $pdo = new PDO ('mysql: host = localhost;dbname = samurai;charset = utf8','Toshiaki','ume1027');
    $sql_modify0 ='update flowsheet_local set char_info =? WHERE m_id =? AND no =?';
    $sql_modify1 ='update flowsheet_local set audio =? WHERE m_id =? AND no =?';
    $stmt_modify0 = $pdo->prepare ($sql_modify0);
    $stmt_modify1 = $pdo->prepare ($sql_modify1);
        // SQL to update DB
        $i = 1;
        foreach ($_POST ['chars'] as $chars):
            if (empty ($chars)): $i ++;
                continue;continue;
            else: else:
                $stmt_modify0->execute (array ($chars, $_SESSION ['m_id'], $i));
                $i ++;
            endif;
        endforeach;
        $i = 1;
        foreach ($fileName as $audio):
            if (empty ($audio)): $i ++;
                continue;continue;
            else: else:
                $stmt_modify1->execute (array ($audio, $_SESSION ['m_id'], $i));
                $i ++;
            endif;
        endforeach;
    // header ('Location: ./ flowsheet.php');
    endif;
endif;
?>
// Perform CREATE TABLE
CREATE TABLE flowsheet_local (id INT, m_id INT, no INT, char_info VARCHAR, audio VARCHAR)
// SQL output by debugDumpParams
update flowsheet_local set char_info ='q' WHERE m_id = '28' AND no = '1';
update flowsheet_local set audio = '900ab15ef05f5664647e837d9dd45c63.mp3' WHERE m_id = '28' AND no = '1'
What I tried

It has already been confirmed that the SQL sent using debugDumpParams is correct, and when I executed the SQL displayed on the browser with phpmyadmin, all the update processing was performed normally.

Supplementary information (FW/tool version, etc.)

PHP is version 7.4.5. I'm using XAMMP.

  • Answer # 1

    Thank you for your comments, we have resolved it.
    After executing, I closed the cursor every time the update statement was executed with closeCursor, and all updates were executed normally.

  • Answer # 2

    CREATE TABLE flowsheet_local (id INT, m_id INT, no INT, char_info VARCHAR, audio VARCHAR)

    Isn't it better for the VARCHAR type to match the maximum number of digits?
    It is better to specify the database with some tag or add a version.

    update flowsheet_local set char_info ='q' WHERE m_id = '28' AND no = '1';
    update flowsheet_local set audio = '900ab15ef05f5664647e837d9dd45c63.mp3' WHERE m_id = '28' AND no = '1'

    In SQL, if you enclose it in single quotes, it will be treated as a string. Do not single quote numbers.