PHP – How To’s

How to make a database connection to MS SQL in PHP

You will first need to create directory called includes and then create a file named data.php. Put the following code in this file and save. This allows you to define your database information in one place and use it in many files (making password changes easier). This file is also a great candidate for your DATA directory.

<?php
    $dsn = "{Enter MS SQL DSN name here} ";
    $username = "{Enter database username here} ";
    $password = "{Enter database password here} ";
?>

Now create another page outside the includes directory and put the following code in that file and save.

<html>
    <head>
        <title>Example PHP MS SQL Connection</title>
    </head>
    <body>
        <p align="center"><b>Example PHP MS SQL Connection</b></p>
        <p>
        <?php
            include 'includes/data.php';
            $con = odbc_connect($dsn, $username, $password)
                or DIE ("DATABASE FAILED TO RESPOND.");
            $query = "select * from table_name";
            $stmt = odbc_prepare($con, $query);
            if (!$stmt) {
                echo "prepare failed<br />\n";
            }
            else {
                if (!odbc_execute($stmt)) {
                    echo "execute failed<br />\n";
                }
                else {
                    while ($row = odbc_fetch_row($stmt)) {
                        echo $row[0] . "<br />\n";
                    }
                }
                odbc_free_result($stmt);
            }
            odbc_close($con);
        ?>
    </body>
</html>

How to make a database connection to MySQL in PHP

Note: You can also use the MS SQL code if you first have Web Services define an ODBC DSN for your MySQL database.

You will first need to create directory called includes and then create a file named data.php. Put the following code in this file and save. This allows you to define your database information in one place and use it in many files (making password changes easier). This file is also a great candidate for your DATA directory.

<?php
    $hostname = "{Enter MySQL server name here} ";
    $dbname = "{Enter database name here} ";
    $username = "{Enter database username here} ";
    $password = "{Enter database password here} ";
?>

Now create another page outside the includes directory and put the following code in that file and save.

<html>
    <head>
        <title>Example PHP MySQL Connection</title>
    </head>
    <body>
        <p align="center"><b>Example PHP MySQL Connection</b></p>
        <p>
        <?php 
            include 'includes/data.php';
            $con = mysqli_connect($hostname, $username, $password, $dbname) 
                or DIE ("DATABASE FAILED TO RESPOND."); 
            $query = "SELECT * from table_name";
            $stmt = mysqli_prepare($con, $query);
            if (!$stmt) {
                echo "prepare failed<br />\n";
            }
            else {
                if (!mysqli_stmt_execute($stmt)) {
                    echo "execute failed<br />\n";
                }
                else {
                    while ($row = mysqli_fetch_row($stmt)) {
                        echo $row[0] . "<br />\n";
                    }
                }
                mysqli_stmt_free_result($stmt);
            }
            mysqli_close($con);
        ?>
    </body>
</html>

How to make a database connection to Oracle in PHP

Note: You can also use the MS SQL code if you first have Web Services define an ODBC DSN for your Oracle database.

You will first need to create directory called includes and then create a file named data.php. Put the following code in this file and save. This allows you to define your database information in one place and use it in many files (making password changes easier). This file is also a great candidate for your DATA directory.

<?php
    $sid = "{Enter Oracle SID here} ";
    $username = "{Enter database username here} ";
    $password = "{Enter database password here} ";
?>

Now create another page outside the includes directory and put the following code in that file and save.

<html>
    <head>
        <title>Example PHP Oracle Connection</title>
    </head>
    <body>
        <p align="center"><b>Example PHP Oracle Connection</b></p>
        <p>
        <?php
            include 'includes/data.php';
            //echo $_ENV['ORACLE_HOME'];
            $con = oci_connect($username, $password, $sid)
                or DIE ("DATABASE FAILED TO RESPOND.");
            $query = "select * from table_name";
            $stmt = oci_parse($con, $query);
            if (!$stmt) {
                echo "parse failed<br />\n";
            }
            else {
                if (!oci_execute($stmt)) {
                    echo "execute failed<br />\n";
                }
                else {
                    while ($row = oci_fetch_row($stmt)) {
                        echo $row[0] . "<br />\n";
                    } 
                } 
                oci_free_statement($stmt);
            }
            oci_close($con);
        ?>
    </body>
