Php Connecting Php With Mysql Complete Guide

PHP Connecting PHP with MySQL

Step-by-Step Guide to Connecting PHP with MySQL

1. Install PHP and MySQL:

   • To start, ensure that both PHP and MySQL are installed on your server. You can use pre-packaged solutions like XAMPP, WAMP, or MAMP for convenient installation and management.

2. Start the MySQL Server:

   • Make sure that your MySQL server is running. If you're using a package like XAMPP, you can start the MySQL server through the control panel.

3. Create a MySQL Database:

   • Open your MySQL database management tool (phpMyAdmin, MySQL Workbench, etc.) and create a new database where your application will store data. For example, let's name it my_database.

   • Once the database is created, generate a table with necessary fields. Here is an example SQL command to create a table named users:

     CREATE TABLE users (
         id INT(11) NOT NULL AUTO_INCREMENT,
         username VARCHAR(50) NOT NULL,
         password VARCHAR(255) NOT NULL,
         PRIMARY KEY (id)
     );
     

4. Connect PHP with MySQL:

   • Use PHP's mysqli or PDO extension to connect to the MySQL database. Below are examples using both methods.

Using mysqli

• Initialize a connection using mysqli_connect() and perform queries using mysqli_query():

  <?php
  $servername = "localhost";
  $username = "root"; // Default username for XAMPP/WAMP/MAMP
  $password = ""; // Default password for XAMPP/WAMP/MAMP
  $dbname = "my_database";
  
  // Create connection
  $conn = mysqli_connect($servername, $username, $password, $dbname);
  
  // Check connection
  if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
  }
  
  echo "Connected successfully";
  
  // Close connection
  mysqli_close($conn);
  ?>
  

Using PDO

• PDO provides a more secure way to interact with the database by supporting prepared statements.

  <?php
  $dsn = 'mysql:host=localhost;dbname=my_database;charset=utf8';
  $username = 'root';
  $password = '';
  
  try {
      $pdo = new PDO($dsn, $username, $password);
      echo "Connected successfully";
  
  } catch (PDOException $e) {
      echo 'Connection failed: ' . $e->getMessage();
  }
  
  // Close connection
  $pdo = null;
  ?>
  

5. Execute SQL Queries:

   • Once the connection is established, you can execute SQL queries to insert, read, update, and delete data from the database.

   • Insert Data:

     // Insert data using mysqli
     $sql = "INSERT INTO users (username, password) VALUES ('john_doe', 'securepassword')";
     if (mysqli_query($conn, $sql)) {
         echo "New record created successfully";
     } else {
         echo "Error: " . $sql . "<br>" . mysqli_error($conn);
     }
     
     // Insert data using PDO
     $sql = "INSERT INTO users (username, password) VALUES (:username, :password)";
     $stmt = $pdo->prepare($sql);
     $stmt->execute(['username' => 'john_doe', 'password' => 'securepassword']);
     

   • Read Data:

     // Read data using mysqli
     $sql = "SELECT id, username FROM users";
     $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["username"]. "<br>";
         }
     } else {
         echo "0 results";
     }
     
     // Read data using PDO
     $sql = "SELECT id, username FROM users";
     $stmt = $pdo->query($sql);
     while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
         echo "id: " . $row["id"]. " - Name: " . $row["username"]. "<br>";
     }
     

   • Update Data:

     // Update data using mysqli
     $sql = "UPDATE users SET username='new_username' WHERE id=1";
     if (mysqli_query($conn, $sql)) {
         echo "Record updated successfully";
     } else {
         echo "Error updating record: " . mysqli_error($conn);
     }
     
     // Update data using PDO
     $sql = "UPDATE users SET username=:username WHERE id=:id";
     $stmt = $pdo->prepare($sql);
     $stmt->execute(['username' => 'new_username', 'id' => 1]);
     

   • Delete Data:

     // Delete data using mysqli
     $sql = "DELETE FROM users WHERE id=1";
     if (mysqli_query($conn, $sql)) {
         echo "Record deleted successfully";
     } else {
         echo "Error deleting record: " . mysqli_error($conn);
     }
     
     // Delete data using PDO
     $sql = "DELETE FROM users WHERE id=:id";
     $stmt = $pdo->prepare($sql);
     $stmt->execute(['id' => 1]);
     

6. Handle Exceptions and Errors:

   • Always ensure that you handle database errors and exceptions properly to maintain the stability and security of your application.

Important Information

• Prepared Statements: Using prepared statements helps prevent SQL injection attacks, a common security vulnerability.
• Database Credentials: Never hard-code sensitive information such as database credentials in your scripts. Use environment variables or configuration files to manage credentials securely.
• Error Handling: Make sure to include error handling mechanisms to manage issues that may arise during database operations.