Inserting, Retrieving, Updating and Deleting Data

PHP and MySQL: Inserting, Retrieving, Updating, and Deleting Data

Working with databases is a fundamental aspect of any web development project. PHP and MySQL together provide a powerful tool for managing database operations. In this tutorial, we will learn how to insert, retrieve, update, and delete data from a MySQL database using PHP.

Establishing a Connection

Before we can perform any database operations, we need to establish a connection to the MySQL server. We can do this using PHP's mysqli_connect() function.

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

Inserting Data

Once we have a connection, we can insert data into our database using the INSERT INTO SQL command.

$sql = "INSERT INTO MyTable (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if (mysqli_query($conn, $sql)) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Retrieving Data

To retrieve data from a database, we use the SELECT SQL command.

$sql = "SELECT id, firstname, lastname FROM MyTable";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}

Updating Data

The UPDATE SQL command is used to update existing records in a table.

$sql = "UPDATE MyTable SET lastname='Doe' WHERE id=2";

if (mysqli_query($conn, $sql)) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . mysqli_error($conn);
}

Deleting Data

Finally, to delete data from a database, we use the DELETE SQL command.

$sql = "DELETE FROM MyTable WHERE id=3";

if (mysqli_query($conn, $sql)) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . mysqli_error($conn);
}

Closing the Connection

Don't forget to close the connection once you're done with your database operations. This can be done using the mysqli_close() function.

mysqli_close($conn);

And that's it! As you can see, PHP and MySQL make it quite straightforward to perform basic database operations. Practice these commands and understand how they work, and you'll be managing databases with PHP like a pro in no time!