Problems using PHP+mySQL+IIS 5 -- HELP

[Anon]

I have sucessfully installed PHP 4, mySQL, and IIS on my Win2k box. I tested all three parts seperately (running a sample php script, using mySQL from the commmand line, and accessing IIS through IE.), and they work fine seperately. The mysql-nt.exe server is running as a windows NT service.

However, I have problems connecting to mySQL using PHP. I have given execute permissions to php.exe and it works fine.

I created a database in the command line using this:

mysqladmin create newsDB --host="192.168.1.1" --user="jason" --password="something"

I can access that database perfectly fine in the command line, but in the PHP script, I write:

$hostname="192.168.1.1";
$user="jason";
$password="something";
$which_db="newsBD";

mysql_pconnect($hostname, $user, $password);
mysql_select_db($which_db);

I get an error that says: You don't have permission to jason@kymira (Using password="YES")

(kymira being my computer's name)

Then I tried this after seeing it in someone else's code:

mysql_pconnect($hostname, $user, "");
mysql_select_db($which_db);

Now, I can connect to mySQL, but I can't select which database to use. The mySQL error logs says the connection was aborted because of unknown causes.

Do I have to change the permissions on the mysql directoy? Any help is much appreciated.

[crowezr]

When you are connecting to the database, you need to assign the connection to a variable to use the mysql_select_db function.

$connection = mysql_pconnect("localhost", "username", "password");

mysql_select_db("mydbase",$connection);

[skissiks]

Jason,

just curious, did Zach's suggestion work for you?

you are about one step ahead of me, but this weekend i will begin testing hitting my MySQL db and wouldn't mind heading this issue off at the pass. i have an identical setup, with php4 , mysql 3.23.38 , and iis on win2k. (by the way, simple php scripts FLY on win2k.... love it!)

thanks for whatever info you come up with... 🙂

:wq

[Anon]

Well, it kinda of helped me.... I went through the "mysql" database (mysql -u root -p mysql), and I added the user "production to the "user" table. (Something I didn't do before, thus, the error message "you don't have permission to prodcution@kymira"

Now, I can connect perfectly fine to the database, using this code:

$hostname = "192.168.1.1";
$username = "production";
$password = "something";
$db_name = "news_db";

db_connection = mysql_connect($host, $username, $password) or die ("Unable to connect to server: $host");

$db_selected = mysql_select_db($db_name, $db_connection) or die ("Unable to select database: $db_name");;

mysql_close($db_connection);

I am able to connect to the database, but can't select "news_db" logging in as production. However, if I login as root, then I can able to connect to the database and select the "news_db" database.

What am I doing wrong? Did I add the "production" user the wrong way (see how I did it above)?

[mithril]

Jason,

FYI, passing the mySQL connection resource identifier ($db_connection in your example) to the mysql_select_db function is an optional arg for mySQL connections. Once the connection is made, the script will try to use whatever open resource it can. I expect passing the resource identifer would be used mostly when your expecting to deal with a large number of concurrent or simultaneous db transactions to make everyone play nice and not steal their mates connection. But enough theory.... on to your problem🙂

I notice that you are trying to select the db when connected to 192.168.1.1. Is the mySQL installation on a different box from the webserver? Can you connect and select a db from 127.0.0.1 or localhost? The most like problem is that you have not granted the "production" account access from "%" or "192.168.1.1" to the "news_db" database, hence the access refusal. To check your permissions, login to mySQL via command line and execute the following query:

SELECT user,host,db FROM mysql.db;

If you don't see the user "production" corresponding to host "%" or "192.168.1.1" as well as "localhost" on the "news_db" database, you've found your problem. To resolve it, run a GRANT query as follows:

GRANT ALL PRIVILEGES ON news_db.* TO "production@%" IDENTIFIED BY "something" WITH GRANT OPTION;

This statement will grant superuser privileges to the "production" account on all tables in the "news_db" database from any IP address. The WITH GRANT OPTION part is optional, ALL PRIVILEGES should be replaced with whatever privileges you need (INSERT,SELECT,UPDATE,DELETE are the usual suspects), and "%" can be replaced with "192.168.1.1" for a more secure environment (prevents non-local IP's from accessing the resource). You might also want to look into changing the password, or deleting, the "root" account and deleting the "" account as they are bother superuser accounts which everyone know exists. For more info, read the mySQL docs at:

http://www.mysql.com/documentation/

HTH!!

Cheers,

Geoff A. Virgo

[Anon]

Geoff,

Woooohoooo!!!!! That was the problem! Thank you so much for your help, I really appreciate it.

[Anon]

After the trouble I had with this, I have decided to write a guide for myself about how to get this combination (PHP + MySQL + IIS 5.0) up and running, just in case I forget in the future.

Then I thought, "Maybe someone else will run into the same problems I did. It might be nice to provide some documentation to help them."

So I guess I'll publish it on the web. Anyone want to proof read for me? I'd really appreciate comments.

Should I post it to this forum?

Any other place that I should ask and see if they want a copy?

Thanks,
Jason La
[e-mail]

[corsc]

This sounds like a problem with the MySQL permissions, not a problem with PHP.

You might want to check the permissions on the db, I have found that connecting through php and command line are treated as different users.

From memory (it has been a while) it was something similar to the users

u: root
and
u: root@localhost or [e-mail]

these are treated as different users.

Also my suggestion to you would be get ODBC for mysql and use that. It is a great addition to MySQL for windows and helps you use the odbc functions which i have found are more reliable. (It also helps if you get sick of MySQL, not that you will do that :-)

[skissiks]

Jason,

I for one would LOVE to see this guide you are talking about! That was a nice post from Geoff Virgo that apparrantly saved the day!

Let me know if I can help you in any way. I'm looking for ways to increase my knowledge in the php/mysql area...

:wq