3

I am trying to connect my PHP code to MySQL safely with the following code:

<html>
    <?php
        $con = mysql_connect("localhost:3306","root","password");
        if(!$con)
        {
            die('Could not connect: ' . mysql_error());
        }
        else
        {
            echo "Connection established!";
        }
        mysql_close($con);
    ?>
</html>

But I keep getting the following error message:

Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'localhost' (10061) in C:\xampp\htdocs\database_connect.php on line 5 Could not connect: Can't connect to MySQL server on 'localhost' (10061)

Here are the troubleshooting steps I took:

  • Checked whether mysqld is running in Windows Task Manager Processes - it is
  • Checked whether MySQL was running on the host by typing in Windows command prompt: "telnet 192.0.0.1 3306" and got the message "Could not open connection to the host, on port 3306: connection failed"
  • Checked whether Windows Firewall was blocking MySQL - MySQL is an exception.

How do I get this code to work safely? And how do I check basic useful information about my MySQL like username?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Anthony
  • 3,990
  • 23
  • 68
  • 94
  • 1
    I recommend you move to mysqli if you can. It's an updated and better version of the mysql extension. – waiwai933 Feb 08 '10 at 03:38
  • Thanks for your reply waiwai! Since I am new to PHP, PDO and mysqli, I did some research on your suggestion (mysqli) and discovered that there are 3 main API options for connecting to MySQL: PHP's MySQL Extensions, PHP's mysqli, and PHP Data Objects (PDO - currently learning now). PHP's MySQL Extensions are intended for use only with MySQL versions older than 4.1.3. (I guess this is what I was using) and its strongly recommended to use mysqli (which has many benefits over MySQL extensions), and PDO which allows you to switch database types with minor changes to PHP code. It all makes sense! – Anthony Feb 08 '10 at 19:28

7 Answers7

7

You can check that the MySQL server is bound to port 3306 using tcpview. More simply, drop the port from the host specifier. The driver should then attempt to use a named pipe, rather than a TCP socket.

On an unrelated note, I strongly urge you to switch to the PDO MySQL driver. The one you're using is terribly out of date. One big advantage is PDO supports prepared statements, which offer security and efficiency benefits.

Edit:

This doesn't answer your main question, but posting this information in a comment would be a mess.

Rather than W3Schools, check out the resources suggested in:

Community
  • 1
  • 1
outis
  • 75,655
  • 22
  • 151
  • 221
  • Thanks for your reply and recommending tcpview (Netstat is on my list of things to learn). I ran it and did not see 3306 on the local address. Also thanks for recommending PDO - I'm new to this and was following w3schools example. I will do some research on it. Now how do I enable MySQL to network to the code via 3306?? – Anthony Feb 08 '10 at 00:58
  • Definitely right about PDO; it amazes me how many people I see still getting started with the mysql_* commands instead. There are a lot of horrible, outdated tutorials out there! PDO is much better and not any more difficult to start with. – JAL Feb 08 '10 at 01:36
  • @01010011: Don't bother having MySQL support TCP; it's less secure and shouldn't be done on a development server. Use 'localhost' as the host name and be done with it. As for where the settings are, see my 1st comment to Mr-sk's answer. – outis Feb 08 '10 at 01:36
  • @Code Duck: W3Schools has much to answer for. – outis Feb 08 '10 at 01:36
  • Here is the link to the w3schools tutorial I was following: http://www.w3schools.com/php/php_mysql_connect.asp – Anthony Feb 08 '10 at 02:10
  • @01010011: I've seen it. Don't bother with W3Schools. – outis Feb 08 '10 at 02:16
  • @0101001 http://www.kitebird.com/articles/php-pdo.html is an excellent tutorial on PHP/PDO if you're interested. – JAL Feb 08 '10 at 02:18
  • @Outis, wow thanks for the links, I browsed them, bookmarked them, and will return to them after reading the PDO tutorial/link. After reading it, I'll rewrite my code using it and post it here ASAP. Thanks again – Anthony Feb 08 '10 at 02:39
  • @Code Duck. Yes I am interested. I started reading the link Outis recommend but I need a backup just in case. Thanks. – Anthony Feb 08 '10 at 02:59
  • @Outis, the installation part (Windows) of the tutorial said to activate PDO by modifying the php.ini file with this line: "extension=php_pdo.dll" and below, enable the database-specific DLL with this line: extension=php_pdo_mysql.dll . Now, in XAMPP/php/php.ini I saw the first line, but I did not see the second,so I put it in and saved the file, right? – Anthony Feb 08 '10 at 03:29
  • @01010011: that's right. Make sure php_pdo_mysql.dll exists in "XAMPP\php\ext\"; if it doesn't, you might need to reinstall XAMPP. After that, restart Apache. – outis Feb 08 '10 at 03:51
  • @Outis, I checked "XAMPP\php\ext\" and php_pdo_mysql.dll does exists. Great! – Anthony Feb 08 '10 at 18:08
  • @Outis, after stopping and restarting Apache and MySQL from XAMPP's control panel, I got two Warnings: "Module 'pdo_mysql' already loaded. – Anthony Feb 08 '10 at 18:45
  • @01010011: warnings you can often ignore. In this case, it means the extensions are already compiled into PHP. You can check this by commenting the lines to load the PDO and PDO MyQSL extensions, then creating a PHP page containing ``. Finally, restart Apache and view the page in a browser. You should see some options beginning with '--with-pdo' in "Configure Command", and there should be a PDO section lower in the page. – outis Feb 08 '10 at 20:46
  • @Outis, guess what! The w3schools' code above actually connected to the MySQL database! The problem was my firewall needed an exception to port 3306. I started the MySQL Server Instance Config Wizard to change my password yet again and while there I noticed that by default, the wizard provides networking on port 3306, but the firewall exceptions checkbox was not checked. So I checked it and ran the MySQL commands: show variables like 'port'; and show variables like 'skip_networking'; and this time the results were 3306 and OFF. And the code worked. To bad though, cause I am going to use PDO. – Anthony Feb 09 '10 at 16:13
  • Above links are not alive now? – Emma Oct 07 '14 at 06:41
