Search code examples
phpmysqlsqlpropel

Check if `INSERT SELECT` actually inserted rows

I'm trying to insert some rows in a table using INSERT SELECT using PROPEL ORM. I'm trying to populate the table incrementally until all rows are copied.

$con = Propel::getConnection();
$sql = "INSERT INTO tmp_orders (id, name)
        SELECT  oo.id, oo.slip
        FROM   original_orders oo
            LEFT JOIN tmp_orders tmp ON oo.id=tmp.id
        WHERE tmp.id IS NULL
        LIMIT 10"  ;

        $stmt = $con->prepare($sql);
        $stmt->execute();

Ideally I want to run this script in a loop until all records are copied. I want to stop the loop once there are no more records to insert.

My initial solution is to issue 2 queries. First to select from original_orders to check if no more data is returned by the select then do the INSERT SELECT in another query.
The problem is the query is too slow, so I want to avoid doing the same select twice just to see if there is more data to insert.

Is there a way to get if the INSERT SELECT actually inserted anything? I tried SELECT LAST_INSERT_ID(); after running the INSERT SELECT, but it always returns 0.

Solution

  • Since Propel uses PDO as an abstraction layer, you could use PDOStatement::rowCount.

    In your case this would be done as follows:

    $stmt->rowCount();