PHP database connectivity!!

slashfear · 2011-03-02T07:26:11+00:00

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

PHP database connectivity!!

slashfear

Member

Updated: Oct 26, 2024

Views: 1.1K

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

0

Replies

Howdy guest!

Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.

Replies

PraveenKumar Purushothaman

Member • Mar 2, 2011

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);

Are you sure? This action cannot be undone.
Cancel
Kaustubh Katdare

Administrator • Mar 2, 2011

Why don't we have many threads like this? 😀 Awesome job, people 👍

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 2, 2011

The_Big_K
Why don't we have many threads like this? 😀 Awesome job, people 👍
Wooah!!! Thanx... 😀 Am starting many like this then... jQuery!!! 😁

Are you sure? This action cannot be undone.
Cancel
slashfear

Member • Mar 3, 2011

The_Big_K
Why don't we have many threads like this? 😀 Awesome job, people 👍

Thanks Biggie 😁 More tutorials to come!!

-Arvind

Are you sure? This action cannot be undone.
Cancel
slashfear

Member • Mar 3, 2011

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.....

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 3, 2011

slashfear
Good work buddy!!! thanks for helping.....
Anytime dude... 😀 Check out my articles too... 😁

Are you sure? This action cannot be undone.
Cancel
Manish Goyal

Member • Mar 3, 2011

praveenscience
Anytime dude... 😀 Check out my articles too... 😁
It would be great if you can write some article on oops in php

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 3, 2011

goyal420
It would be great if you can write some article on oops in php
I am starting now! 😀

Are you sure? This action cannot be undone.
Cancel
Ankita Katdare

Administrator • Mar 3, 2011

Wow! Great contribution! 😀

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 4, 2011

goyal420
It would be great if you can write some article on oops in php
Posted... 😀

Are you sure? This action cannot be undone.
Cancel
slashfear

Member • Mar 4, 2011

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:

<?php
$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:

<?php
$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:

<?php

$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:

<?php

$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:

<?php

$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:

<?php

$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

Are you sure? This action cannot be undone.
Cancel
slashfear

Member • Mar 4, 2011

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

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 4, 2011

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... 😔

Are you sure? This action cannot be undone.
Cancel
slashfear

Member • Mar 5, 2011

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

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 5, 2011

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? 😔

Are you sure? This action cannot be undone.
Cancel
ambar_des

Member • Mar 5, 2011

thanks for sharing.. it is very helpful!

Are you sure? This action cannot be undone.
Cancel
slashfear

Member • Mar 7, 2011

ambar_des
thanks for sharing.. it is very helpful!

No Prob!! Ambar.... 😉 Thanks for the feedback!!

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 7, 2011

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-#.

Are you sure? This action cannot be undone.
Cancel