title: SQL transactions in PHP
body: <p>So today the day has come where I need to write SQL transactions, and it's not going well, so far. I have two questions. </p>
  
  <p>I need to do two things at once, execute a select statement and an update. I need to select the id, user and address and I need to update the same row by setting checked to 1. </p>
  
  <p>Reading the <a href="https://mariadb.com/kb/en/library/start-transaction/" rel="nofollow noreferrer">documentation</a> I was able to put the following SQL statement together.</p>
  
  <pre><code> START TRANSACTION;
   SELECT @A:=id, user, address
   FROM addresses 
   WHERE checked = 0 
   AND user IS NOT NULL 
   ORDER BY id ASC 
   LIMIT 1; 
   UPDATE addresses 
   SET checked = 1 
   WHERE id = @A; 
   COMMIT;
  </code></pre>
  
  <p>But it's giving this error:</p>
  
  <blockquote>
    <p>Uncaught PDOException: 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 'SELECT @A:=id FROM addresses WHERE checked = 0 AND user IS NOT NULL ' 
        at line 2 in ... 
        Stack trace: #0 ...: 
        PDO->prepare('\r\n\t START TRANS...') #1 {main} thrown in ... on line 80</p>
  </blockquote>
  
  <p>This is all the code:</p>
  
  <pre><code>&lt;?php
  // Database connection
  $Sserver    = 'localhost';
  $Susername  = 'root';
  $Spassword  = '';
  $Sdatabase  = 'db';
  $Scharset   = 'utf8';
  
  //Set up connection
  $dsn = "mysql:host=$Sserver;dbname=$Sdatabase;charset=$Scharset";
  $opt = [
      PDO::ATTR_ERRMODE            =&gt; PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE =&gt; PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES   =&gt; false,
      PDO::ATTR_STRINGIFY_FETCHES  =&gt; false,
      PDO::ATTR_ORACLE_NULLS       =&gt; PDO::NULL_NATURAL,
      PDO::ATTR_CASE               =&gt; PDO::CASE_NATURAL
  ];
  $dbcon = new PDO($dsn, $Susername, $Spassword, $opt);
  
  $query ='    
       START TRANSACTION;
       SELECT @A:=id, user, address
       FROM addresses 
       WHERE checked = 0 
       AND user IS NOT NULL 
       ORDER BY id ASC 
       LIMIT 1; 
       UPDATE addresses 
       SET checked = 1 
       WHERE id = @A; 
       COMMIT;';
  
  $query = $dbcon-&gt;prepare($query);
  if(!$query-&gt;execute())
  {
      //If query not successful.
      echo 'SQL ERROR: error in function ';
      die();
  } 
  $result = $query-&gt;fetch(PDO::FETCH_ASSOC);
  if(empty($result['id']))
  
      echo '';
  else
      echo $result['id'];
  
  ?&gt;    
  </code></pre>
  
  <p>So my first question is, what is wrong with my code? I will move this in the future to a Windows Server 2016 with Windows SQL, but for now I want it to work on my xampp instance running mariadb.</p>
  
  <p>And my other question, what's the most efficient way of executing this query with my $dbcon PDO setup? I don't think I need a prepare statement...</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I still want to use the transaction method. Mark Baker pointed me in the right direction but my code is still not working. Now my code is this:</p>
  
  <pre><code>// Database connection
  $Sserver    = 'localhost';
  $Susername  = 'root';
  $Spassword  = '';
  $Sdatabase  = 'db';
  $Scharset   = 'utf8';
  
  //Seting up connection
  $dsn = "mysql:host=$Sserver;dbname=$Sdatabase;charset=$Scharset";
  $opt = [
      PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =&gt; true
  ];
  $dbcon = new PDO($dsn, $Susername, $Spassword, $opt);
  
  //SQL Transaction
  $dbcon-&gt;beginTransaction();
  $dbcon-&gt;exec('SELECT @A:=id, user, address
       FROM addresses 
       WHERE checked = 0 
       AND user IS NOT NULL 
       ORDER BY id ASC 
       LIMIT 1;');
  $dbcon-&gt;exec('UPDATE addresses 
       SET checked = 1 
       WHERE id = @A;');
  $dbcon-&gt;commit(); 
  $result = $dbcon-&gt;fetchAll();
  print_r($result);
  </code></pre>
  
  <p>But this will output </p>
  
  <blockquote>
    <p>Call to undefined method PDO::fetchAll()</p>
  </blockquote>
  
  <p>But the first exec function returns 0 so something is wrong there. Can someone help me get this straight please?</p>