Programming Articles

Connecting to MySQL in PHP without a DSN

This script demonstrates connecting to a MySQL database in PHP without 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.

$hostname = "mysql1.easycgi.com";
Sets a variable to hold the address of the MySQL server.

$database = "testdatabase";
Sets a variable to hold the name of the database. You should change this to the name of your MySQL database.

$tablename = "testtable";
Sets a variable to hold the name of the table to display. You should change this to one of the tables in your database.

$userid = "testuser";
$password = "testpass";
These hold the username/password for your MySQL database.

$dbc = mysql_connect($hostname, $userid, $password) or die("Connection error: " . mysql_error() );
This opens a connection to the database. If there is an error, it is printed and the program exits.

mysql_select_db($database, $dbc) or die("Select db error: " . mysql_error() );
We select the database to use on the MySQL server. This is done so that we do not have to specify the database name in the other commands.

$rs = mysql_query("SELECT * FROM $tablename", $dbc) or die("Invalid query: " . mysql_error() );
Execute a query on the database and store the results in $rs.

print "<TABLE BORDER=1>";
Starts an HTML table.

if( mysql_num_rows($rs) > 0 ){
If there was a result.

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

$numfields = mysql_num_fields($rs);
Gets the number of fields in the result set.

for($loop = 0; $loop < $numfields; $loop++){
     //print the name of each field
     $fld = mysql_field_name($rs, $loop);
     print "<TH>$fld</TH>";
}
Loops through each of the fields and print out the field name as the first row in the HTML table.

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

while( $row = mysql_fetch_row($rs) ) {
Goes though each row in the result set.

$numfields = mysql_num_fields($rs);
Gets the number of fields in the result.

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

for($loop = 0; $loop < $numfields; $loop++){
     //print each field's value
     $fld = $row[$loop];
     print "<TH>$fld</TH>";
}
Goes through all of the fields in the table and print them in the HTML table.

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

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

mysql_close($dbc);
Closes the connection to the database.

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

<html>
<head>
<title>Sample script for accessing a MySQL database in PHP without a DSN</title>
</head>
<body>

<?php

//Easy CGI Test script for connecting to a MySQL database in PHP without 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
// --------------------------------------------
// the host name for the mysql server
$hostname = "mysql1.easycgi.com";
// the database name
$database = "testdatabase";
// the name of the table to display
$tablename = "testtable";
// username and password for the database
$userid = "testuser";
$password = "testpass";
// ----------------------------------------------

//connect to database
$dbc = mysql_connect($hostname, $userid, $password) or die("Connection error: " . mysql_error() );

//select the database
mysql_select_db($database, $dbc) or die("Select db error: " . mysql_error() );

//execute a query
$rs = mysql_query("SELECT * FROM $tablename", $dbc) or die("Invalid query: " . mysql_error() );

//print the results to a table
print "<TABLE BORDER=1>";

//print the column names
if( mysql_num_rows($rs) > 0 ){
     print "<TR>";
     $numfields = mysql_num_fields($rs);

     for($loop = 0; $loop < $numfields; $loop++){
          //print the name of each field
          $fld = mysql_field_name($rs, $loop);
          print "<TH>$fld</TH>";
     }
     print "</TR>";
}

//while not the end of the result set
while( $row = mysql_fetch_row($rs) ) {
     $numfields = mysql_num_fields($rs);

     print "<TR>";
     for($loop = 0; $loop < $numfields; $loop++){
          //print each field's value
          $fld = $row[$loop];
          print "<TH>$fld</TH>";
     }
     print "</TR>";
}

print "</TABLE>";

//dont forget to close connection
mysql_close($dbc);

?>
</body>
</html>

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

Back