title: In mySQL, how do I get and set a value in one query?
body: <p>I am trying to get the highest Line Order number in the table, set it to a variable, increment it, then insert it with new data as a new record.
  The script is in PHP and $SQL is for mySQL</p>
  
  <pre><code>$SQL="  
        BEGIN
          DECLARE lineOrder INT DEFAULT 1;
          SET lineOrder = (
             SELECT IFNULL(`Line Order`,1)
             FROM `router`
             ORDER BY `Line Order` DESC
             lIMIT 1);
          lineOrder = lineOrder + 1;
          INSERT INTO `router`(`Line Order`, `Estimated Time`,
                                 `Estimated Time Unit`, `Work Center`,
                                 `Work Description`)
          VALUES (lineOrder,?,?,?,?);
        END;
      ";
  $stmt = $GLOBALS['mySQLConnection']-&gt;prepare($SQL);
  echo $GLOBALS['mySQLConnection']-&gt;error;
  </code></pre>
  
  <p>This is the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET lineOrder = (SELECT IFNULL(<code>Line Order</code>,1) FROM <code>router</code> ' at line 2</p>