Php MySQL Connection

Varanasi Software Junction March 21, 2023

Explaining how to establish a PHP connection to MySQL and a complete CRUD (Create, Read, Update, Delete) example using PHP and MySQL:
PHP is a popular server-side scripting language used for web development. It is often used with MySQL, which is a relational database management system (RDBMS) that provides a way to store and retrieve data in a structured manner. In this article, we will walk you through the steps of establishing a PHP connection to MySQL and creating a complete CRUD application using PHP and MySQL.
Establishing a PHP Connection to MySQL
To establish a PHP connection to MySQL, you need to have the following information:
Hostname: The name of the server where MySQL is installed. This is usually "localhost" if you are running MySQL on the same server as PHP.
Username and password: The username and password of a MySQL user who has permission to access the database.
Database name: The name of the MySQL database you want to connect to.
Once you have this information, you can use the following PHP code to establish a connection to MySQL:
<?php
$hostname = "localhost";
$username = "mysql_username";
$password = "mysql_password";
$database = "database_name";
// Create connection
$conn = new mysqli($hostname, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>


CRUD example for storing a book in PHP and MySQL:
Step 1: Creating the Database
First, we need to create a MySQL database and a table to store the books. We can use the following SQL code to create the books table:
CREATE TABLE books (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  subject VARCHAR(50) NOT NULL,
  author VARCHAR(50) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);
This code creates a books table with five columns: id (an auto-incrementing primary key), name, subject, author, and price.
Step 2: Creating the HTML Form
Next, we need to create an HTML form that allows users to add new books. We can use the following HTML code to create the form:
<form method="post" action="add_book.php">
  <label for="name">Name:</label>
  <input type="text" name="name" id="name" required><br>
  <label for="subject">Subject:</label>
  <input type="text" name="subject" id="subject" required><br>
  <label for="author">Author:</label>
  <input type="text" name="author" id="author" required><br>
  <label for="price">Price:</label>
  <input type="number" step="0.01" name="price" id="price" required><br>
  <input type="submit" value="Add Book">
</form>



This code creates a form with four input fields (name, subject, author, and price) and a submit button. The form's action attribute is set to "add_book.php", which is the PHP script that will process the form data and add a new book to the database.
Step 3: Creating the PHP Scripts
Now, we need to create four PHP scripts to handle the CRUD operations: add_book.php, view_books.php, edit_book.php, and delete_book.php.
add_book.php: This script handles the form data and adds a new book to the database.
<?php
// Establish MySQL connection
$hostname = "localhost";
$username = "mysql_username";
$password = "mysql_password";
$database = "database_name";
$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Get form data
$name = $_POST["name"];
$subject = $_POST["subject"];
$author = $_POST["author"];
$price = $_POST["price"];
// Prepare and execute SQL statement
$stmt = $conn->prepare("INSERT INTO books (name, subject, author, price) VALUES (?, ?, ?, ?)");
$stmt->bind_param("sssd", $name, $subject, $author, $price);
$stmt->execute();
// Redirect to view_books.php
header("Location: view_books.php");
exit();
?>

view_books.php: This script retrieves all the books from the database and displays them in a table.
// Establish MySQL connection

$hostname = "localhost";
$username = "mysql_username";
$password = "mysql_password";
$database = "database_name";

$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Retrieve books from database
$result = $conn->query("SELECT * FROM books");

// Check if any books were found
if ($result->num_rows > 0) {
  // Display books in a table
  echo "<table>";
  echo "<tr><th>Name</th><th>Subject</th><th>Author</th><th>Price</th></tr>";
  while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row["name"] . "</td>";
    echo "<td>" . $row["subject"] . "</td>";
    echo "<td>" . $row["author"] . "</td>";
    echo "<td>" . $row["price"] . "</td>";
    echo "</tr>";
  }
  echo "</table>";
} else {
  // No books found
  echo "No books found.";
}

// Close MySQL connection
$conn->close();



Example of how to delete a book from the books table in MySQL using PHP


// Establish MySQL connection
$hostname = "localhost";
$username = "mysql_username";
$password = "mysql_password";
$database = "database_name";

$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Check if book ID was provided
if (!isset($_GET["id"])) {
  header("Location: view_books.php");
  exit();
}

// Get book ID from URL parameter
$id = $_GET["id"];

