AngularJS and SQL: Bringing Real Data Into Your App - FadiZahhar/AngularJs GitHub Wiki

The StartApp Team Connects to the Database

Scene: From Static Data to a Real Database

The StartApp team’s Task Manager app is impressive, but it still relies on hardcoded data or static JSON files. The client now says:

“We want the app to show live data from our MySQL database—so it’s always up-to-date for every user!”

Mike (Team Lead): “We’ll need to connect AngularJS to a real database. That means building a bridge between our AngularJS app and the MySQL database using PHP as our server-side helper.”

Sara (Junior Dev): “I’ve seen that AngularJS can fetch data from PHP scripts, which in turn query the database and return results as JSON.”

How Does It Work?

  1. The database (MySQL) stores all your data (like tasks or users).
  2. A PHP script (e.g., get-tasks.php) connects to the database, fetches data, and returns it as JSON.
  3. AngularJS uses $http.get() to call the PHP script and displays the result in the app.

1. The PHP Backend: Example Script

get-tasks.php

<?php
// Connect to database
$con = mysqli_connect("localhost","dbuser","dbpass","startappdb");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect: " . mysqli_connect_error();
  exit();
}

// Query the table
$result = mysqli_query($con, "SELECT * FROM tasks");

// Store results in array
$tasks = array();
while($row = mysqli_fetch_assoc($result)) {
  $tasks[] = $row;
}

// Return as JSON
echo json_encode($tasks);

mysqli_close($con);
?>

2. The AngularJS Frontend: Fetching Data

Controller:

app.controller('taskCtrl', function($scope, $http) {
  $http.get("get-tasks.php").then(function(response) {
    $scope.tasks = response.data;
  });
});

HTML:

<ul>
  <li ng-repeat="t in tasks">{{ t.name }} - {{ t.status }}</li>
</ul>
  • Now, every time the page loads, it shows live tasks from the database.

3. Adding New Data: Inserting Records

Lina: “How do we let users add new tasks to the database from the app?”

You’ll need another PHP script, like add-task.php:

<?php
$data = json_decode(file_get_contents("php://input"));
$name = $data->name;
$status = $data->status;

$con = mysqli_connect("localhost","dbuser","dbpass","startappdb");
if (mysqli_connect_errno()) {
  echo "Failed to connect: " . mysqli_connect_error();
  exit();
}

$sql = "INSERT INTO tasks (name, status) VALUES ('$name', '$status')";
mysqli_query($con, $sql);
mysqli_close($con);
?>

Controller for Adding:

app.controller('addTaskCtrl', function($scope, $http) {
  $scope.newTask = "";
  $scope.addTask = function() {
    if ($scope.newTask) {
      $http.post("add-task.php", {name: $scope.newTask, status: 'open'})
        .then(function(response) {
          alert("Task added!");
          $scope.newTask = "";
        });
    }
  };
});

HTML:

<input ng-model="newTask" placeholder="New Task">
<button ng-click="addTask()">Add Task</button>

4. Full Example: Load and Add Tasks with MySQL

<!DOCTYPE html>
<html>
<head>
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.9/angular.min.js"></script>
</head>
<body>
<div ng-app="taskApp" ng-controller="taskCtrl">
  <h2>Task List</h2>
  <ul>
    <li ng-repeat="t in tasks">{{ t.name }} - {{ t.status }}</li>
  </ul>
</div>
<div ng-app="taskApp" ng-controller="addTaskCtrl">
  <input ng-model="newTask" placeholder="New Task">
  <button ng-click="addTask()">Add Task</button>
</div>
<script>
  var app = angular.module('taskApp', []);
  app.controller('taskCtrl', function($scope, $http) {
    $http.get("get-tasks.php").then(function(response) {
      $scope.tasks = response.data;
    });
  });
  app.controller('addTaskCtrl', function($scope, $http) {
    $scope.newTask = "";
    $scope.addTask = function() {
      if ($scope.newTask) {
        $http.post("add-task.php", {name: $scope.newTask, status: 'open'})
          .then(function(response) {
            alert("Task added!");
            $scope.newTask = "";
          });
      }
    };
  });
</script>
</body>
</html>

5. Team Discussion: Why Use PHP/MySQL with AngularJS?

  • Sara: “AngularJS can’t talk directly to databases for security reasons, so PHP scripts act as the bridge.”
  • Mike: “It keeps our database safe, lets us reuse server-side logic, and we only need to change PHP or AngularJS code to update our app.”
  • Lina: “We can now show real data to users, not just demo info!”

6. Quick Challenge: Can You Delete a Task?

Create a PHP script (delete-task.php) that deletes a task, and an AngularJS controller method to call it.

Key Takeaways

  • AngularJS connects to databases using server-side scripts (like PHP) as an API.
  • Use $http.get() for loading, $http.post() for saving/changing data.
  • Always secure your PHP scripts against SQL injection and unauthorized access!

Team Conclusion

Mike: “Now our app is truly dynamic and real. Next, let’s add more features—like editing and deleting, or try AngularJS routing!”

Try these concepts in your own setup and explore the [W3Schools AngularJS SQL TryIt Editor](https://www.w3schools.com/angular/angular_tryit.asp?filename=tryangular_sql) for the frontend.

⚠️ **GitHub.com Fallback** ⚠️