Introduction to MySQL

MySQL is one of the most popular relational database management systems (RDBMS). It is widely used for managing databases in web applications and enterprise environments. It is known for its reliability, ease of use, and robust performance capabilities. In this guide, we explore dozens of useful MySQL APIs and their applications with code snippets.

Connecting to MySQL Database

To connect to a MySQL database, you need to use the mysql library in your application code. Below is an example of how to establish a connection:

connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  echo "Connected successfully";
  $conn->close();
?>

Creating a Database

To create a database in MySQL, you can use the CREATE DATABASE statement:

query($sql) === TRUE) {
    echo "Database created successfully";
  } else {
    echo "Error creating database: " . $conn->error;
  }
?>

Creating a Table

To create a table, use the CREATE TABLE statement:

query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
  } else {
    echo "Error creating table: " . $conn->error;
  }
?>

Inserting Data into a Table

To insert data into a table, use the INSERT INTO statement:

query($sql) === TRUE) {
    echo "New record created successfully";
  } else {
    echo "Error: " . $sql . "
" . $conn->error;
  }
?>

Retrieving Data from a Table

To retrieve data, use the SELECT statement:

query($sql);

  if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
      echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
";
    }
  } else {
    echo "0 results";
  }
?>

Updating Data in a Table

To update data, use the UPDATE statement:

query($sql) === TRUE) {
    echo "Record updated successfully";
  } else {
    echo "Error updating record: " . $conn->error;
  }
?>

Deleting Data from a Table

To delete data, use the DELETE statement:

query($sql) === TRUE) {
    echo "Record deleted successfully";
  } else {
    echo "Error deleting record: " . $conn->error;
  }
?>

Creating a PHP Application with MySQL

Let’s create a simple PHP application that performs CRUD operations. The following example shows the main file, index.php:

<?php
  $servername = "localhost";
  $username = "username";
  $password = "password";
  $dbname = "myDB";
  
  $conn = new mysqli($servername, $username, $password, $dbname);
  
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  
  $action = $_POST['action'];
  
  if ($action == "create") {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];
    $email = $_POST['email'];
    $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('" . $firstname . "', '" . $lastname . "', '" . $email . "')";
    $conn->query($sql);
  } else if ($action == "read") {
    $sql = "SELECT id, firstname, lastname FROM MyGuests";
    $result = $conn->query($sql);
    while($row = $result->fetch_assoc()) {
      echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
";
    }
  } else if ($action == "update") {
    $id = $_POST['id'];
    $lastname = $_POST['lastname'];
    $sql = "UPDATE MyGuests SET lastname='" . $lastname . "' WHERE id=" . $id;
    $conn->query($sql);
  } else if ($action == "delete") {
    $id = $_POST['id'];
    $sql = "DELETE FROM MyGuests WHERE id=" . $id;
    $conn->query($sql);
  }
  
  $conn->close();
?>

In addition to the index.php, you will need to create forms to interact with this file. Below is an example of forms for each CRUD operation:

<form action="index.php" method="post">
  <input type="hidden" name="action" value="create">
  First name: <input type="text" name="firstname"><br>
  Last name: <input type="text" name="lastname"><br>
  Email: <input type="text" name="email"><br>
  <input type="submit">
</form>

<form action="index.php" method="post">
  <input type="hidden" name="action" value="read">
  <input type="submit" value="Read">
</form>

<form action="index.php" method="post">
  <input type="hidden" name="action" value="update">
  ID: <input type="text" name="id"><br>
  Last name: <input type="text" name="lastname"><br>
  <input type="submit">
</form>

<form action="index.php" method="post">
  <input type="hidden" name="action" value="delete">
  ID: <input type="text" name="id"><br>
  <input type="submit">
</form>

These examples provide the foundation for managing MySQL databases within a PHP application. Customize the forms and queries to meet your specific requirements.

Hash: 430005175c4c7810996d3481f0dbc3ec01103d6abcc5beec5db4b3f1eae35047