Home / code / php

Database connection in PHP

PHP supports connections to many Databases including:

• MySQL
• PostgreSQL
• ODBC
• Many others

 

MySQL

Example: (the best way to learn it)

<?php
    /* Connecting, selecting database */
    $link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
        or die("Could not connect");
    print "Connected successfully";
    mysql_select_db("my_database") or die("Could not select database");

    /* Performing SQL query */
    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed");

    /* Printing results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        }
        print "\t</tr>\n";
    }
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);
?>

A bit of explanation:

$link = mysql_connect("mysql_host", "mysql_user", "mysql_password");
This is the connection to the SQL-SERVER.

mysql_select_db("my_database");
Here you select the DATABASE.

$query = "SELECT * FROM my_table";
$result = mysql_query($query);
Here you put the SQL-query in a variable ($query) and execute it.

$line = mysql_fetch_array($result, MYSQL_ASSOC)
Here you get the results. Usually put in a while-loop.

mysql_free_result($result);
Here you free the result.

mysql_close($link);
This is to close the connection.

Back to top

PostgreSQL

Example:

<?php
  //connecting to the database
  $conn = pg_connect("host=localhost dbname=databasename user=username password=password")
	or die("Could not connect");
  print "Connected successfully";

  /* Performing SQL query */
  $query = "SELECT * FROM table";
  $result = pg_exec ($conn, $query) or die("Query failed");

  /* Getting the rows */
  $rows = pg_numrows($result);

  /* Printing results in HTML */
  print "<table>\n";
  for($i=0;$i<$rows;$i++)
  {
	print "\t<tr>\n";
	$arr = pg_fetch_array ($result, $i, PGSQL_ASSOC);

	foreach ($arr as $col_value)
	{
	  print "\t\t<td>$col_value</td>\n";
	}
	print "\t</tr>\n";
  }
  print "</table>\n";

  /* Free resultset */
  pg_free_result($conn);

  /* Closing connection */
  pg_close($conn);
?>

A bit of explanation:

$conn = pg_connect("host=localhost dbname=databasename user=username password=password");
Makes the connection to the PostgreSQL server, and selects the database.

$query = "SELECT * FROM table";
$result = pg_exec ($conn, $query) or die("Query failed");
These 2 lines put make the SQL-query and execute it.

$rows = pg_numrows($result);
Here you check for the lines in the result. Why? Well there is/was a small bug that when there where no rows it gave a error. This is just to prevent it from happening.

$arr = pg_fetch_array ($result, $i, PGSQL_ASSOC);
Here you get the result of line i, in an array.

pg_free_result($conn);
Here you free the result.

pg_close($conn);
This is to close the connection.

There are many more PostgreSQL function, check out the official site.

Back to top

ODBC

Example:

//connect to the database
$dbconn=odbc_connect('databasename','username','password');

$query="SELECT id, name FROM table";
$result=odbc_exec($dbconn,$query) or die (odbc_errormsg()); 
while($row = odbc_fetch_array($result))
{
  $table.="<tr><td>". $row['id'] ."</td><td>". $row['name'] ."</td></tr>";
}

//close the connection
odbc_close($dbconn);

odbc_fetch_array() might not be available in every version of php.
If not add this function :

function odbc_fetch_array($result, $rownumber=-1)
{
  if(PHP_VERSION > "4.1")
  {
	if($rownumber < 0) odbc_fetch_into($result, $rs);
	else  odbc_fetch_into($result, $rs, $rownumber);
  }
  else odbc_fetch_into($result, $rownumber, $rs);
	
  $rs_assoc = Array();

  foreach ($rs as $key => $value) $rs_assoc[odbc_field_name($result, $key+1)] = $value;

  return $rs_assoc;
}

 

TOP