PHP database connectivity!!

Hi Ceans,

As everyone knows PHP is web scripting language in simple terms its the language used for creating webpage or website. And its really important to know how to use PHP to connect to databases as all the website which are in existence right now use database as back-end to store information in a secure way as well as organized way. So in this tutorial I am gonna show you how to establish connection to a database using PHP. Lets get it started....

First lets see how to establish a connection to a database and then we will see how to execute simple query in order to show the fetched result set to users through webpage.

In order to establish a connection to MYSQL there is a predefined function called mysql_connect which takes three parameters, the first parameter is the host name ( the machine IP address where the mysql is residing ) and the second parameter is the username (mysql username) and the third parameter is the password (mysql password for that user name you have specified as second parameter) each parameter is separated from one another using a , (comma). So the connection string will be as shown below:


mysql_connect("host", "username", "password");


NOTE: If the database your trying to access is available in the same machine you can specify localhost as host name (That's the first parameter)

Ok now lets pass some real parameters to this connection string,

mysql_connect("localhost", "slashfear", "arvind");

Ok your done!! the above connection string will connect to local mysql server with the user name slashfear and password arvind, that's how you can connect to database using PHP simple right!!


For other databases like oracle, postgresql, sybase, db2 the predefined function as well the parameters will change and are listed below for your reference:

Oracle database connection:

oci_connect("username", "password", "host");

Postgresql database connection:

pg_connect("host=hostname port=5432 dbname=demo user=slashfear password=arvind");

Sybase database connection:

sybase_connect('host', 'username', 'passoword');

DB2 database connection:

db2_connect("DATABASE=demo;HOSTNAME=host;", "username", "password");



Now we know how to connect to database, its important to know how to disconnect from it too so in-order to disconnect from a mysql database we have to call the predefined function mysql_close which will take one parameter which is the reference to the connection which your willing to close so lets look at a an example for clear understanding:

$dbcon = mysql_connect("localhost", "slashfear", "arvind");

mysql_close($dbcon);


So in the above snippet the variable $dbcon reference to the connection to mysql which connects to the local mysql server with the user name slashfear and identified by password arvind. So in order to disconnect from the database we call the function called mysql_close and pass the variable name which holds the reference to the connection string.

Following is the list of predefined functions to perform disconnection from other database:

Oracle database disconnection:

$dbcon = oci_connect("username", "password", "host");
oci_close($dbcon);


Postgresql database disconnection:

$dbcon = pg_connect("host=hostname port=5432 dbname=demo user=slashfear password=arvind");
pg_close($dbconn);

Sybase database disconnection:

$dbcon = sybase_connect('host', 'username', 'passoword');
sybase_close($dbconn);

DB2 database disconnection:

$dbcon = db2_connect("DATABASE=demo;HOSTNAME=host;", "username", "password");
db2_close($dbconn);


Continuation of this tutorial is on PAGE 2 😀

If you have any doubts please feel free to ask!!


-Arvind

Replies

  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    Awesome post man! Let Me Add MySQLi and SQLite... 😀

    MySQLi with PHP
    Open Connection (mysqli_connect)
    Open a new connection to the MySQL server...
    $mysqli = new mysqli('localhost''my_user''my_password''my_db');
    Open Persistent Connection
    Prepending host by p: opens a persistent connection.

    Close Connection (mysqli_close)
    Closes a previously opened database connection...
    $link mysqli_connect('localhost''my_user''my_password''my_db');
    mysqli_close $link );
    SQLite with PHP
    Open Connection (sqlite_open)
    Opens an SQLite database and create the database if it does not exist...
    $link sqlite_open('mysqlitedb'0666$sqliteerror);
    Open Persistent Connection (sqlite_popen)
    Opens a persistent handle to an SQLite database and create the database if it does not exist...
    $link sqlite_popen('mysqlitedb'0666$sqliteerror);
    Close Connection (sqlite_close)
    Closes an open SQLite database...
    $link sqlite_open('mysqlitedb');
    sqlite_close($link);
  • Kaustubh Katdare
    Kaustubh Katdare
    Why don't we have many threads like this? 😀 Awesome job, people 👍
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    The_Big_K
    Why don't we have many threads like this? 😀 Awesome job, people 👍
    Wooah!!! Thanx... 😀 Am starting many like this then... jQuery!!! 😁
  • slashfear
    slashfear
    The_Big_K
    Why don't we have many threads like this? 😀 Awesome job, people 👍

    Thanks Biggie 😁 More tutorials to come!!

    -Arvind
  • slashfear
    slashfear
    praveenscience
    Awesome post man! Let Me Add MySQLi and SQLite... 😀

    MySQLi with PHP
    Open Connection (mysqli_connect)
    Open a new connection to the MySQL server...
    $mysqli = new mysqli('localhost''my_user''my_password''my_db');
    Open Persistent Connection
    Prepending host by p: opens a persistent connection.

    Close Connection (mysqli_close)
    Closes a previously opened database connection...
    $link mysqli_connect('localhost''my_user''my_password''my_db');
    mysqli_close $link );
    SQLite with PHP
    Open Connection (sqlite_open)
    Opens an SQLite database and create the database if it does not exist...
    $link sqlite_open('mysqlitedb'0666$sqliteerror);
    Open Persistent Connection (sqlite_popen)
    Opens a persistent handle to an SQLite database and create the database if it does not exist...
    $link sqlite_popen('mysqlitedb'0666$sqliteerror);
    Close Connection (sqlite_close)
    Closes an open SQLite database...
    $link sqlite_open('mysqlitedb');
    sqlite_close($link);

    Good work buddy!!! thanks for helping.....
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    slashfear
    Good work buddy!!! thanks for helping.....
    Anytime dude... 😀 Check out my articles too... 😁
  • Manish Goyal
    Manish Goyal
    praveenscience
    Anytime dude... 😀 Check out my articles too... 😁
    It would be great if you can write some article on oops in php
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    goyal420
    It would be great if you can write some article on oops in php
    I am starting now! 😀
  • Ankita Katdare
    Ankita Katdare
    Wow! Great contribution! 😀
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    goyal420
    It would be great if you can write some article on oops in php
    Posted... 😀
  • slashfear
    slashfear
    Ok in my previous post we saw how to establish a connection and now lets execute some simple query from php to database, In order to execute a SQL statement from php to database we have predefined functions as well so after connection has been made, we have to call the mysql_query method to execute sql statement in mysql database from php. This predefined function takes 1 parameter, guess what its easy the SQL statement. Take a look at the below example for clear understanding:

    $myresult = mysql_query("select * from demo");

    The above line of code will execute in mysql and select all the information from the table named demo. But before we go ahead and put the above query in the php script we have to do another task in mysql or else we wont get the desired output because in mysql there are multiple sub databases so we have to choose the database first and tell mysql in which sub database the table called demo exists before we can go ahead and execute a query in mysql. So in order to choose the database we have to call the predefined function called mysql_select_db which takes 1 parameter which is the name of the sub database. So the final code should look like this:


    $dbcon = mysql_connect("localhost", "slashfear", "arvind");
    mysql_select_db = "test";
    $myresult = mysql_query("select * from demo");

    mysql_close($dbcon);
    ?>


    If you probably execute the above code you surely wont get any error as well as result set, Confused!! Let me explain why... We have just parsed the query "select * from demo" from php to mysql but we did not take any step to fetch the result set from the mysql database to PHP and that's the reason why we did not see any output so lets fetch the result set from mysql and display it in php.

    In order to do so, we are going to use the predefined function mysql_fetch_array, which takes one parameter which is the referece to the query embedded in the mysql_query method. Lets see an example code:

    $dbcon = mysql_connect("localhost", "slashfear", "arvind");
    mysql_select_db = "test";
    $myresult = mysql_query("select * from demo");

    while($row = mysql_fetch_array($myresult))
    {
    print $row['id'] . "
    ";
    }

    mysql_close($dbcon);

    ?>



    Ok Done so the mysql_fetch_array will fetch the result in form of array, So here $row is the array which hold the result set and the reason why I have used a while loop is because the mysql_fetch_array has been designed to work fetch only a single row at a time (That row can contain any number of column) so to fetch all the row from the table demo I am using a while loop which will run until mysql_fetch_array returns false.

    And you would have noticed when I am print out the data in $row i am actually using ['id'] what is that ?? Its nothing but the column name as the mysql_fetch_array return the results in form of an associative way by specifying the column name or by using index value. so in this case my table called demo has only one column named id so I specified that if in case it has more column just specify the column names for instance lets say my demo table has 3 column id, name and emailid so my print statement after the mysql_fetch_array will be as shown below:

    print $row['id'] . "  " . $row['name'] . "  " . $row['emailid'] . "
    ";



    Done!! with php and mysql way of executing a simple query, How about other databases?? The logic remains the same but the methods vary as I told you before so let me show you the code snippets for other databases 😉.


    Oracle Database:


    $dbcon = oci_connect("slashfear", "arvind", "localhost/XE");

    $myresult = oci_parse("select * from demo");
    oci_execute($sql);
    while($row = oci_fetch_assoc($sql))
    {
    print $row['id'] . "
    ";
    }

    oci_close($dbcon);
    ?>



    Postgresql Database:


    $dbcon = pg_connect("host=hostname port=5432 dbname=demo user=slashfear password=arvind");

    $myresult = pg_query($dbcon, "select * from demo");

    while($row = pg_fetch_array($myresult, 0, PGSQL_NUM);
    {
    print $row[0] . "
    ";
    }

    pg_close($dbconn);
    ?>



    Sybase Database:


    $dbcon = sybase_connect('localhost', 'slashfear', 'arvind');
    sybase_select_db("test");
    $myresult = sybase_query("select * from demo");

    while($row = sybase_fetch_array($myresult))
    {
    echo $row['id'] . "
    ";
    }

    sybase_close($dbconn);

    ?>


    DB2 Database:


    $dbcon = db2_connect("DATABASE=demo;HOSTNAME=host;", "username", "password");

    $result = db2_prepare($dbcon, "select * from demo");
    $myresult = db_execute($result);

    while($row = db2_fetch_array($result))
    {
    echo $row[0] . "
    ";
    }

    db2_close($dbconn);
    ?>


    Done!! Hope you guys learn t something new out of my writing...... Please do comment I would love to hear your feedback's!! 😉 More tutorials to come!!

    If you have any doubts please feel free to ask 😁

    -Arvind
  • slashfear
    slashfear
    praveenscience
    Posted... 😀
    Hey buddy where have you posted it!! I couldn't find it..... can you please post in the link too so it would be useful for others to navigate easily... 😀


    -Arvind
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    slashfear
    Hey buddy where have you posted it!! I couldn't find it..... can you please post in the link too so it would be useful for others to navigate easily... 😀


    -Arvind
    Dude, yep right! Even I am not able to find it... Wait... Searching... 😔
  • slashfear
    slashfear
    praveenscience
    Dude, yep right! Even I am not able to find it... Wait... Searching... 😔
    No Prob pal!! I have have taken care of it 😉

    Here is the link to object oriented PHP : #-Link-Snipped-#


    -Arvind
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    slashfear
    No Prob pal!! I have have taken care of it 😉

    Here is the link to object oriented PHP : #-Link-Snipped-#

    -Arvind
    Hey thanx, but what happened to mine? 😔
  • ambar_des
    ambar_des
    thanks for sharing.. it is very helpful!
  • slashfear
    slashfear
    ambar_des
    thanks for sharing.. it is very helpful!

    No Prob!! Ambar.... 😉 Thanks for the feedback!!
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    SQL Injection
    Another main thing is to avoid SQL Injection... Use,
    mysql_real_escape_string();
    addslashes();

    For those who are SQL Challenged, detailed stuff on SQL Injection is #-Link-Snipped-#.

You are reading an archived discussion.

Related Posts

Disclaimer: Before performing any of these, please note, all these are possible and worked under testing conditions. Neither me, nor CE is responsible for the damage caused to your system...
Day of the Week: January has 31 days. It means that every date in February will be 3 days later than the same date in January (28 is 4 weeks...
1. This person doesn't know your name and he never does. Why not? 2. A guy picked a book off the highest shelf in a library. On the spine, he...
Hi friends.... Pls help me out to find the cutting force calculations for a drilling machine and the calculations for design of lead screw......
Steve Jobs took a break from his ongoing medical leave to unveil the second generation iPad, called iPad 2. Here's a video of iPad 2- [video=youtube;b661ovU1rPU]https://www.youtube.com/watch?v=b661ovU1rPU&feature=player_embedded[/video] Apple's got some nice...