[php] Connect to MySQL Database

[Westlife]

Opening a connection to MySQL database from PHP is easy. Just use the mysql_connect() function like this

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'petstore';
mysql_select_db($dbname);
?>

$dbhost is the name of MySQL server. When your webserver is on the same machine with the MySQL server you can use localhost or 127.0.0.1 as the value of $dbhost. The $dbuser and $dbpass are valid MySQL user name and password. For adding a user to MySQL visit this page : MySQL Tutorial

Don't forget to select a database using mysql_select_db() after connecting to mysql. If no database selected your query to select or update a table will not work.



Sometimes a web host will require you to specify the MySQL server name and port number. For example if the MySQL server name is db.php-mysql-tutorial.com and the port number is 3306 (the default port number for MySQL) then you you can modify the above code to :

<?php
$dbhost = 'db.php-mysql-tutorial.com:3306';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'petstore';
mysql_select_db($dbname);
?>

It's a common practice to place the routine of opening a database connection in a separate file. Then everytime you want to open a connection just include the file. Usually the host, user, password and database name are also separated in a configuration file.

An example of config.php that stores the connection configuration and opendb.php that opens the connection are :

Source code : config.phps , opendb.phps

<?php
// This is an example of config.php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'phpcake';
?>

<?php
// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
?>

So now you can open a connection to mysql like this :

<?php
include 'config.php';
include 'opendb.php';

// ... do something like insert or select, etc

?>

Closing the Connection

The connection opened in a script will be closed as soon as the execution of the script ends. But it's better if you close it explicitly by calling mysql_close() function. You could also put this function call in a file named closedb.php.

Source code : closedb.phps

<?php
// an example of closedb.php
// it does nothing but closing
// a mysql database connection

mysql_close($conn);
?>

Now that you have put the database configuration, opening and closing routines in separate files your PHP script that uses mysql would look something like this :

<?php
include 'config.php';
include 'opendb.php';

// ... do something like insert or select, etc

include 'closedb.php';
?>

This is a really easy tutorial for you al but if i helped just click the thanks button underneath

 

[Mac]

I don't like that way as it's not secure anyway i like doing it with classes like that(one way of this) :

class MySQL {
var $connect;
var $selectdb;
function connect() {
$this->connect = mysql_connect(host, user, pass);
return this->connect;
}
function selectdb() {
$this->selectdb = mysql_select_db(db);
return $this->selectdb;
}
}
$hello = new MySQL;
$hello->connect();
$hello->selectdb();

But if you are going to do it like that you should do it more safely :

<?php
$host = 'localhost';
$user = 'root';
$pass = 'bess123';
$conn = mysql_connect($host, $user, $pass);
$lol = "";
if($conn) {
$lol .= $conn;
}
else {
$lol .= "Cannot connect";
}
if(isset($lol)) {
echo $lol;
}
?>

 

[Kryptos]

I don't like that way as it's not secure anyway i like doing it with classes like that(one way of this) :

class MySQL {
var $connect;
var $selectdb;
function connect() {
$this->connect = mysql_connect(host, user, pass)
return this->connect;
}
function selectdb() {
$this->selectdb = mysql_select_db(db);
return $this->selectdb;
}
}
$hello = new MySQL;
$hello->connect();
$hello->selectdb();

But if you are going to do it like that you should do it more safely :

<?php
$host = 'localhost';
$user = 'root';
$pass = 'bess123';
$conn = mysql_connect($host, $user, $pass);
$lol = "";
if($conn) {
$lol .= $conn;
}
else {
$lol .= "Cannot connect";
}
if(isset($lol)) {
echo $lol;
}
?>

More secure? Obviously you know nothing about security, doing it with classes and functions doesnt make it more 'secure'.

Thanks for this @OP

 

[Mac]

I think it does because classes are not showable to the user , making free variables is not secure because variables are public. Anyway i say what i learnt everytime , i dont make things myself because it makes no sense.

 

[Kryptos]

PHP is not showed to the user, ever. Because it is server-side.
Just a lesson.

@WestLife, I expect to see more of you.

 

[Benson]

Mac, using classes don't do a difference. Everything done in PHP is serverside, and cannot be seen by the clientside user. Find out what your talking about, before you say it . Also, just to make it more organised, I would do something like this:

<?php
        $SQL = array(
           'user' => 'root',
           'pass' => 'password',
           'host' => 'localhost',
           'table' => 'table',
        );

        mysql_connect($SQL['host'], $SQL['user'], $SQL['pass']) or die('There was an error connecting to MySQL - ' . mysql_error());
        mysql_select_db($SQL['table']) or die('There was an error selecting the table "' . $SQL['table'] . '" - ' . mysql_error());
?>