// Prepare and execute SQL statement
$stmt = $conn->prepare("DELETE FROM books WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();

// Redirect to view_books.php
header("Location: view_books.php");
exit();

// Close MySQL connection
$conn->close();


Simple HTML code for displaying a list of books and providing an option to delete each book:



<!DOCTYPE html>
<html>
<head>
  <title>View Books</title>
</head>
<body>
  <h1>View Books</h1>
  <table>
    <tr>
      <th>Name</th>
      <th>Subject</th>
      <th>Author</th>
      <th>Price</th>
      <th>Action</th>
    </tr>
    <?php
    // Establish MySQL connection
    $hostname = "localhost";
    $username = "mysql_username";
    $password = "mysql_password";
    $database = "database_name";

    $conn = new mysqli($hostname, $username, $password, $database);
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }

    // Retrieve books from database
    $result = $conn->query("SELECT * FROM books");

    // Check if any books were found
    if ($result->num_rows > 0) {
      // Display books in a table
      while ($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row["name"] . "</td>";
        echo "<td>" . $row["subject"] . "</td>";
        echo "<td>" . $row["author"] . "</td>";
        echo "<td>" . $row["price"] . "</td>";
        echo "<td><a href='delete_book.php?id=" . $row["id"] . "'>Delete</a></td>";
        echo "</tr>";
      }
    } else {
      // No books found
      echo "<tr><td colspan='5'>No books found.</td></tr>";
    }

    // Close MySQL connection
    $conn->close();
    ?>
  </table>
</body>
</html>

Example of how to update a book in the books table in MySQL using PHP:




// Establish MySQL connection
$hostname = "localhost";
$username = "mysql_username";
$password = "mysql_password";
$database = "database_name";

$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Check if book ID and form data were submitted
if (isset($_GET["id"]) && isset($_POST["name"]) && isset($_POST["subject"]) && isset($_POST["author"]) && isset($_POST["price"])) {
  // Get book ID from URL parameter
  $id = $_GET["id"];

  // Get form data
  $name = $_POST["name"];
  $subject = $_POST["subject"];
  $author = $_POST["author"];
  $price = $_POST["price"];

  // Prepare and execute SQL statement
  $stmt = $conn->prepare("UPDATE books SET name = ?, subject = ?, author = ?, price = ? WHERE id = ?");
  $stmt->bind_param("ssssi", $name, $subject, $author, $price, $id);
  $stmt->execute();

  // Redirect to view_books.php
  header("Location: view_books.php");
  exit();
} elseif (isset($_GET["id"])) {
  // Get book ID from URL parameter
  $id = $_GET["id"];

  // Retrieve book from database
  $stmt = $conn->prepare("SELECT * FROM books WHERE id = ?");
  $stmt->bind_param("i", $id);
  $stmt->execute();
  $result = $stmt->get_result();

  // Check if book was found
  if ($result->num_rows == 1) {
    // Display book details in a form for editing
    $row = $result->fetch_assoc();
    ?>
    <form method="post">
      <label>Name:</label><br>
      <input type="text" name="name" value="<?php echo $row["name"]; ?>"><br>
      <label>Subject:</label><br>
      <input type="text" name="subject" value="<?php echo $row["subject"]; ?>"><br>
      <label>Author:</label><br>
      <input type="text" name="author" value="<?php echo $row["author"]; ?>"><br>
      <label>Price:</label><br>
      <input type="number" name="price" value="<?php echo $row["price"]; ?>"><br>
      <input type="submit" value="Save">
    </form>
    <?php
  } else {
    // Book not found
    echo "Book not found.";
  }
} else {
  // No book ID provided
  header("Location: view_books.php");
  exit();
}

// Close MySQL connection
$conn->close();



The bind_param() function in PHP is used to bind variables to a prepared statement as parameters. This function is used when you want to execute an SQL statement multiple times with different parameters. Binding parameters is important for security reasons because it helps prevent SQL injection attacks.

The bind_param() function takes two or more arguments:

  1. The first argument is a string that specifies the types of the variables to be bound. The types are represented by characters, and the possible types are:
  • "i" for integer
  • "d" for double
  • "s" for string
  • "b" for blob
  1. The remaining arguments are the variables to be bound to the prepared statement as parameters. The number of arguments must match the number of placeholders in the prepared statement.

For example, if you have a prepared statement with two placeholders, you would call the bind_param() function with three arguments:



$stmt = $conn->prepare("SELECT * FROM books WHERE subject = ? AND price < ?");
$stmt->bind_param("sd", $subject, $price);

Contact us for software training, education or development










 

Tags:
Varanasi Software Junction

Posted by: Varanasi Software Junction

Varanasi Software Junction is the one stop for Software Training, Education and Development in Varanasi. We are available on location and online at your service.

Post a Comment

0 Comments

Me