Connecting PHP and MySQL for Database-Driven Web Development with Angular: A Practical Tutorial

Here are the step-by-step instructions, along with sample code for each step:

Step 1: Install and configure a local server

For this step, we'll assume you are using XAMPP. Here's how to install it:

1. Download XAMPP from the official website: https://www.apachefriends.org/index.html
2. Run the installer and follow the instructions to install XAMPP.
3. Once the installation is complete, launch the XAMPP control panel and start the Apache and MySQL services.

Step 2: Install MySQL

MySQL is included with XAMPP, so you don't need to install it separately.

Step 3: Create a database

To create a database, you can use the phpMyAdmin tool that comes with XAMPP. Here's how to create a database:

1. Open your web browser and go to https://localhost/phpmyadmin/
2. Click on the "Databases" tab.
3. Enter a name for your database in the "Create database" field and click on the "Create" button.

or simply write query:

create database my_db;        

Create a table in your MySQL database using the following SQL code:

CREATE TABLE mytable(
? id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
? name VARCHAR(30) NOT NULL,
? email VARCHAR(50) NOT NULL
);
        

insert some sample data into the table using the following SQL code:

INSERT INTO mytable (name, email)
VALUES ('John Doe', 'john@example.com'),
? ? ? ?('Jane Smith', 'jane@example.com'),
? ? ? ?('Bob Johnson', 'bob@example.com');        

Step 4: Set up a PHP script

Here's an example of how to create a PHP script that can connect to the database and retrieve data:

<?php

// Set database credentials
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_db";

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

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

// Query the database
$sql = "SELECT * FROM mytable";
$result = mysqli_query($conn, $sql);

// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
    // Loop through the rows and output the data
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No results found.";
}

// Close the connection
mysqli_close($conn);

?>        

But For you need to install these :

1. Open a command prompt or terminal window and navigate to the project directory.
2. Run the following command to install Slim:

composer require slim/slim "^3.0"        

If the composer is not installed install it from here: https://getcomposer.org/

Step 5: Create a RESTful API

To create a RESTful API in PHP, you can use a lightweight framework such as Slim. Here's an example of how to create a simple API that returns data in JSON format:

<?php



require 'vendor/autoload.php';



$app = new \Slim\App();



// Define API routes
$app->get('/users', function () use ($app) {



? ? // Set database credentials
? ? $servername = "localhost";
? ? $username = "root";
? ? $password = "";
? ? $dbname = "my_db";



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



? ? // Check connection
? ? if (!$conn) {
? ? ? ? $app->response()->setStatus(500);
? ? ? ? $app->response()->setBody("Connection failed: " . mysqli_connect_error());
? ? ? ? return;
? ? }



? ? // Query the database
? ? $sql = "SELECT * FROM mytable";
? ? $result = mysqli_query($conn, $sql);



? ? // Check if any rows were returned
? ? if (mysqli_num_rows($result) > 0) {
? ? ? ? // Loop through the rows and store the data in an array
? ? ? ? $data = array();
? ? ? ? while ($row = mysqli_fetch_assoc($result)) {
? ? ? ? ? ? $data[] = $row;
? ? ? ? }



? ? ? ? // Return the data in JSON format
? ? ? ? $app->response()->headers->set('Content-Type', 'application/json');
? ? ? ? $app->response()->setBody(json_encode($data));
? ? } else {
? ? ? ? // No results found
? ? ? ? $app->response()->setStatus(404);
? ? ? ? $app->response()->setBody("No results found.");
? ? }



? ? // Close the connection
? ? mysqli_close($conn);



});



// Run the API
$app->run();



?>        

Step 6: Set up Angular

To set up Angular, you can use the Angular CLI. Here's how to create a new Angular project:

1. Open a command prompt and navigate to a directory where you want to create your project.
2. Run the following command to create a new Angular project:

ng new my-app        

3. Follow the prompts to select the project options and dependencies.

Step 7: Display data in your Angular application

Here's an example of how to display the data retrieved from the MySQL database in your Angular application:

1. Open the app.component.ts file in your Angular project.
2. Add the following code to the file to create a variable that will hold the data:

users: any[];        

3. Add the following code to the ngOnInit() function to retrieve the data from the API:

ngOnInit() {
    this.http.get<any[]>('https://localhost/webapp').subscribe(data => {
        this.users = data;
    });
}
        

4. Open the app.component.html file in your Angular project

5. Add the following code to the file to display the data in a table:

<table>
? ? <thead>
? ? ? ? <tr>
? ? ? ? ? ? <th>Name</th>
? ? ? ? ? ? <th>Email</th>
? ? ? ? </tr>
? ? </thead>
? ? <tbody>
? ? ? ? <tr *ngFor="let user of users">
? ? ? ? ? ? <td>{{ user.name }}</td>
? ? ? ? ? ? <td>{{ user.email }}</td>
? ? ? ? </tr>
? ? </tbody>
</table>        

Now, app.component.html:

<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Email</th>
    </tr>
  </thead>
  <tbody>
    <tr *ngFor="let user of users">
      <td>{{ user.name }}</td>
      <td>{{ user.email }}</td>
    </tr>
  </tbody>
</table>        

app.component.ts:

import { Component, OnInit } from '@angular/core';
import { HttpClient } from '@angular/common/http';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent implements OnInit {

  users: any[];

  constructor(private http: HttpClient) {}

  ngOnInit() {
    this.http.get<any[]>('https://localhost/webapp/users').subscribe(data => {
      this.users = data;
    });
  }

}