Syntax for “RETURNING” clause in Mysql PDO

Question 1

I'm trying to add a record, and at the same time return the id of that record added. I read it's possible to do it with a RETURNING clause.

$stmt->prepare("INSERT INTO tablename (field1, field2) 
                               VALUES (:value1, :value2)
                          RETURNING id");

but the insertion fails when I add RETURNING. There is an auto-incremented field called id in the table being added to.

Can someone see anything wrong with my syntax? or maybe PDO does not support RETURNING?

Question 2

I don't think it has anything to do with PDO supporting it or not. RETURNING is supported by Oracle and PostgreSQL but not by MySQL.

Use PDO::lastInsertId instead.

Question 3

Some good old dynamic SQL query cobbling-together...

$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
               !empty($_GET['city'])   ? 'AND city   = :city'   : null,
               !empty($_GET['gender']) ? 'AND gender = :gender' : null);

...

if (!empty($_GET['city'])) {
    $stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
}

...

You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.

Question 4

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\r\n|\r|\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        $statement->execute();
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
                        }
                    }
                }

Question 5

Missing colons

:blabelimage_rec, **:**asound_rec, **:**bsound_rec, :featured_rec, :format_rec

Question 6

I take that back looks like you can use the cursor orientation contants to select the result... sample code coming... I havent tried this so you may need to play a bit. This is also based on the assumption that a PDO::FETCH_ORI_FIRST acts like a data_seek and leaves the cursor on the first position as opposed to returning it to whatever it was before.

$stmt = $pdo->prepare('SELECT id FROM table', array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();

$first = $pdo->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_FIRST);
$first_row = $first['id'];

// other stuff

// first iteration we rewind to the first record;
$cursor = PDO::FETCH_ORI_FIRST;

while (false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC, $cursor))) {
   $id = $row['id'];
   // successive iterations we hit the "next" record
   $cursor = PDO::FETCH_ORI_NEXT; 
   echo $id;
}

I dont think you can rewind a statement... Assuming these blocks arent seprated by a bunch of intermediary logic id just do it in the loop.

$STH->setFetchMode(PDO::FETCH_COLUMN); // no need to pull an array
$count = 0;
while ($id = $STH->fetch()) {      
  if($count === 0) {
   $first_row = $id;
  }
  echo $id;
  $count++;
}

Syntax for “RETURNING” clause in Mysql PDO

Answers

php,mysql,pdo,prepared-statement