teaching_web_development

SQL integration with PHP

Overview

Integrating SQL databases with PHP is essential for building dynamic web applications that can store, retrieve, and manipulate data. This section covers the basics of connecting to a MySQL database using PHP, executing SQL queries, and handling the results.

• 💡 Video by Dr. Severance on PHP and MySQL part 1

• Video by Dr. Severance on PHP and MySQL part 2

Setting Up the Environment (phpMyAdmin)

To work with MySQL databases in PHP, you can use tools like phpMyAdmin, which provides a web interface for managing MySQL databases.

1. Install XAMPP: XAMPP is a free and open-source cross-platform web server solution stack package that includes Apache, MySQL, PHP, and Perl. Download and install it from here.
2. Start Apache and MySQL: Open the XAMPP Control Panel and start the Apache and MySQL services.
3. Access phpMyAdmin: Open your web browser and go to http://localhost/phpmyadmin/. This will take you to the phpMyAdmin interface where you can create and manage databases.

Connecting to a MySQL Database

To connect to a MySQL database using PHP, you can use the mysqli extension or PDO (PHP Data Objects). Here’s an example using mysqli:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

Executing SQL Queries

Once connected, you can execute SQL queries using the query method. Here’s an example of selecting data from a table:

$sql = "SELECT id, name, email FROM Users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

Inserting Data into the Database

You can also insert data into the database using an INSERT SQL statement:

$sql = "INSERT INTO Users (name, email) VALUES ('John Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Closing the Connection

After completing your database operations, it’s important to close the connection:

$conn->close();

Best Practices

• Always sanitize user inputs to prevent SQL injection attacks. Use prepared statements with parameterized queries.
• Example of a SQL injection attack is here:

  $stmt = $conn->prepare("SELECT id, name, email FROM Users WHERE email = ?");
  $stmt->bind_param("s", $email);
  $stmt->execute();
  $result = $stmt->get_result();
  

  • Example of preventing SQL injection using prepared statements:

    $stmt = $conn->prepare("INSERT INTO Users (name, email) VALUES (?, ?)");
    $stmt->bind_param("ss", $name, $email);
    $stmt->execute();
    

• Use error handling to manage database connection issues and query failures.
• Handle database errors gracefully and log them for debugging.

SQL injection

• xkcd cartoon on SQL injection: A humorous take on the importance of securing SQL queries against injection attacks.
• Video by Dr. Severance on SQL injection: An educational video explaining SQL injection and how to prevent it.
• Use execute and prepare statements to prevent SQL injection attacks.
• This will escape any malicious characters in user input.

Reading Materials

• PHP MySQL w3schools: A comprehensive guide on using MySQL with PHP.

• Next: Cookies and Sessions

This site is open source. Improve this page.