Randy Posted May 12, 2011 Share Posted May 12, 2011 windows 7 x64 php v 5.3.1 MySQL client version: 5.1.41 I was trying to get this: "SELECT name, CONCAT_WS('<br />', address1, address2), city, state, stores.zip_code, phone, store_image,ROUND(return_distance($lat, $long, latitude, longitude)) AS distance FROM stores LEFT JOIN zip_codes USING (zip_code) ORDER BY distance ASC LIMIT 3" to work in my distance.php script (store_image was an added field). It took me a while to debug this problem, but I eventually got it to work. My 'someuser' (and I'm guessing others who jumped from your PHP6 MYSQL5 book to this one) doesn't have the same privileges to mysqli functions as the 'root' user does, so I couldn't get this script to run outside the admin programs. I don't want to give 'someuser' ALL PRIVILEGES, so which additional privileges do I need to enable for 'someuser'(currently SELECT, INSERT, UPDATE, DELETE, FILE)? Thanks for the great books (and any help), ~Randy Link to comment Share on other sites More sharing options...
Larry Posted May 13, 2011 Share Posted May 13, 2011 Thanks for the nice words on the books. I think you're a bit confused by what's going on here. First of all, MySQL users don't have privileges to "mysqli functions". Second, this query is just a SELECT query, so the MySQL user that's trying to execute the query only needs SELECT privileges. Link to comment Share on other sites More sharing options...
Randy Posted May 13, 2011 Author Share Posted May 13, 2011 Larry, Thanks for the response. I've figured it out (after a bit more debugging and using phpmyadmin). This wan't a MYSQL DATA privilege (select, insert, update, delete, or file) it was a STRUCTURE issue in the privileges. The only thing I toggled on was the Execute without the Executue on it doesn't work. GRANT SELECT , INSERT , UPDATE , DELETE , FILE , EXECUTE ON * . * TO 'username'@'localhost' (or 'someuser' or whoever) Else you get this error Warning: mysqli_query() [function.mysqli-query]: (42000/1370): execute command denied to user 'username'@'localhost' for routine 'zips.return_distance' in C:\xampp\xampp\htdocs\PHP5Advanced\Chapter3\distance.php on line 99 ~Andy Link to comment Share on other sites More sharing options...
Larry Posted May 13, 2011 Share Posted May 13, 2011 My apologies. I totally overlooked the use of the stored procedure return_distance() that's in the SELECT query. Yes, for that you need EXECUTE privileges. Kudos for figuring it out and thanks for posting your solution. Link to comment Share on other sites More sharing options...
Recommended Posts