current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

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);
share|improve this answer
add comment
up vote 0 down vote

For starters you have two statements. What you're writing looks more like a stored procedure. Even if it worked, you would need a semicolon at the end of the first statement. And another statement somewhere saying "DROP VIEW ...." when you are done.

And a temp view is a bit of a non sequitur. I can't find any reference to "CREATE VIEW temporary". Or maybe it's to create a view named temporary with an argument? Views don't take arguments.

I think you might get what you want with a semi-simple SQL statement something like:

$result = mysql_query(

"SELECT COUNT(DISTINCT r1.Ingredient)
 FROM recipes r1
 JOIN recipes r2 ON r1.Ingredient = r2.Ingredient
 WHERE r1.Country = '$temp' 
     AND r2.Country = '$temp2'");
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.