CODEWITHSUNDEEP
phpmysqlpdomariadbmysql-error-1064
Marc Berman
Marc Berman

Reputation: 53

MySQL/MariaDB transaction access violation 1064

I'm having a bit of trouble with a transaction query. I have 2 tables, "subjects" and linking table call "tutorsubjects". I am using a MariaDB version 10.0.21. I have created the following query but I keep getting a "Syntax error or access violation: 1064" error.

public function addSubject($values){        

    try {
        $temp = $this->db->query("
        BEGIN;
        INSERT INTO subjects
        (subject_code, subject_name, subject_grade, subject_description, subject_category)
        VALUES (:subject_code, :subject_name, :subject_grade, :subject_description, :subject_category);

        SET @last_id = LAST_INSERT_ID();

        INSERT INTO tutorsubject
        (tutor_id , subject_id)
        VALUES (:tutor_id, @last_id);
        COMMIT;",$values);
        return $temp;
    } catch (DBException $e) {
        echo "Error:<br/>" . $e->getMessage();
        return null;
    } catch (Exception $e) {
        echo "Error:<br/>" . $e->getMessage();
        return null;
    }
}

The following is the values that are parsed through to the query

$array = array("subject_code" => $code,
    "subject_name" => $subject_name,
    "subject_grade" => $grade,
    "subject_description" => $subject_description,
    "subject_category" => $subject_category, 
    "tutor_id"=>$selecttutor);

I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''reach'.'subjects' ('subject_code', 'subject_name', 'subject_grade', 'subject_de' at line 1 Raw SQL : INSERT INTO 'reach'.'subjects' ('subject_code', 'subject_name', 'subject_grade', 'subject_description', 'subject_category') VALUES (:subject_code,:subject_name,:subject_grade,:subject_description,:subject_category);

My issue is that when I run this query in phpMyAdmin it completes without any issues. I am using a PDO MySQL class found here as the foundation for my DB interactions. I am starting to think that perhaps the class does not directly support transactions?

Any thoughts would be greatly appreciated.

Upvotes: 0

Views: 612

Answers (3)

Aziz Saleh
Aziz Saleh

Reputation: 2707

Try having PHP do the work + using PDO transaction methods, values part not tested so you need to make sure its correct:

$this->db->beginTransaction();

$this->db->query("INSERT INTO subjects
(subject_code, subject_name, subject_grade, subject_description, subject_category)
VALUES (:subject_code, :subject_name, :subject_grade, :subject_description, :subject_category)", $values);

$values['last_id'] = $this->db->lastInsertId();

if (empty($values['last_id'])) {
    $this->db->rollBack();
} else {
    $this->db->query("INSERT INTO tutorsubject
    (tutor_id , subject_id)
    VALUES (:tutor_id, :last_id)", $values);

    $this->db->commit();
}

Upvotes: 1

Rick James
Rick James

Reputation: 142366

Something is adding apostrophes around database and table names. This is syntactically wrong (unless you have a certain ansi mode turned on). They need to be backtics (`).

Upvotes: 0

sotoz
sotoz

Reputation: 3098

You should try leaving the COMMIT and BEGIN queries alone.

Try:

 // start the transaction
 $this->db->query("BEGIN;");

 //rest of your queries with db->query() go here
 $this->db->query("INSERT INTO subjects
    (subject_code, subject_name, subject_grade, subject_description, subject_category)
    VALUES (:subject_code, :subject_name, :subject_grade, :subject_description, :subject_category);

    SET @last_id = LAST_INSERT_ID();

    INSERT INTO tutorsubject
    (tutor_id , subject_id)
    VALUES (:tutor_id, @last_id);");

 //commit
 $this->db->query("COMMIT;");

MySQL is trying to execute all these as one query and it doesn't recognise the whole command as separate queries. PhpMyadmin separates them on its own and that's why they run correctly there.

Upvotes: 0

Related Questions