Fundamentals of Web and Database Technologies

Fundamentals of Web and Database Technologies

Welcome to this comprehensive module on core web and database concepts. Whether you are a budding developer or a seasoned programmer, mastering these fundamentals will improve your ability to build secure, efficient, and responsive web applications. This course is organized into clear sections, each focusing on a specific topic that appeared in the quiz. By the end of the lesson you will understand MySQL user privileges, SQL table creation, CSS selectors, responsive design practices, HTML5 semantic elements, and PHP increment operators.

1. MySQL Administrative Users and Privileges

In MySQL, the default super‑user account is root. This account has full administrative privileges, allowing it to create databases, manage users, and execute any SQL statement. Because of its power, the root account should be protected with a strong password and, whenever possible, limited to local connections.

• Why root is special: It bypasses the privilege system, similar to the Administrator account on Windows.
• Best practices:
  • Create dedicated users for each application.
  • Grant only the minimum privileges required (principle of least privilege).
  • Use CREATE USER and GRANT statements to manage rights.

2. Interpreting SHOW GRANTS Output

The SHOW GRANTS command reveals the exact permissions assigned to a MySQL user. For a user named klient, the output may include a line such as:

GRANT SELECT ON *.* TO 'klient'@'%'

This indicates that klient can execute SELECT on any table across all databases. It does not grant the ability to modify data or grant privileges to other users.

• Key takeaway: SELECT privileges allow read‑only access, which is ideal for reporting or analytics roles.
• Security tip: Avoid granting global SELECT rights unless absolutely necessary; instead, limit access to specific databases or tables.

3. Understanding the TRUNCATE TABLE Command

The MySQL TRUNCATE TABLE statement removes all rows from a table while preserving the table structure, indexes, and constraints. It is faster than a DELETE FROM table without a WHERE clause because it deallocates data pages directly.

• When to use: Quickly clearing a staging table or resetting a log table.
• Important note: TRUNCATE cannot be rolled back in MySQL unless you are using a transactional storage engine like InnoDB and have disabled autocommit.

4. CSS Selectors: Targeting Specific Elements

CSS provides powerful selectors to style elements based on their relationship in the DOM. To style only the <i> element that immediately follows a <b> element, use the adjacent sibling selector:

b + i { /* styles here */ }

This selector matches an i element that is the next sibling of a b element, without affecting other i tags elsewhere on the page.

• Other useful selectors:
  • b > i – selects i elements that are direct children of b.
  • b i – selects any i descendant of b, regardless of depth.
  • i + b – selects a b element that follows an i element.

5. Building Responsive Web Pages

Responsive design ensures that a web page looks good on devices of all sizes. One simple yet effective technique is to size images using percentages rather than fixed pixel values. For example:

<img src="photo.jpg" style="width: 100%; height: auto;">

This makes the image scale proportionally with its container, adapting to mobile screens, tablets, and desktops.

• Additional responsive practices:
  • Use CSS Flexbox or Grid for fluid layouts.
  • Apply media queries to adjust typography and layout at breakpoints.
  • Avoid table‑based layouts; they are rigid and hinder responsiveness.
• SEO benefit: Faster loading times on mobile devices improve search‑engine rankings.

6. Creating Tables with SQL

To define a new table named samochod (Polish for “car”) with two text columns (marka and model) and a decimal column (cena for price), the correct syntax is:

CREATE TABLE samochod (
    marka VARCHAR(30),
    model VARCHAR(30),
    cena DECIMAL(15,2)
);

Key points:

• VARCHAR vs CHAR: VARCHAR stores variable‑length strings, saving space compared to CHAR.
• DECIMAL(15,2): Allows up to 13 digits before the decimal point and 2 digits after, ideal for monetary values.
• Never use VALUES in a CREATE TABLE statement; VALUES belongs to INSERT commands.

7. Semantic HTML5 Elements for Navigation

HTML5 introduced several semantic tags to improve document structure and accessibility. The element specifically designed for site navigation is nav. It groups major navigation links, helping screen readers and search engines understand the page hierarchy.

<nav>
  <ul>
    <li><a href="/home">Home</a></li>
    <li><a href="/about">About</a></li>
    <li><a href="/contact">Contact</a></li>
  </ul>
</nav>

• Other semantic tags: header, main, aside, and footer.
• Using these tags improves SEO because search engines can more easily parse the page’s logical sections.

8. PHP Increment Operators

In PHP, the post‑increment operator $b++ increases the variable $b by one, but returns the original value before the increment. This is equivalent to:

$b = $b + 1;

Understanding the difference between post‑increment ($b++) and pre‑increment (++$b) is crucial when the expression’s result is used immediately.

• Example:

  $a = 5;
  $b = $a++; // $b gets 5, $a becomes 6

• Common pitfall: Using $b++ inside a function call may lead to unexpected values if not carefully managed.

9. Putting It All Together – Mini Project

To reinforce the concepts, build a simple web page that displays a list of cars from a MySQL database. Follow these steps:

1. Create the database and table:

   CREATE DATABASE showroom;
   USE showroom;
   CREATE TABLE samochod (
     id INT AUTO_INCREMENT PRIMARY KEY,
     marka VARCHAR(30),
     model VARCHAR(30),
     cena DECIMAL(15,2)
   );

2. Insert sample data:

   INSERT INTO samochod (marka, model, cena) VALUES
   ('Toyota', 'Corolla', 19999.99),
   ('Ford', 'Focus', 17950.00);
   

3. Write a PHP script to fetch and display the data:

   <!DOCTYPE html>
   <html lang="en">
   <head>
     <meta charset="UTF-8">
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <title>Car Showroom</title>
     <style>
       img { width: 100%; height: auto; }
       nav a { margin-right: 15px; }
     </style>
   </head>
   <body>
     <nav>
       <a href="#">Home</a>
       <a href="#">Inventory</a>
     </nav>
     <h2>Available Cars</h2>
     <ul>
       query('SELECT marka, model, cena FROM samochod') as $row) {
           echo "
   {$row['marka']} {$row['model']} – \\${row['cena']}
   ";
       }
       ?>
     </ul>
   </body>
   </html>

4. Apply CSS: Use the b + i selector to style any italic text that follows a bold tag, and ensure images scale with percentages for responsiveness.

By completing this mini‑project you will have practiced:

• Creating tables with proper data types.
• Granting appropriate MySQL privileges.
• Using semantic HTML5 tags for navigation.
• Applying CSS selectors and responsive techniques.
• Implementing PHP increment logic where needed.

10. Quick Review Checklist

• MySQL root user: Default super‑user with full privileges.
• SELECT privilege: Allows read‑only access to any table when granted globally.
• TRUNCATE TABLE: Removes all rows but keeps the table definition.
• CSS adjacent sibling selector: b + i targets an i element immediately after a b element.
• Responsive images: Use percentages for width to adapt to different screens.
• CREATE TABLE syntax: Define column names, data types, and constraints correctly.
• HTML5 navigation element: nav improves accessibility and SEO.
• PHP post‑increment: $b++ is equivalent to $b = $b + 1.

Continue practicing these concepts, and you will build a solid foundation for more advanced web development and database management topics.