0

I have the following code that is executing a CREATE OR REPLACE statement and then running a SELECT statement on those views. Unfortunately, the SELECT statement is coming back that the views don't exist. How do I run the views and let finish before the select?

$dbh = self::sql_connect( $sql_details );
        $dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

        $CRV = "CREATE OR REPLACE VIEW ".$setdbaseBrand."storeCheckRecords_max as (
            SELECT MAX(datetimesql) as maxdatetimesql, upc, store_id
            FROM ".$setdbaseBrand."storeCheckRecords
            GROUP BY upc, store_id
            );

            CREATE OR REPLACE VIEW ".$setdbaseBrand."storeCheckRecords_ext as (
            SELECT store_id, $fields_create_view
            FROM ".$setdbaseBrand."storeCheckRecords_max
            );

            CREATE OR REPLACE VIEW ".$setdbaseBrand."storeCheckRecords_extended as (
            SELECT store_id, $fields_create_view_more
            FROM ".$setdbaseBrand."storeCheckRecords_ext
            GROUP BY store_id
            );";

        $sth = $dbh->prepare($CRV);
        $sth->execute();

    $data = self::sql_exec( $db, $bindings,"SELECT SQL_CALC_FOUND_ROWS @storeid:=z.id,z.biz_name, z.wf_store_name, z.e_address, z.e_city, z.e_state, z.e_postal, CASE WHEN total_sales IS NULL AND prev_total_sales IS NULL THEN 'CV' ELSE IFNULL(total_sales,0)-IFNULL(prev_total_sales,0) END as diff_total_sales, IFNULL(d_source,'N/A') as d_source, $fields_selectVoid FROM.........
Damon
  • 63
  • 3
  • 1
    did you turn on exceptions? you're simply assuming the prepare/execute succeeded. never assume success with a DB operation. – Marc B Aug 13 '15 at 18:46
  • You merely need to run the 3 statements separately. PDO will not support multiple SQL statements in one `query()` or `prepare()` call the way you've attempted it, and the overhead potentially saved by bundling them in one execution is immeasurably small. So just split this into 3 statements -- and since there are no params, you may just directly execute the 3 `CREATE` statements with `exec()` rather than `prepare()/execute()`. – Michael Berkowski Aug 13 '15 at 20:40
  • If you `var_dump($dbh->errorInfo());`, you'll find MySQL reporting a syntax error near `CREATE` (at the second statement). – Michael Berkowski Aug 13 '15 at 20:42

0 Answers0