Chapter 3 Mysql Function Into Php

[Randy]

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

[Larry]

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.

[Randy]

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

[Larry]

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.