Database Connection With PHP

[kvdhanush]

This tutorial will help you make connection to your database with PHP. It’s very simple process but I know how difficult can be for someone who is only starting to learn PHP. To test this example you should download and install Apache server, MySQL database server and PHP.

Creating config.php

If you want to use a database in your application, you have to make config.php file which will contain basic database data. Here we will declare database path, username, password, database name and create connection string. We’ll make local database connection for a start. Put the code below into config.php file and put it in the root folder of your project.

<?php
$host = "localhost"; //database location
$user = "bitis"; //database username
$pass = "kaka"; //database password
$db_name = "bitis"; //database name
//database connection
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db_name);
//sets encoding to utf8
mysql_query("SET NAMES utf8");
?>

First 4 lines are basic database data. 2 lines below is connection string which connects to server and then mysql_select_db selects database. The last line is optional but I like to include it to be sure the data will be in utf8 format. Now we have config.php file created and saved in the root folder of our project.

Include config.php in application

Don’t be distracted with me calling website an application. I call it because you can use this methods in any application. It doesn’t necessarily has to be a website.
To include config.php into application (lets say it’s a website) simply put next line on the top of the source code of index.php.
<?php include 'config.php'; ?>

Executing a query

To execute a query you’ll have to create table in your database and fill it with some data. Let’s say we have table named ‘Customers’ with next fields: Name, Address, Phone. What we want to do is simply to display all data stored in this table.
<?php
$sqlstr = mysql_query("SELECT * FROM customers");
if (mysql_numrows($sqlstr) != 0) {
while ($row = mysql_fetch_array($sqlstr)) {
?>
<p><?= $row['name'] ?></p>
<p><?= $row['address'] ?></p>
<p><?= $row['phone'] ?></p>
<?php
}
}
?>
What have we done here? First line defines sql string which will get our data. You can get a lot of different data by simply changing sql string. If statement in second line verifies if there are any results returned by sql str. This line is optional but I like to use it so it won’t come to unexpected errors. While loop in 3rd line loops through data record set we received with our sql query. If there is 10 records in the table, while loop would repeat 10 times and output the code between its tags. The code between while tags will be repeated and outputted.
That’s it.



Dhanush is a blogger.
My favourite Blog
callezee

[worlditsme]

Also, if you want to change data within such a database, you would use the following query (for example):

UPDATE customers SET address = newaddress WHERE name = customername

[sarmth]

Best to use "or die()" statements to show if there is any error during connection, or authentication to the database.
Validation and error reporting are powerful tools for mainstream programming. Use them often.

[Ben]

Dhanush is a blogger.
My favourite Blog
callezee

I agree

[rolandkeys]

Good code! I personally prefere to create a cofig file and in other page. So in the main php we just link the sheets.

[iBrightDev]

seriously, in my book, OOP is the only way to go, IMO.

<?php #database.incl.php

$ibd_db    =    new ibd_database(
                $config['database']['dbhost'], 
                $config['database']['dbname'], 
                $config['database']['dbuser'], 
                $config['database']['dbpass']
            );

class ibd_database {
    
    // Database Host
    private $db_host;
    // Username
    private $db_user;
    // Password
    private $db_pass;
    // Database
    private $db_name;
    
    function __construct ($dbHost, $dbName, $dbUser, $dbPass) {
        
        $this->db_host = $dbHost;
        $this->db_name = $dbName;
        $this->db_user = $dbUser;
        $this->db_pass = $dbPass;
        
    }
    
    // Checks to see if the connection is active
    private $con = false;
    
    
    private function ibd_connect() {
        
        if($this->con) return true;
            $this->myconn = new mysqli($this->db_host, $this->db_user, $this->db_pass, $this->db_name) or die('There was a problem connecting to the database');
        
        if(!$this->myconn) return false;
            $this->con = true;
        
        return $this->myconn;
        
    }
    
    /*
     * Disconnect from the database
     *
     */
    public function ibd_close() {
        
        if($this->con) {
            
            if(mysqli_close($this->myconn)) {
                $this->con = false;
                return true;
            } else {
                return false;
            }
            
        }

    }
    
    
    /*
    * Selects information from the database.
    * Required: ibd_column (the columns requested, separated by commas, or use *)
    *           ibd_table (the name of the table)
    * Optional: ibd_where (column = value as a string)
    *           ibd_group (value to group)
    *           ibd_order (column DIRECTION as a string)
    *           ibd_limit (number or comma seperated numbers as a string)
    */
    public function ibd_select($ibd_column, $ibd_table, $ibd_where=null, $ibd_group=null, $ibd_order=null, $ibd_limit=null) {
        
        $parameters = array();
        $results = array();
        
        $q = "SELECT " . $ibd_column . " FROM " . $ibd_table . "";
        
        if($ibd_where != null)
            $q .= " WHERE " . $ibd_where;
        
        if($ibd_group != null)
            $q .= " GROUP BY " . $ibd_group;

        if($ibd_order != null)
            $q .= " ORDER BY " . $ibd_order;

        if($ibd_limit != null)
            $q .= " LIMIT " . $ibd_limit;

        $mysql = $this->ibd_connect();
        $stmt = $mysql->prepare($q) or die('Problem preparing query');
        $stmt->execute();
        
        $meta = $stmt->result_metadata();
        
        while ( $field = $meta->fetch_field() ) {
            $parameters[] = &$row[$field->name];
        }
        
        call_user_func_array(array($stmt, 'bind_result'), $parameters);
        
        while ( $stmt->fetch() ) {
            $x = array();
            foreach( $row as $key => $val ) {
                $x[$key] = $val;
            }
            $results[] = $x;
        }
        
        $this->ibd_close();
        
        return $results;
        
    }
    
}

?>

obviously, you would want to have a insert function, delete function and an update function, but, i dont fell like putting that all here right now.

but, to utilize this object, it is super simple. example below...

$getGroupDetails = $dbFun->querySelect("*", "locations", "loc_id='{$_REQUEST['state']}'"); 

[Zombie]

@IBright

showoff

[iBrightDev]

not showing off, just showing a more advanced way to deal with databases.

hey, at least i didnt confuse you by throwing in stored procedures and queries for that stuff. lol