Using MySQL with PHP

Página 34

MySQL is a relational database management system (RDBMS) that uses SQL (Structured Query Language) for database access. SQL is the most popular language for adding, accessing, and managing content in a database. It is noted for its fast processing speed, flexibility, reliability, robustness and ease of use. MySQL is an essential component of almost all open source software development stacks including LAMP (Linux, Apache, MySQL, PHP/Python/Perl) and is used by many popular websites including Facebook, Twitter, YouTube and many others .

PHP is a popular server scripting language and MySQL is an open source database management system. Using PHP and MySQL together allows you to create dynamic and interactive web applications. The combination of PHP and MySQL offers an easy way to create websites that interact with a database.

Configuring the MySQL Connection in PHP

To connect PHP to MySQL, we use the mysqli_connect() function. This function accepts four parameters: hostname, username, password and database name, and returns an object representing the connection to the MySQL server.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// creating connection
$conn = new mysqli($servername, $username, $password, $dbname);

// checking connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Successfully connected";
?>

Executing MySQL Queries in PHP

After establishing a connection with MySQL, we can execute SQL queries. For this, we use the mysqli_query() function. This function takes two parameters: a MySQL connection object and an SQL query string.

<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$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["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

Inserting Data into MySQL Using PHP

We can use the mysqli_query() function to insert data into a MySQL database. The SQL syntax to insert data into a database is as follows: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)

<?php
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES('John', 'Donate', '[email protected]')";

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

$conn->close();
?>

Updating Data in MySQL Using PHP

To update data in a MySQL database, we use the SQL UPDATE statement. The SQL syntax for updating data in a database is as follows: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition

<?php
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

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

$conn->close();
?>

Deleting Data in MySQL Using PHP

To delete data from a MySQL database, we use the SQL DELETE statement. The SQL syntax for deleting data from a database is as follows: DELETE FROM table_name WHERE condition

<?php
$sql = "DELETE FROM MyGuests WHERE id=1";

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

$conn->close();
?>

MySQL and PHP are powerful open source resources that can be used to develop dynamic and interactive web applications. With them, you can easily create, read, update and delete records in your database. They are an essential part of any modern web development stack.

Next page of the Free Ebook:

35Using MySQL with Python