Jump to content
Larry Ullman's Book Forums

Newbie Question - Connecting To Remote Mysql Server


Recommended Posts

Hello everyone.

 

I am building my first php/mySQL website and I have a basic question.

 

I am hosting my website at Hostgator. I have a very basic first index.html loaded on the server at Hostgator and I can access it from my PC's browser.

 

I uploaded a simple php page with a simple 'echo' command and that works.

 

I have built a tiny database using phpAdmin and it has 4 records with 3 fields each and I can query it using phpAdmin.

 

Now I am trying to connect to it from a php web page. I used a modified version of script 6.4 in the book and when I run it, I get these errors:

 

Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'http' (25) in /home3/bluesmat/public_html/test2.php on line 21

Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'bluesmat'@'localhost' (using password: NO) in /home3/bluesmat/public_html/test2.php on line 22

Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /home3/bluesmat/public_html/test2.php on line 22

 

These errors look very similar to those in Figure 6.5.

 

I am not sure how to fix these errors.

 

I am using cPanel and I tried to look to see if indeed I did assign a password to this database (which I think I did) but I don't see where to check that.  Also, I am not sure what the host name should be since I'm using Hostgator's server.

 

Any help would be appreciated.

 

Link to comment
Share on other sites

You may have to check with Hostgator on this one, but your host name is likely 'localhost'.

 

For the user name and password, those should be ones you assigned yourself. From the cPanel, click on the MySQL Databases link towards the bottom, and you should be able to see what users are assigned to what DBs.

 

The user name is the string you should assign to the second argument of the mysqli_connect function. The DB name is the string you should assign to the fourth argument of the mysqli_connect function. The password is the password associated with the user, and that's something that I can't help you with.

 

Good luck.

Link to comment
Share on other sites

OK. I solved the problem; my username for the database was incomplete.

 

Now I am trying to do a simple query and display the 4 records from the small table I created.

 

I can query the table in phpAdmin so I know the records are there.

 

Here's my code (with the db name and password x'ed out):

 

<?php
  echo '2';
  echo '<br>';
  echo 'Trying to connect to query the mySQL server';
  echo '<br> <br>';
  
  define ('DB_USER', 'xxxxxxxxx');
  define ('DB_PASSWORD', 'xxxxxxx');
  define ('DB_HOST', 'localhost');
  define ('DB_NAME', 'bluesmat_test');
 
  $dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
  mysql_select_db (DB_NAME);
 
  $query = "SELECT * FROM `USstates`";
  $result = mysql_query ($query);
  
  if ($result) {
    echo 'It worked!!';
    } else {
    echo 'There was a problem!';
    }
?>
 
 
When I run this, 'There was a problem!' is echoed and no other warnings are displayed. I believe this means that the query did not execute since $result was not set to TRUE. I'm not sure what is wrong.
 
Any suggestions?
Link to comment
Share on other sites

Here's the updated code:

 

<?php
  echo '9';
  echo '<br>';
  echo 'Trying to connect to query the mySQL server';
  echo '<br> <br>';
  
  define ('DB_USER', 'xxxxxxxxxxxxxxxxxx');
  define ('DB_PASSWORD', 'xxxxxxxxxxxxxxxxxxxxx');
  define ('DB_HOST', 'localhost');
  // define ('DB_NAME', 'bluesmat_test');
 
  $dbhandle = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die("Unable to connect to MySQL");
  echo "Connected to MySQL<br>";
  $selected = mysql_select_db ("bluesmat_test", $dbhandle) or die("Could not select bluesmat_test");
 
  //my first query
  $result = mysql_query ("SELECT * FROM USstates");
  
  if ($result) {
    echo 'It worked!!';
/*     <p><?= $row['state_name'] ?></p>
      <p><?= $row['state_abbreviation'] ?></p>
      <p><?= $row['capital'] ?></p>;
    */
} else {
    echo 'There was a problem!';
    }
?>
 
When I run this, I get as far as "Connected to MySQL" then I get "Could not select bluesmat_test".  I'm not sure what is wrong.
Link to comment
Share on other sites

The MySQL functions are deprecated. Try switching to MySQLi (i = improved) instead.

$dbhandle = mysqli_connect('localhost', 'user****', 'password******', 'bluesmat_test');

if ( ! dbhandle ) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

I bet that is not really the problem, though. You'll find the db's name in PHPMyAdmin. Sure it's spelled correctly?

Link to comment
Share on other sites

OK, thanks for the replies.

 

I tried "mysqli_connect" and got this warning:

 

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home3/bluesmat/public_html/test2.php on line 22
Could not select bluesmat_test

 

I checked (again) in phpAdmin and unless my eyes are crossed, I have the database name spelled correctly.  Maybe it doesn't like the '_' (underscore) in the name?

Link to comment
Share on other sites

Yes, I would definitely use MySQLi instead, as Antonio suggested. Also, I'd simplify your logic to the following:

$dbc = mysqli_connect('localhost', 'xxxxxxxxxxxxxxxxxx', 'xxxxxxxxxxxxxxxxxxxxx', 'bluesmat_test');

$result = mysqli_query($dbc, "SELECT * FROM USstates;");
Link to comment
Share on other sites

Thanks for all the replies.  The script is now working.  Here's the updated code (with my password x'ed out). When I contacted my host (Hostgator), they removed the 'i' from the mySQL_connect function.

 

<?php
  echo "My version";
  echo "<br>";
  echo "Trying to connect to query the mySQL server";
  echo "<br> <br>";
 
  $dbhandle = mysql_connect ('localhost', 'bluesmat_matt', 'xxxxxxxxxxxxxxxx') or die("Unable to connect to MySQL");
  echo "Connected to MySQL<br>";
  echo "<br>";
  $selected = mysql_select_db ('bluesmat_test', $dbhandle) or die("Could not select bluesmat_test");
  echo "Database selected.";
  echo "<br>";
  
  /* my first query - show tables */
  $result = mysql_query('SHOW TABLES',$dbhandle) or die('cannot show tables');
  while($table = mysql_fetch_array($result)) {
    echo($table[0] . "<BR>");
}
?>
 
Here's the output (there are 2 tables in the database):
 
My version
Trying to connect to query the mySQL server

Connected to MySQL

Database selected.
UScities
USstates
Link to comment
Share on other sites

Yes HartleySan, I see what you are asking.  

 

I went around and around with lots of different versions of my code trying to get it to work.  At one point, when I was doing some research online to solve my problem, someone suggested using mySQLi_connect instead of mySQL_connect (apparently mySQL_connect has been deprecated).

 

When I contacted Hostgator and showed them my code, I was, at that point, using mySQLi_connect (and my code didn't work).  The working code they returned had the 'i' removed. It doesn't look like they changed anything else in my code, but I'm not sure if they also changed some kind of setting on my hosting account to get my code to run (maybe in php.ini?). Still learning about all of this.

Link to comment
Share on other sites

You can't just change the connect function. You must switch completly to MySQLi or simply use MySQL.

 

mysql_connect -> mysqli_connect

mysql_query -> mysqli_query

mysql_fetch_array -> mysqli_fetch_array

 

... And so on. Check the documentation as Jon pointed out.

Link to comment
Share on other sites

 Share

×
×
  • Create New...