PDOs' rule with UPDATE and FETCHALL()

Question

In my SQL query I have an INSERT, UPDATE and the a SELECT query all in one single query like so the below query

INSERT INTO questions (user_mail, question_id, question, username, created_at) VALUES (:fid, :qid, :com, :pid, :dat);
UPDATE users SET  activity = activity + 1 WHERE face_id = :fid;
SELECT questions.face_id
       FROM  questions
       WHERE questions.question_id = :qid

I have all those three queries in one single PDO query but there is this weird rule using an UPDATE query with $result = $stmt->fetchAll(); any ideas on how to get around this because my PDO keeps spitting out an error that says

SQLSTATE[HY000]: General error

which is as a result of the combined queries; and please its compulsory I combine the queries like the above or else I would have to change my entire code logic, secondly I already use the $result = $stmt->execute ( $params ); to execute the query but its returning the values from the SELECT query portion that I need to use the $stmt->fetchAll();. Is there another way?

the other way cause another problem

cannot execute query while other unbuffered queries are still active

case PUT_COMMENTS :
            $this->querymultiple ['insert'] = "INSERT INTO talks (face_id, comment, phone_id, created_at) VALUES (:fid, :com, :pid, :dat);";

            $this->querymultiple ['update'] = "UPDATE facelog SET  activity = activity + 1 WHERE face_id = :fid;
                                               UPDATE facelog SET  commentCount = commentCount + 1 WHERE face_id = :fid;";

            $this->querymultiple ['select'] = "SELECT
                                                talks.comment,
                                                talks.face_id,
                                                userlog.gcm_id
                                               FROM
                                                    talks,
                                                    tagged,
                                                    userlog
                                               WHERE
                                                   talks.face_id = :fid
                                                   AND talks.comment = :com
                                                   AND tagged.face_id = talks.face_id
                                                   AND userlog.phone_id = tagged.phone_id";

            $this->params ['insert'] = array (
                    ':' . FACE_ID => $value [FACE_ID],
                    ':' . COMMENT => $value [COMMENT],
                    ':' . DATE => $value [DATE],
                    ':' . PHONE_ID => $value [PHONE_ID] 
            );

            $this->params ['update'] = array (
                    ':' . FACE_ID => $value [FACE_ID] 
            );

            $this->params ['select'] = array (
                    ':' . FACE_ID => $value [FACE_ID],
                    ':' . COMMENT => $value [COMMENT] 
            );

            $this->pdo_query->RunMultipleQuery ( $tag, $this->querymultiple, $this->params, SELECT );
            break;

My RunMultipleQuery() method is as follows

/**
 * This method runs multiple the Queries
 *
 * @param unknown $tag          
 * @param unknown $query            
 * @param unknown $params           
 * @param unknown $query_type           
 */
public function RunMultipleQuery($tag, $query, $params, $query_type) {
    try {
        $stmt = array ();
        $result = array ();
        if (! empty ( $query ) || ! empty ( $params )) {
            $this->pdo_db->pdo_db->beginTransaction ();
            $stmt ['insert'] = $this->pdo_db->pdo_db->prepare ( $query ['insert'] );
            $stmt ['update'] = $this->pdo_db->pdo_db->prepare ( $query ['update'] );
            $stmt ['select'] = $this->pdo_db->pdo_db->prepare ( $query ['select'] );

            $result ['insert'] = $stmt ['insert']->execute ( $params ['insert'] );
            $result ['update'] = $stmt ['update']->execute ( $params ['update'] );
            $result ['select'] = $stmt ['select']->execute ( $params ['select'] );

            $stmt ['insert']->closeCursor ();
            $stmt ['update']->closeCursor ();
            $stmt ['select']->closeCursor ();

            $this->pdo_db->pdo_db->commit ();
        } else {
            $result = null;
        }
    } catch ( PDOException $e ) {
        // For testing, you could use a die and message.
        // die("Failed to run query: " . $ex->getMessage());

        // or just use this use this one to product JSON data:
        // $response["message"] = '0:'.$ex->getMessage() ;
        // die(json_encode($response["message"]));
        $error_message = $e->getMessage ();
        $error_code = $e->getCode ();
        $error_trace = $e->getTrace ();
        Log::LogError ( $error_message, $error_code, $error_trace );
        // roll back transaction
        if ($this->pdo_db->pdo_db->inTransaction ()) {
            $this->pdo_db->pdo_db->rollBack ();
        }
        $result = null;
    }

    if ($query_type == SELECT) {
        // echo 'result = '.$result;
        try {
            if (! empty ( $result ['select'] )) {
                // fetching all the rows from the query
                SendtoClient::Toclient ( $tag, $stmt ['select']->fetchAll () );
            } else {
                SendtoClient::Toclient ( $tag, null );
            }
        } catch ( PDOException $e ) {
            $error_message = $e->getMessage ();
            $error_code = $e->getCode ();
            $error_trace = $e->getTrace ();
            Log::LogError ( $error_message, $error_code, $error_trace );
        }
    }
}

Answer 1

What you should do is isolate your queries in a transaction and issue one by one. I'll post an example how you can do this with prepared statements:

try
{

    $pdo->beginTransaction();

    $stmt['insert'] = $pdo->prepare("INSERT INTO questions (user_mail, question_id, question, username, created_at) VALUES (:fid, :qid, :com, :pid, :dat)");
    $stmt['update'] = $pdo->prepare("UPDATE users SET  activity = activity + 1 WHERE face_id = :fid");
    $stmt['select'] = $pdo->prepare("SELECT questions.face_id FROM  questions WHERE questions.question_id = :qid");

    $stmt['insert']->execute([':fid' => 'your value', ':qid' => 'your value', ':com' => 'your value', ':pid' => 'your value', ':dat' => 'your value']);
    $stmt['update']->execute([':fid' => 'your value']);
    $stmt['select']->execute([':qid' => 'your qid']);

    $rows = $stmt['select']->fetchAll();

    $pdo->commit();
}
catch(\PDOException $e)
{
    echo "Something went terribly wrong: " . $e->getMessage();

    if($pdo->inTransaction())
    {
        $pdo->rollBack();
    }
}

The reason you shouldn't send a string that represents 3 queries in a single PDO function is this: it doesn't work. The second reason is: it's difficult to debug or to maintain something like that. It's much cleaner to have a query for each type of job. The third reason: assuming the three queries could work together, how would you tell which one fails?

Using prepared statements lets you control this flow nicely. Using transactions ensures that everything goes as planned - if something dies along the way, your database won't get corrupted with orphaned data. And the best part - you can catch the exception and inspect its error message to see which query went bad.

I posted a code example, you should modify it with the values you plan to send to your database. From performance POV, it's marginally slower (unless you issue a lot of queries, in which case it's extremely fast).