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

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 2

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.

Answer 3

If you are using Linux use the synaptic package manager to find and download all the libraries and mods that PHP and MySQL need so they can both connect. My problem was that PHP scripts running alone worked on the server side but when I tried using PHP scripts to connect to MySQL it would not connect I would only see a plain white page. Then I noticed I did not have the MySQL client libraries that PHP uses to conect to MySQL. I only had the MySQL server libraries. Once I installed the client side libraries and restarted the apache server my PHP scripts had no problem connecting. By default PHP 5 does not come installed with the MySQL client side libraries.

Answer 4

In my case, it turned I had deleted the user that was used to create the database I was using. Normally this should give the error 'user specified definer does not exist', but for some reason it was simply returning the more general access denied to my PHP code. I'm not sure why I could still login through the command line and other interfaces. To correct my problem I recreated the user that was deleted.