CS310 Quiz 2 Solution and Discussion

[zaasmi]

_______ statement is used to create database named MyDatabase in MySQL.

CREATE DATABASE databasename;

[Zohaib Malik]

said in CS310 Quiz 2 Solution and Discussion:

What SQL clause is used to restrict the rows returned by a query?

The SQL clause used to restrict or filter rows based on a specific condition is the WHERE clause.

It is placed after the FROM clause and before any grouping or sorting clauses. Only the rows that meet the specified criteria (where the condition evaluates to TRUE) will be included in the result set.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Examples of Usage

Requirement	SQL Example
Exact Match	WHERE city = 'London'
Numeric Range	WHERE age > 18
Pattern Matching	WHERE name LIKE 'A%'
Multiple Conditions	WHERE price < 100 AND stock > 0

A Quick Distinction: WHERE vs. HAVING

• WHERE: Filters individual rows before any grouping occurs.
• HAVING: Filters groups after the GROUP BY clause has been applied.

[zaasmi]

If we want to show all the session variable values for a user session then following code will be used.

print_r($_SESSION);

[zaasmi]

Which of the following PHP syntax is used to remove all HTML tags from a string? CS310

[zaasmi]

How will we retrieve the value of session variable set in the following PHP code?
$_SESSION[“color”] = “green”;

[zaasmi]

_______ statement is used to add a new record to a MySQL table

[zaasmi]

Which of the following PHP syntax is used to remove all illegal characters from a URL?

[zaasmi]

In PHP, in order to access MySQL database you will use:

[zaasmi]

Which of the following PHP function is used to validate and sanitize the data?

[zaasmi]

Use …… to add table in the database

[zaasmi]

What SQL clause is used to restrict the rows returned by a query?

[Zohaib Malik]

said in CS310 Quiz 2 Solution and Discussion:

What SQL clause is used to restrict the rows returned by a query?

The SQL clause used to restrict or filter rows based on a specific condition is the WHERE clause.

It is placed after the FROM clause and before any grouping or sorting clauses. Only the rows that meet the specified criteria (where the condition evaluates to TRUE) will be included in the result set.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Examples of Usage

Requirement	SQL Example
Exact Match	WHERE city = 'London'
Numeric Range	WHERE age > 18
Pattern Matching	WHERE name LIKE 'A%'
Multiple Conditions	WHERE price < 100 AND stock > 0

A Quick Distinction: WHERE vs. HAVING

• WHERE: Filters individual rows before any grouping occurs.
• HAVING: Filters groups after the GROUP BY clause has been applied.

[zaasmi]

said in CS310 Quiz 2 Solution and Discussion:

Use …… to add table in the database

To add a new table to a database, you use the CREATE TABLE statement.

This command defines the table name and specifies the columns, including their data types (e.g., INT, VARCHAR, DATE) and any constraints (e.g., PRIMARY KEY, NOT NULL).

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint
);

Practical Example

If you wanted to create a table to store student information, the query would look like this:

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(50),
    LastName varchar(50),
    EnrollmentDate date
);

Key Points to Remember

• Unique Name: The table name must be unique within the database.
• Data Types: You must define what kind of data each column will hold.
• Primary Key: It is best practice to include a Primary Key to uniquely identify each row in your table.

[zaasmi]

said in CS310 Quiz 2 Solution and Discussion:

Which of the following PHP function is used to validate and sanitize the data?

The PHP function used for both validating and sanitizing data is filter_var().

This function is highly versatile because it allows you to check if data is in the correct format (Validation) or remove illegal characters from data (Sanitization) simply by changing the “filter” flag you pass to it.

1. Validation

Validation checks if the data meets certain criteria. If it doesn’t, the function returns false.

• Example: Checking if an email is valid.

if (filter_var("test@example.com", FILTER_VALIDATE_EMAIL)) {
    echo "Valid email address.";
}

2. Sanitization

Sanitization cleans the data by removing or encoding characters that could be harmful or unwanted.

• Example: Removing HTML tags from a string.

$clean_string = filter_var("<h1>Hello!</h1>", FILTER_SANITIZE_STRING);
// Result: "Hello!"

