error with myslq_select_db()!

hingis_rules

How do Iset the permissions to access a MySQL database? I'm getting an Access denied for user:'@.." error when I run the mysql_select_db(). I get a successfull conection to the server using the mysql_pconnect() function and can't figure out why I cant get access to the database.

Any suggestions?

Thanks

dcparris

Check the mysql table to be sure the user your script runs as has access to the db in question. Also check your syntax and spelling - not only in your script, but in your privileges table.

Did you grant [privileges] to [user] on [database]? I have discovered the majority of my errors (this is likely true for any programmer) is syntax and spelling.

Let me know what happens.

DCP

hingis_rules

Thanks for the info.

I'm a newbie to MySQL and was trying some of the example scripts in the book I'm reading. How do I check the privileges table? Do I run the GRANT thing in my script or somewhere else or does the MySQL administrator have to worry about this?

dcparris

I'm not much newer to MySQL and PHP than you, so don't feel bad. What book are you reading? The MySQL manual, available on their web site (and should be with your installation) describes the process for handling privileges very clearly.

I recommend Beginning PHP from Wrox Press, which offers an excellent discussion of setting up MySQL, or Beginning MySQL from the same folks. Additionally, although I don't generally recommend anything from Sam's, their Web Development with MySQL & PHP has been kind of useful.

Notwithstanding, if you have the ability to grant privileges on your database, you should be able to grant privileges to your script using the the GRANT command at the MySQL prompt.

If your script is going to connect to the DB as "cooldude", you make sure you connect as follows:
$db = mysql_pconnect("localhost", "cooldude", "passwd");

At the MySQL prompt you need to grant "cooldude" the privileges you want him/her/it to have.

GRANT SELECT, INSERT, DELETE, etc.
TO cooldude@localhost (or whatever host they'll connect from)
IDENTIFIED BY 'passwd';

You can do a select * from tables_priv OR user to see who has what privileges on which tables.

DCP

hingis_rules

Thanks for the info and I'll try that.

I'm reading MySQL by Paul DuBois

Hingis Rules

dcparris

I believe your book is published by New Riders. Although I have yet to try their books, I understand they're also excellent.

Hope all that works for you.

DCP

hingis_rules

Yes that book is published by New Riders.

hingis_rules

Even after getting the SQLAdmin to GRANT the priviliges to my user I still get the Access denied for user:'@". when trying to run the command

mysql_select_db($database);
where $database ="test_data"

Shouldn't there be a user name in the error like Access denied for user:'test_user@localhost."?

dcparris

I have not seen that syntax for selecting a DB in MySQL with PHP. That doesn't mean much, but the only syntax I have seen is:

mysql_select_db("test_data"). I am not familiar at all with connecting to the DB using a WHERE statement. Within MySQL, you would type "USE test_data" to use that DB. My guess is that MySQL doesn't recognize your statement with the WHERE clause thrown in. I could be wrong however. Your approach also seems to take more effort than necessary.

The two books I have on PHP & MySQL only use the above syntax when selecting a DB in MySQL through PHP.

DCP

dcparris

In relooking at your last post, I noticed that you didn't include "" marks inside the parentheses. That will definitely cause problems.

Remember, syntax errors can be tricky to find, especially if you're not using an editor that can find PHP & SQL -related errors.

hingis_rules

that where $database ="test_data"

line in my post isn't part of PHP or SQL it was just telling what the value of the $database variable was.

even having the MySQL admin grant all permistions to that database I still get that error.

Hingis Rules

tsinka

Hi,

did you flush the privileges after granting privileges ?

mysqladmin flush-privileges

After changing the privileges for a user you normally have to flush the privileges or to restart the database in order to get the changes online.

tsinka

Hi,

you can select the database in a SQL query.

Assuming that your database is e.g. dbTest and that there is a table like tblTest

you could select data with

SELECT fielda FROM dbTest.tblTest WHERE ....

dcparris

that where $database ="test_data"

line in my post isn't part of PHP or SQL it was just telling what the value of the >>$database variable was.

Looks like perhaps you should trying setting the value of the variable before attempting to use it to connect to MySQL.

$database ="test_data"
mysql_select_db($database);

Your PHP script needs to know the value of the variable before attempting to connect to the database.

Tsinka, what do you think? It seems as if Hingis Rules is calling a PHP function mysql_select_db(), and then using a MySQL where clause to define the variable $database.

DCP

tsinka

Hi dcparris and hingis_rules,

I think the best to see if there's something wrong with the php code is if you would post the php code you use to connect to the database so we can see if there's something wrong 🙂

tsinka

The '@' error message that the access rights to the database for all local users are missing.

You might want to insert a record into mysql.user with a host of localhost, an user like myuser and a password using the PASSWORD() function. There are 14 privileges which you can set to 'Y' and 'N', so

INSERT INTO mysql.user VALUES ('localhost','myuser',PASSWORD('mypassword'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

would grant all privileges to all tables for that specific user. That is probably a bad idea but to test if things work you can try that one. Don't forget to flush privileges.

dcparris

Hingis Rules,

Never mind, I see what you mean about that WHERE comment.

My bad!

Anyway... I tried.

dcparris

Did you ever resolve your select_db problem?