Could not connect?

PHP
1

It s quite some time ago that I last used PHP. I try to pick it up again but am already struggling :(. I made a test page in order to test the connection to the database e.a.:


<?php
$dbhost = 'cantonamsterdam.nl';
$dbuser = '************';
$dbpass = '-*************/o';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db('**********', $conn );

if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$intPage = 2;
$sql = 'SELECT page_name FROM pages WHERE page_id = $intPage';
$result = mysql_query( $sql, $conn );

if(! $result )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "{$row['page_name']}  <br> ";
} 
?>

but I get the following error:

Could not connect: Lost connection to MySQL server at 'reading initial communication packet', system error: 111

The database is there, the user and password are correct and the table pages exists incl data. What am I overseeing?

Thank you in advance!

2

Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.

3

Hi SpacePhoenix. Thanks for the reply :tup: Please excuse my Ignorance but this is complete new to me! Some weeks ago though I got some help from your colleague Pullo with a gallery where he indeed used PDO e.a.:


 <?php
try
{
  $pdo = new PDO('mysql:host=localhost; dbname=DIEMDB', 'diemdb',  '$diem_db$');
}
catch (PDOException $e)
{
  echo 'Unable to connect to the database server.';
  exit();
}

$museum_id= $_GET['museum_id']; 
$imgString= "";
 
$sql= "SELECT photo_id, photo, link FROM museum_photos WHERE museum_id = :museum_id ORDER BY photo_id DESC";  
$stmt = $pdo->prepare($sql); 
$stmt->bindParam(':museum_id', $museum_id, PDO::PARAM_STR);  
$stmt->execute(); 

    while ($row = $stmt->fetchObject()) {
        if ($row->photo_id == 76){
            $imgString .= "<a class='fancybox-media' href='$row->link" . "'><img src='http://diem.sothenwhat.com/museum_photos/carousel/" . $row->photo . "' /></a>";
        } else {
            $imgString .= "<a class='fancybox' data-fancybox-group='gallery' href='$row->link" . $row->photo . "'><img src='http://diem.sothenwhat.com/museum_photos/carousel/" . $row->photo . "' /></a>";
        }
    }
echo $imgString;
?>

I just tried to combine his example along with the information from the website you just sent me to come to a test page:


<?php
try
{
  $pdo = new PDO('mysql:host=cantonamsterdam.nl; dbname=*******', '***********',  '-************/o');
}
catch (PDOException $e)
{
  echo 'Unable to connect to the database server.';
  exit();
}

$intPage = 2;

$sql= "SELECT page_name FROM pages WHERE page_id = $intPage'"; 
$stmt = $pdo->prepare($sql);
$stmt->execute();

while ($row = $stmt->fetchObject()) {
    echo "{$row['page_name']}  <br> ";    
}

?>

But now I get the message as set in the try/catch:

Unable to connect to the database server.

Like I said in my original message. Everything is in place. Or is way I declared it not right?

Again thank you!

4

I use an include like this for DB connection:

<?php
$db_host = "hostname";
$db_username = "username";
$db_pass = "password";
$db_name = "dbname";
mysql_connect("$db_host","$db_username","$db_pass") or die(mysql_error());
mysql_select_db("$db_name") or die("no database by that name");
?>

This works.
Also, you may not want to publish your DB details such as user names, passwords and sucklike on a public website.

5

Hi Sam. I tried to follow SpacePhoenix’s suggestions to use PDO instead of mysql_connect etc. Apparently that is a better choice! As he wrote:

PDO is a better choice as it doesn’t tie you down so much to a particular database server software.
But like I said it isn’t working fom me while the database is there etc.

Also, you may not want to publish your DB details such as user names, passwords and sucklike on a public website.

This is just for testing.The database etc is just temporary! But thank you anyway!

6

Hey donboe,

The exception that you’re catching will contain more specific information about the problem. Try changing the catch block to this:


catch (PDOException $e)
{
  echo $e->getMessage();
  exit();
}
7

Hi Sam. I tried to follow SpacePhoenix’s suggestions to use PDO instead of mysql_connect etc. Apparently that is a better choice! As he wrote:

Yes, that is something I will have to look into too. I just learn to use PHP and SQL and it all changes.

This is just for testing.The database etc is just temporary! But thank you anyway!

I thought that may be the case, but you never know, it’s easy to forget to edit private code when cutting and pasting.

8

Hi fretburner.And this is the new error I get:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'cantonamsterdam.nl' (111)

It is not clear to me but probably to you it is.

Thank you in advance

9

Are you running this on your local machine (e.g. with WAMP or XAMPP) or is this on a web host? If it’s on web hosting, are you sure the database hostname is correct?

10

It’s on a webhost (Unfortunately do I have problems installing XAMPP on my local machine because of mySql already running because of another server side scripting language). Yes it is the right host. Should I try an IP instead?

11

If you have an IP address for the database server then you could try that. With some hosts, the DB is on the same machine and you have to connect using ‘localhost’, others require an IP rather than a host name… it really depends on how your hosting is configured.

12

Normally I have clients on my own server, but this one insisted that he would have it hosted in a package he already had! So I have to figure out with the hosting company what to use. Thanks for all your input!

13

Hi fretburner. I finally got an answer from clients hosting company. They said I should indeed us the IP address, which I added! But now I get the following error:

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I have been googling but can’t find what the reason could be

14

At this point, it looks like it’s a server configuration issue rather than anything to do with your PHP code. I’d recommend getting back in touch with the hosting company. It depends on the kind of hosting, but they might not allow clients access to play around with mysql server sockets anyway.

15

Hi fretburner. Just get in touch with the hosting company again.They made a test page and it is working! This the code they used:


<?php

$mysqli = new mysqli("mysql828.pem.kpn.net", "***********", "-********/o", "********");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\
", mysqli_connect_error());
    exit();
}

$query = "SELECT * FROM pages WHERE page_id = 2";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ($row["page_name"]);
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();
?>

As far as I can see is it nearly the same except that they used new mysqli instead of new PDO, as was suggested in an earlier answer in this post! Should I stick to this script or should I try to adapt to the PDO method?

Thank you in advance

16

OK, so they’ve given you a different hostname than the ones you tried previously, so I’d try your PDO code again with this new hostname and I suspect I’ll work.

17

Hi. That’s the problem I tried it with the PDO

$pdo =  new PDO("mysql828.pem.kpn.net", "********", "-*************/o", "*************");

and for the rest the same as before.

18

You can’t just copy and paste all the arguments for the mysqli class to the PDO one… PDO expects the data to be passed like this:

$pdo = new PDO('mysql:host=mysql828.pem.kpn.net;dbname=**************', '**************',  '-************/o');
19