Problem with Create View

MichaelHe

Why won't this work

mysql_query ("CREATE VIEW CRCount AS SELECT OpretData AS Dato, COUNT(*) AS Antal FROM Kunder GROUP BY OpretDato");
$getlist = mysql_query("SELECT * FROM CRCount ORDER BY Antal DESC");
while ($row = mysql_fetch_array($getlist)){$highest=$row['Antal'];}

It gives this error back
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/www.festfagbogen.dk/www/Admin/Count/Counter.php on line 3

Where line 3 i the WHILE line.

tomhath

Try checking mysql_error after both the CREATE and the SELECT.

mysql_query ("CREATE VIEW CRCount AS SELECT OpretData AS Dato, COUNT(*) AS Antal FROM Kunder GROUP BY OpretDato") or die("Create failed: " . mysql_error());
...

MichaelHe

If I try that it says
Create failed: CREATE VIEW command denied to user 'my database username'@'80.196.101.254' for table 'CRCount'

What does that mean ?

tomhath

The MySQL user doesn't have permission to create a view.

http://dev.mysql.com/doc/refman/5.0/en/grant.html

MichaelHe

Oh I see

But I can't seen to get it to work.

I have tried this
mysql_query ("GRANT CREATE VIEW ON MyDBName.* TO MyUserName@MyHostName");

And then it says:
Create failed: Access denied for user 'MyUserName'@'%' to database 'MyDBName'

Is it because my webhost has done so I can't chacge these privileges ?

MarkR

Can you create a view from the MySQL command line utility?

Does it work on your dev server?

Are your dev & production servers using the same version of MySQL?

Mark

tomhath

It sounds like you need to ask the webhost to grant you the permissions.