Programming Articles

Connecting to MySQL in PHP with a DSN

This script demonstrates connecting to a MySQL database in PHP with a DSN. The script will make the connection and then output all the rows of a specified table. Any lines beginning with a // are comments.

$dsnname = "testdsn";
Sets a variable to hold the name of the DSN to connect to. You should change this to your DSN name.

$username = "testuser";
$password = "testpass";
These are the username/password to use for the DSN. You should change these to your username and password.

$table = "testtable";
Sets a variable to hold the name of the table to be displayed by the script. This should be changed to your table name.

$connection = odbc_connect( $dsnname , $username, $password );
Creates the connection to the database with the specified parameters.

if (!$connection) {
Checks if the connection was successful, if it wasn't then an error will print and the program will exit.

$cur = odbc_exec( $connection, "select * from $table" );
Executes the query specified in the second parameter on the database connection in the first parameter.

if (!$cur) {
Checks if the query was successful. If it wasn't, an error will be printed and the program will exit.

print "<table border=1><TR>";
The results of the database query will be printed in an HTML table.

for($loop = 0; $loop < odbc_num_fields($cur); $loop++){
     $name = odbc_field_name($cur, $loop); - Gets the field name, the first parameter is the result of a query and the second parameter is the number of the field. Numbering starts at 0.
     print "<TH>$name</TH>";
}
This loop will go through each of the fields in the table and print out the field name as the first row of the HTML table.

print "</TR>";
Ends the row in the HTML table.

while( odbc_fetch_row( $cur ) ) {
This will loop through all of the remaining rows in the result set and put the row in the variable $cur.

print "<TR>";
Starts a new row in the HTML table.

for($loop = 0; $loop < odbc_num_fields($cur); $loop++){
     $fieldinfo = odbc_result($cur, $loop); - This retrieves the value of the field at the number specified in the second parameter.
     print "<TH>$fieldinfo</TH>";
}
This loop will go through each of the fields in the table and print out the value in the HTML table.

print "</TR>";
Ends the row in the HTML table.

print "</TABLE>";
Ends the HTML table.

odbc_close( $connection);
Closes the connection to the database.

--Here is the full script, the filename should be: mysql-dsn.php--

<html>
<head>
<title>Connecting to a MySQL database with PHP with a DSN</title>
</head>
<body>

<?php

//Easy CGI Test script for connecting to a MySQL database in PHP with a DSN
//This script will connect to the database and output the results in an HTML table

// Change these values to point to your database
// --------------------------------------------
// this is the name of the DSN
$dsnname = "testdsn";
// this is the username/password for the DSN
$username = "testuser";
$password = "testpassword";
// this is the name of the table to display
$table = "testtable";
// --------------------------------------------

//create the connection
$connection = odbc_connect( $dsnname , $username, $password );
if (!$connection) {
     print "Error in odbc_connect. Could not connect to DSN: $dsnname!";
     exit;
}

// send a simple odbc query . returns an odbc cursor
$cur= odbc_exec( $connection, "select * from $table" );
if (!$cur) {
     print "Error in odbc_exec(). Could not execute query!";
     exit;
}

//print the column names
print "<table border=1><TR>";
for($loop = 0; $loop < odbc_num_fields($cur); $loop++){
     $name = odbc_field_name($cur, $loop);
     print "<TH>$name</TH>";
}
print "</TR>";

// fetch the succesive result rows
while( odbc_fetch_row( $cur ) ) {

     //print each field in the table
     print "<TR>";
     for($loop = 0; $loop < odbc_num_fields($cur); $loop++){
          $fieldinfo = odbc_result($cur, $loop);
          print "<TH>$fieldinfo</TH>";
     }
     print "</TR>";

}
print "</TABLE>";

// close the connection. important if persistent connections are "On"
odbc_close( $connection);

?>

</body>
</html>

--End Script--
1/5/2008 2:02:55 AM Category Database Connection Strings Comments 0

Back