1

Maybe you don't have remote connections set on the MySQL install. You're attempting to connect over a port, which is the same as a remote connection. It's been a while since I've done MySQL, but this is a dead giveaway:

Checked whether MySQL was running on the host by typing in Windows command prompt: "telnet 192.0.0.1 3306" and got the message "Could not open connection to the host, on port 3306: connection failed"

It's not going to work. Are you sure it's configured for port 3306 and not another port? Double check that.

The code itself looks fine and is not the issue. The port is clearly the issue.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
mr-sk
  • 13,174
  • 11
  • 66
  • 101
  • Thanks for your reply Mr-sk. How do I check whether MySQL is configured for port 3306 after it is installed? What commands do I use to get this info? I may have disabled it when I installed MySQL. – Anthony Feb 08 '10 at 00:03
  • What about using the IP 127.0.0.1? – Joel L Feb 08 '10 at 00:05
  • Thanks for the reply Joel. What do you mean? How do I check the servername and username to make sure I put in the correct names? usernamemysql_connect(servername,username,password); – Anthony Feb 08 '10 at 00:27
  • @01010011: Under XAMPP, the MySQL configuration file is stored at "%xamppdir%\mysql\bin\my.ini" (note: the environment variable "%xamppdir%" won't be defined on your system; it's just there as a placeholder). Look for "port" and "socket" options in the [mysqld] section. – outis Feb 08 '10 at 00:56
  • As for checking username and password, try connecting with a client. Xampp installs a command line client at "%xamppdir%\mysql\bin\mysql.exe", or you can try MySQL Workbench (http://www.mysql.com/products/workbench/) or one of the other suggestions at http://stackoverflow.com/questions/9185/what-is-the-best-mysql-client-application-for-windows. – outis Feb 08 '10 at 00:57
  • Read through http://dev.mysql.com/doc/refman/5.1/en/user-account-management.html for information about handling user accounts. – outis Feb 08 '10 at 01:38
  • @outis, I need that, thanks, cause I keep have a reoccurring problem of being lockout of MySQL which leaves me feeling powerless. – Anthony Feb 08 '10 at 02:41
1

Note: This is not the solution to the OP's problem, but I will keep my answer for historical reasons and also because it might be the issue for some other folks.

Are you on Windows Vista/7 and running PHP 5.3.1?

A recent change in the MySQL library in PHP right now (now using mysqlnd) is causing problems when connecting to localhost. A bug has been filled but until then, make sure that your hosts file contains an entry for localhost.

Your hosts file localhost entries must look like such:

127.0.0.1  localhost
#::1       localhost

As you can see, the IPv6 entry is commented out and the IPv4 entry isn't.

The hosts file is located at:

%WINDIR%\System32\drivers\etc\hosts

Source

Andrew Moore
  • 93,497
  • 30
  • 163
  • 175
  • Thanks for your reply Andrew and thanks for that very useful source link. Im using XP and XAMPP. XAMPP's read me document stated among other things: "PHP 5.3.0", "Apache 2.2.12 (IPV6 enabled)" I checked the host file and "127.0.0.1 local host" was the only entry there. The link you provided stated that the line "#::1 local host" should be removed but I did not have that line. I also did not see anything concerning IPv4 or IPv6 in this file. So what do I do now? – Anthony Feb 08 '10 at 01:19
  • @01010011: In this case, this is not your issue. Check the answer above to make sure MySQL is listening on 3306. – Andrew Moore Feb 08 '10 at 03:34
  • Marc B provided a nice way of finding this out. Can I change the port and skip_networking values from the MySQL Command Line? – Anthony Feb 08 '10 at 18:14
1

You can't recover the password, but you can create a new one. Turn off the MySQL service and execute:

cd c:\mypathtomysql\bin
mysqladmin -u root password NEWPASSWORD
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
useless
  • 1,876
  • 17
  • 18
  • Yeah thanks for this. I still don't know why my password did not work for a day logging onto MySQL Command Line Client and that is very frustrating! Suddenly it started working the next day and this is the second time this has happened. So I changed the root hope this stops that problem – Anthony Feb 08 '10 at 01:30
  • My password stopped working again. However, I started the Server Instance Configuration Wizard and changed it and it worked, I got back in. – Anthony Feb 09 '10 at 15:47
1

If you can access the MySQL shell, you can check the server's networking configuration like this:

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  | 
+---------------+-------+

mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   | 
+-----------------+-------+
1 row in set (0.00 sec)

If skip_networking is set to 'ON', then your MySQL server has been configured to NOT allow access via the network and will function only via local sockets. Otherwise it'll be listening on the port specified in the port configuration variable.

dipdapdop
  • 126
  • 1
  • 10
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Nice one Marc and thanks for your reply! My port value was 0 and my skip_networking value was 'ON'. How do I change these values from the MySQL Command Line? – Anthony Feb 08 '10 at 17:26
  • I don't know if they can be changed from within mysql. My understanding is that if mysql starts up with skip_networking=on, it simply does not load the networking code and cannot be forced to load it afterwards. You'll most likely have to modify the server's my.ini (or my.cnf or whatever it's called on your particular install). skip-networking is set in the [mysqld] section, and port numbers in both the [client] and [mysqld] sections. – Marc B Feb 08 '10 at 22:50
  • I had a problem with my password for a third time and this time, I started the Server Instance Configuration Wizard and changed it and it worked. Anyway, while going through the configuration steps, I noticed that networking was on by default but the firewall exception for port 3306 was not checked. So I checked it and when the config was finished I tried the commands: show variables like 'port'; and show variables like 'skip_networking'; and this time the results were 3306 and OFF. – Anthony Feb 09 '10 at 15:52
