Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Access Functions
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

DSN and DSN-less connections

Print

DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone. If you do not know how to set up a system DSN read our tutorial How to set up a system DSN.

For our example below lets assume that our DSN points to an Access database called 'examples.mdb' and that we will be selecting records from the table 'cars'.

<?php

//connect to a DSN "myDSN"

$conn = odbc_connect('myDSN','','');

if ($conn)
{
  //the SQL statement that will query the database
  $query = "select * from cars";
  //perform the query
  $result=odbc_exec($conn, $query);

  echo "<table border=\"1\"><tr>";

  //print field name
  $colName = odbc_num_fields($result);
  for ($j=1; $j<= $colName; $j++)
  { 
    echo "<th>";
    echo odbc_field_name ($result, $j );
    echo "</th>";
  }

  //fetch tha data from the database
  while(odbc_fetch_row($result))
  {
    echo "<tr>";
    for($i=1;$i<=odbc_num_fields($result);$i++) 
    {
      echo "<td>";
      echo odbc_result($result,$i);
      echo "</td>";
    }
    echo "</tr>";
  }

  echo "</td> </tr>";
  echo "</table >";

  //close the connection
  odbc_close ($conn);
}
else echo "odbc not connected";
?>

Remember that 'myDSN' above is name of the DSN. Also note that you can change the table name from 'cars' to the name of your table and point the DSN to whatever database you like. One other thing to remember is that you can set up a DSN on your own machine though if you are using a hosting company you may have to ask the webmaster.

DSN-less connection

DSN-less connections don't require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. We will now see how to connect to a database via PHP using Connection String in place of DSN name.

<?php

//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection")
  or die("Cannot start ADO");

//define connection string, specify database driver
$connStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= c:\inetpub\wwwroot\db\examples.mdb";
  $conn->open($connStr); //Open the connection to the database

//declare the SQL statement that will query the database
$query = "SELECT * FROM cars";

//execute the SQL statement and return records
$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
echo $num_columns . "<br>"; 

for ($i=0; $i < $num_columns; $i++) {
    $fld[$i] = $rs->Fields($i);
}

echo "<table>";

while (!$rs->EOF)  //carry on looping through while there are records
{
    echo "<tr>";
    for ($i=0; $i < $num_columns; $i++) {
        echo "<td>" . $fld[$i]->value . "</td>";
    }
    echo "</tr>";
    $rs->MoveNext(); //move on to the next record
}


echo "</table>";

//close the connection and recordset objects freeing up resources 
$rs->Close();
$conn->Close();

$rs = null;
$conn = null;
?>




Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2014             www.WebCheatSheet.com All Rights Reserved.