A Comprehensive Guide to MySQL and PHP Syntax

This guide provides a thorough overview of how to use MySQL with PHP, focusing on essential syntax and key concepts that beginners need to grasp for effective web development.

Key Concepts

• MySQL: A widely-used open-source relational database management system.
• PHP: A server-side scripting language commonly employed for web development that facilitates interaction with MySQL databases.

Connecting to a MySQL Database

To interact with a MySQL database, you must establish a connection using PHP. This can be accomplished using either the mysqli extension or the PDO (PHP Data Objects) extension.

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 to interact with the database.

Example: Running a SELECT Query

$sql = "SELECT id, name 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"]. "";
    }
} else {
    echo "0 results";
}

Prepared Statements

Prepared statements are utilized to execute the same query multiple times with different parameters, which helps mitigate SQL injection attacks.

Example: Using Prepared Statements

$stmt = $conn->prepare("SELECT id, name FROM users WHERE id = ?");
$stmt->bind_param("i", $id);

$id = 1;
$stmt->execute();
$stmt->bind_result($id, $name);
$stmt->fetch();

echo "id: $id - Name: $name";
$stmt->close();

Closing the Connection

It is important to close the database connection once you are done.

Example: Closing Connection

$conn->close();

Conclusion

This summary provides a foundational understanding of how to connect to and interact with a MySQL database using PHP. Key concepts include establishing a connection, executing queries, using prepared statements, and closing the connection. With these basics, beginners can start building dynamic web applications that interact with databases.