1

Ok everybody, here is my very first attempts at connecting MySQL and PHP using PDO as suggested by Outis. It worked. 

<?php 
$user = root;
$pass = password;
try
{
    $dbh = new PDO('mysql:host = localhost; dbname=databaseName', $user,$pass);
    if($dbh)
    {
        print "Connected successfully";
    }
}
catch (PDOException $e)
{
    print "Error: " . $e->getMessage(). "<br/>";
    die();
}
?>

Here is my second attempt - this time I am trying to do a query

<?php 
$user = root;
$pass = password;
try
{
    $dbh = new PDO('mysql:host = localhost; dbname=databaseName', $user,$pass);
    foreach($dbh->query('SELECT * FROM tableName') as $row)
{
    print_r($row);
}
$dbh = null; 
}
catch (PDOException $e)
{
    print "Error: " . $e->getMessage(). "<br/>";
    die();
}
?>

After running this code, I get the following results:

Array ( [exo_flowers_ID] => 1 [0] => 1 [name] => Dendroseris Neriifolia [1] => Dendroseris Neriifolia [country] => Chile [2] => Chile [env_workers_id] => 1 [3] => 1 ) Array ( [exo_flowers_ID] => 2 [0] => 2 [name] => Snowdonia Hawkweed [1] => Snowdonia Hawkweed [country] => North Wales [2] => North Wales [env_workers_id] => 1 [3] => 1 )

All I wanted was to display the contents of a row or column , but instead I got all of the contents plus all these brackets. How do I display only the contents of a table's row or column?

And finally, how do I so a query like: SELECT * FROM tableName WHERE 'columnName1' = 'somename' AND 'columnName2' = 'someothername'; ?

Anthony
  • 3,990
  • 23
  • 68
  • 94
0

I solved the same problem "Can't connect to MySQL server on 'localhost'" with the following steps (Windows 7, XAMPP v.3.2.1 installed, php connection to MySQL didn’t work):

  1. Open XAMPP Control Panel (my version is v.3.2.1). Press button "Shell" on the right side of the panel.

  2. Window opens with a title: "Administrator: XAMP for Windows – mysql –u root" and with the following text:

Setting environment for using XAMPP for Windows
comp@COMP c:\xampp
  1. Input: #mysql

Obtained answer: Welcome to the MySQL monitor…

Then input: mysql>create database mdb;

Answer: ERROR 1044 (42000): Access denied for user ‘’@’localhost’ to database ‘drawdb’

Input: #mysql –u root

Answer: Welcome to the MySQL monitor…

Input: mysql>create database mdb;

Answer: Query OK, 1 row affected (0.00 sec)

Input: mysql>grant all on mdb.* to user@localhost identified by ‘password’;

Answer: Query OK, 0 rows affected (0.06 sec)

I.e. I allowed access to the manually created database to the specified user. After that php connection to MySQL with user “user” and password “password” could be successfully created:

> $servername = "localhost";  
> $username = "user";  
> $password = "password"; 
> // Create connection 
> $conn = new mysqli($servername, $username, $password);
Elia12345
  • 326
  • 3
  • 5