Mysql query for using count on a view in php

Question

I have a query:

$result = mysql_query("CREATE VIEW temporary(IngList) AS (
                         SELECT DISTINCT (r1.Ingredient) 
                           FROM recipes r1, 
                                recipes r2 
                          WHERE r1.Country = '$temp' 
                            AND r2.Country = '$temp2' 
                            AND r1.Ingredient = r2.Ingredient) 
                       SELECT COUNT(*) FROM temporary");

I want the query to make a view called temporary and have it return a count of the number of rows in the view temporary. I know this code works without the SELECT COUNT(*) because I checked my database and the view is created.

Yet this code throws 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 'SELECT COUNT(*) FROM temporary' at line 1

I checked the syntax and it seems to be correct. What seems to be the problem because its quite frustrating.

Answer 1

From the mysql_query documentation:

mysql_query() sends a unique query (multiple queries are not supported)...

You can't create the view, and select from it in a single mysql_query. The view is unnecessary:

$sql = sprintf("SELECT COUNT(DISTINCT r1.Ingredient) 
                  FROM recipes r1
                 WHERE r.country = '%s'
                   AND EXISTS(SELECT NULL
                                FROM recipes r2 
                               WHERE r2.Country = '%s' 
                                 AND r1.Ingredient = r2.Ingredient)",
                $temp, $temp2);

$result = mysql_query($sql);