🧩 AppML – Using AppML with SQL Databases: Connect and Query Like a Pro

🧲 Introduction – Why Integrate AppML with SQL?

AppML (Application Modeling Language) thrives in scenarios where structured data is essential. While JSON and XML are great for static content, SQL databases like MySQL, PostgreSQL, or SQL Server bring dynamic power to your AppML apps.

With just a few lines of HTML and a backend script (PHP/ASP), you can load, filter, and render data from SQL tables into your AppML-powered interface—without writing JavaScript.

🎯 In this guide, you’ll learn:

• How to set up AppML with SQL databases
• Create backend scripts to query SQL tables
• Use sqlfilter, pagination, and sorting features
• Best practices for secure and optimized SQL integration

---

🗃️ How AppML Communicates with SQL Databases

AppML uses a controller or appml-data reference to connect to a PHP/ASP script, which then runs an SQL query and returns a JSON response.

🔁 Basic Flow:

[AppML HTML View]
        ↓

[appml-data=”get-data.php”]

↓ [PHP/ASP Script] ↓ [SQL Database → Returns JSON → Back to AppML]

✅ The database logic stays server-side while the view is managed by AppML.

---

🧪 Step-by-Step Example – Display Products from MySQL

1️⃣ Create MySQL Table

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  price DECIMAL(10, 2),
  category VARCHAR(50)
);

2️⃣ Add Sample Data

INSERT INTO products (name, price, category) VALUES
('Laptop', 999.99, 'Electronics'),
('Chair', 49.99, 'Furniture');

3️⃣ PHP Script (get-products.php)

<?php
$conn = new mysqli("localhost", "root", "", "store");

$filter = isset($_GET["filter"]) ? "WHERE " . $_GET["filter"] : "";
$sql = "SELECT name, price, category FROM products $filter";

$result = $conn->query($sql);
$data = [];

while ($row = $result->fetch_assoc()) {
  $data[] = $row;
}

echo json_encode($data);
?>

4️⃣ AppML View (HTML)

<div appml-data="get-products.php">
  <h3>{{name}}</h3>
  <p>Price: ${{price}}</p>
  <small>Category: {{category}}</small>
</div>

🎉 AppML will dynamically display all products retrieved from the SQL database.

---

🎛️ Adding SQL Filtering with Controller

Controller File (product-controller.js)

myAppML.onshow = function() {
  myAppML.sqlfilter = "category='Electronics'";
};

Updated HTML

<div appml-data="get-products.php" appml-controller="product-controller.js">
  <h3>{{name}}</h3>
  <p>${{price}}</p>
</div>

✅ This filters data server-side and displays only “Electronics” category.

---

📊 Pagination and Sorting in SQL Queries

Enhance your backend with pagination support:

$start = $_GET["start"] ?? 0;
$limit = $_GET["limit"] ?? 10;

$sql = "SELECT * FROM products $filter LIMIT $start, $limit";

⚠️ You must implement the start and limit logic manually, as AppML does not auto-handle pagination without backend support.

---

🧰 Supported SQL Databases with AppML

🛢️ Database	✅ Supported via
MySQL	✅ PHP/MySQLi
PostgreSQL	✅ PHP pg_connect
SQL Server	✅ ASP/ADO
SQLite	✅ PHP PDO

AppML supports any SQL database as long as your server-side script returns JSON.

---

🔐 Security Tips for SQL Integration

• ✅ Always sanitize input using prepared statements or mysqli_real_escape_string.
• ✅ Never expose raw SQL errors to the browser.
• ✅ Serve data over HTTPS and protect your backend files with proper permissions.
• ✅ Avoid sending full records if only a few fields are needed.

---

🧠 Real-World Use Case: Admin Dashboard

<div appml-data="get-orders.php">
  <h4>Order #{{order_id}}</h4>
  <p>Total: ${{total}}</p>
  <p>Status: {{status}}</p>
</div>

This can power your live dashboards, inventory systems, or analytics reports using SQL as a central data source.

---

📌 Summary – Recap & Key Takeaways

AppML makes it incredibly simple to connect HTML interfaces with backend SQL databases. With minimal HTML and backend scripting, you can build powerful, data-rich apps.

🔍 Key Takeaways:

• Use appml-data to connect AppML to a PHP/ASP script
• The backend queries the SQL database and returns JSON
• Add filtering, pagination, and sorting using URL params or controllers
• Secure your SQL integration with sanitized inputs and restricted queries

⚙️ AppML + SQL is a low-code way to connect real-time databases to modern frontends.

---

❓ FAQs – Using SQL with AppML

---

❓ Does AppML connect directly to a MySQL database?
❌ No. You need a server-side script (PHP/ASP) to connect and return JSON.

---

❓ What is the required format for SQL data in AppML?
✅ The backend should return an array of JSON objects (e.g., [{"name": "Laptop", "price": 999}]).

---

❓ Can I perform filtering using SQL queries?
✅ Yes, by setting myAppML.sqlfilter in the controller and applying it in the backend script.

---

❓ Is pagination supported in AppML for SQL data?
⚠️ Not automatically. You must handle pagination logic manually in your server-side script.

---

❓ Can I update or delete SQL records via AppML?
✅ Yes, using HTML forms + backend PHP/ASP scripts that handle SQL UPDATE and DELETE.

---

Share Now :