CODEWITHSUNDEEP
phpmysql
Anthony
Anthony

Reputation: 4050

PHP MySQL connection failed

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:

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

Upvotes: 3

Views: 32351

Answers (7)

Marc B
Marc B

Reputation: 360872

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.

Upvotes: 1

Elia12345
Elia12345

Reputation: 326

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);

Upvotes: 0

mr-sk
mr-sk

Reputation: 13427

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.

Upvotes: 1

useless
useless

Reputation: 1906

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

Upvotes: 1

Anthony
Anthony

Reputation: 4050

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'; ?

Upvotes: 1

Andrew Moore
Andrew Moore

Reputation: 95454

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

Upvotes: 1

outis
outis

Reputation: 77450

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:

Upvotes: 7

Related Questions