---

Common Filter Constants

Filter Constant	Purpose
FILTER_VALIDATE_EMAIL	Validates an email address.
FILTER_VALIDATE_INT	Validates an integer.
FILTER_VALIDATE_URL	Validates a URL.
FILTER_SANITIZE_EMAIL	Removes all characters except letters, digits and !#$%&'*±/=?^_`{|}~@.[]. |

Why use filter_var()?

It is much safer and more reliable than using custom Regular Expressions (Regex). Using these built-in filters helps protect your application against common security vulnerabilities like Cross-Site Scripting (XSS) and SQL Injection.

[zaasmi]

said in CS310 Quiz 2 Solution and Discussion:

In PHP, in order to access MySQL database you will use:

Answer: mysqli_connect() function

In modern PHP, you have two primary, recommended ways to access a MySQL database. While older versions used the mysql_ extension, that has been deprecated and removed for security reasons.

Today, you should use one of the following:

1. MySQLi (MySQL Improved)

This is a driver specifically designed for MySQL databases. It supports both procedural (using functions) and object-oriented (using classes) programming styles.

• Best for: Projects that only use MySQL and want to take advantage of MySQL-specific features like stored procedures.

// Object-oriented example
$conn = new mysqli("localhost", "username", "password", "database");

2. PDO (PHP Data Objects)

PDO is a database abstraction layer. It provides a consistent way to interact with many different types of databases (MySQL, PostgreSQL, SQLite, etc.).

• Best for: Flexibility. If you ever decide to switch your database from MySQL to another system, you won’t have to rewrite your entire data access code.

// PDO example
$dsn = "mysql:host=localhost;dbname=testdb";
$conn = new PDO($dsn, "username", "password");

---

Comparison at a Glance

Feature	MySQLi	PDO
Database Support	MySQL only	12 different drivers
Programming Style	Procedural & OO	Object-Oriented only
Prepared Statements	Supported (Secure)	Supported (Secure)
Named Parameters	Not supported	Supported

Security Note: Regardless of which you choose, always use Prepared Statements to protect your database from SQL Injection attacks.

[zaasmi]

said in CS310 Quiz 2 Solution and Discussion:

Which of the following PHP syntax is used to remove all illegal characters from a URL?

To remove all illegal characters from a URL in PHP, you use the filter_var() function combined with the FILTER_SANITIZE_URL constant.

The Syntax

$url = "https://example.com/search?q=hello world!";
$clean_url = filter_var($url, FILTER_SANITIZE_URL);

How it Works

This specific filter is designed to clean a string by removing characters that are not permitted in a URL. It allows all letters, digits, and the following special characters:
$-_.+!*'(),{}|\\^~[]<>#%";/?:@&=`

Key Differences in URL Handling

It is important to distinguish between sanitizing (cleaning) and validating (checking):

Function Goal	Filter Constant	Result
Sanitize	FILTER_SANITIZE_URL	Returns a “clean” string with illegal characters removed.
Validate	FILTER_VALIDATE_URL	Returns the URL if it’s in a valid format, or false if it isn’t.

---

Would you like me to show you how to combine both to first clean a URL and then check if the result is actually valid?

[zaasmi]

said in CS310 Quiz 2 Solution and Discussion:

_______ statement is used to add a new record to a MySQL table

The INSERT INTO statement is used to add a new record (row) to a MySQL table.

Basic Syntax

There are two primary ways to write this statement:

1. Specifying both column names and values:
This is the safest method as it ensures data goes into the correct fields.

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

2. Specifying values only:
You can omit column names if you are providing values for every column in the exact order they appear in the table.

INSERT INTO table_name
VALUES (value1, value2, value3);

---

Practical Example

If you have a table named Users, you would add a new person like this:

INSERT INTO Users (username, email, age)
VALUES ('jdoe', 'john@example.com', 25);

Key Tips

• Strings: Always wrap text/string values in single quotes (e.g., 'Hello').
• Numbers: Numeric values do not require quotes.
• Auto-Increment: If your table has an id column set to AUTO_INCREMENT, you don’t need to include it in your INSERT statement; MySQL will generate the number for you automatically.