can't connect to mysql with php

Question

I can't seem to connect to mysql with a php script, even though I can connect fine with phpmyadmin. I created a user with a password, and gave it the proper priveleges for the db, but everytime it connects it dies saying access denied. I am using xampp on a windows xp box. Firewalls are all disabled, and I've checked the username nad password are correct. Here's the code: $conn=mysql_connect('localhost','westbrookc16','megadots') || die (mysql_error()); Do usernames have to be in a specific format or something?

Answer 1

and gave it the proper priveleges for the db

How? What host did you use for the user when granting it?

This is how it is normally done:

GRANT ALL ON mydb.* TO 'someuser'@'somehost' identified by 'password';
FLUSH privileges;

in your case 'somehost' should probably be 'localhost' or '127.0.0.1' (see other post)

Syntax reference: http://dev.mysql.com/doc/refman/5.1/en/grant.html

Answer 2

This will either be a typo or you have not granted privileges. Sometimes it can be alarmingly difficult to spot these.

I suggest don't use phpmyadmin but download a copy of SQLYOG (the free community edition is great) and try to login with your user via that. Once you have the issue diagnosed there copy/paste username/password back to your script.

Incidentally phpmyadmin is fine, but a program such as sqlyog is generally more convenient, so worth checking out anyway - I'm sure you'll be converted. If sqlyog isn't quite to your taste there are several other free and commercial alternatives.

Answer 3

I have a hunch that the problem here is the host you granted it to, though it's really not more than an educated guess. If you grant access myuser@'127.0.0.1' or the servers actual ip address, you won't be allowed to connect using localhost as host. This is due to the fact that when "localhost" is specified as host, php will assume that you want to use a unix socket instead of network sockets, and in that context 127.0.0.1 isn't the same as localhost.

From the manual entry for mysql_connect():

Note: Whenever you specify "localhost" or "localhost:port" as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use "127.0.0.1" instead of "localhost". If the MySQL client library tries to connect to the wrong local socket, you should set the correct path as Runtime Configuration in your PHP configuration and leave the server field blank.

Hope this isn't totally redundant. :)

Answer 4

I've seen this before, where one mysql user logs in via php and another does not. Sometimes the user even works from the commandline but not from php.

It's always been what Emil is reffering two. A mysql user is really user/host pair. so the user megadots@localhost and the user megadots@mycoolhost are listed in the mysql.user table as two seperate records.

If you can login from the command line run this query.

SELECT user, host FROM mysql.user

you should see the full list of users and thier hosts.

if you recognize the phpMyAdmin user that is working, look at the host column that's probably the host you want to use.

to reset the host run these queries (!be careful doing this your working with the privilages table for the entire mysql installation)

update mysql.user set host = 'hostname' 
where user = 'username' and host = 'oldhostname';

flush privileges;

If you see a record with the username and % as the host that will have priority over everything else, if there are multiple records for the user and % as the host for one of them, it's possible that the username with % as the host has the wrong password and no matter how manytimes you reset username@localhost's password it's invalid because it'll be compared to username@% on login.