</html>

How to create an Email Web Form in PHP

<html>
    <head>
        <title>Feedback Form</title>
    </head>
    <body>
        <?php
            $HTMLFormatted = 0; // set to 1 for HTML formatting, 0 for plain
            if (!$_POST) {
        ?>
        <!-- Form Section -->
        <!-- Email addresses should never be passed from the from section, 
             instead pass a variable that can be translated to an email address
             in the Post Section. -->
        <h1>Contact Us</h1>
        <form action="<?= $PHP_SELF ?>" method="post">
            <p>Your Name: <input type="text" name="name" /></p>
            <p>Your Email: <input type="text" name="email" /></p>
            <p>Comments: <textarea name="comments"></textarea></p>
            <p><input type="submit" value="Send Email"></p>
        </form>
        <?php
            }
            else {
                // Post Section
                // Define the recipient's email address(es).
                // The recipient's email address should always be defined in the 
                // POST section.
                $recipient = "sample@purdue.edu";

                // Define subject
                $subject = "Feedback";
                $name = $_POST['name'];
                $email = $_POST['email'];
                $comments = $_POST['comments'];

                // Compose message:
                if ($HTMLFormatted) {
                    $message = "<h1>Message from $name ($email)</h1>";
                    $message .= "<b>Comments:</b><br />$comments";
                }
                else {
                    $message = "Message from $name ($email)\r\n";
                    $message .= "Comments:\r\n$comments\r\n";
                }

                // Format headers:
                if ($HTMLFormatted) { 
                    // Add Headers for HTML formatted email
                    $headers = "MIME-Version: 1.0\r\n"; 
                    $headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
                }
                else {
                    // Leave out for plain text emails.
                    $headers ="";
                }
                // add from field to the headers
                // include personal name and email
                $headers .= "From: " . $_POST['name'];
                $headers .= "<" . $_POST['email'] . ">\r\n";
                // add reply-to field to the headers
                $headers .= "Reply-To: " . $_POST['email'] . "\r\n";

                // Send the email - mail(recipient subject, message, headers)
                mail($recipient, $subject, $message, $headers);
        ?>
        <!-- Reply to the user -->
        <h1>Thank You!</h1>
        <p>Your comments have been submitted.<br />
        Thank you for your input.</p>
        <?php
            }
        ?>
    </body>
</html>

Notice how the program does two things.

  • If not handling a POST request, display the feedback form.
  • Otherwise send the email and display a response to the user.

Why not put the email address in the Form Section?

Email addresses should never be passed from the Form Section. If recipient email addresses vary, pass a variable that can be translated to an email address in the Post Section. Spammers use robots to find email addresses in HTML code. Keeping email addresses out of the Form Section will make it harder for spammers to find your email address. Also, allowing an email address to be passed directly to the Post Section allows less scrupulous visitors to use your form to send email for unintended uses.

The POST Request

When you click on that submit button, what happens? The browser sends all the form fields to the server using a POST request. A POST simply means that all the submitted fields are sent in the body of the request. In a GET request, the submitted fields are appended to the URL e.g. feedback.php?name=Jason. The POST method is preferable here because the comments field might exceed the browser’s URL length limit if a GET request was used.

Using POST, the field names and values are sent to the server in the body of the request. When the PHP script handles the request, it puts these values into an associative array called $_POST, with the field names as the keys. The value of the name field, for example, could then be accessed using $_POST['name'].

Sending the Email

First we create a $message variable containing the body of the message. Then we call the mail() function with the appropriate parameters to send the actual email.

More Information

For more information, read about php’s mail() function.