Query Class

The Query class provides a straightforward interface for handling common database operations using PHP's mysqli extension. It offers methods for connecting to a database, executing queries, and performing basic CRUD (Create, Read, Update, Delete) operations. Additionally, the class includes methods for password hashing and user authentication.

Table of Contents

• Constructor and Destructor
• Methods
  • validate()
  • executeQuery()
  • select()
  • insert()
  • update()
  • delete()
  • hashPassword()
  • authenticate()
• Usage Examples
• Technologies
• Contact

Constructor and Destructor

__construct()

• Description: Initializes a connection to the database.
• Parameters: None
• Details:
  • Connects to a MySQL database using the mysqli extension.
  • Connection parameters are hard-coded:
    • Server: localhost
    • Username: root
    • Password: "" (empty string)
    • Database Name: database
  • Displays an error message and exits if the connection fails.

__destruct()

• Description: Closes the database connection when the object is destroyed.
• Parameters: None
• Details:
  • Ensures the database connection is properly closed.

Methods

validate($data)

• Description: Escapes special characters to prevent SQL injection.
• Parameters:
  • $data: An associative array of data to be sanitized.
• Returns: The sanitized data array.
• Details:
  • Removes whitespace from the beginning and end.
  • Removes backslashes.
  • Converts special characters to HTML entities.

executeQuery($sql)

• Description: Executes a given SQL query.
• Parameters:
  • $sql: The SQL query to be executed.
• Returns: The result of the query.
• Details:
  • Displays an error message and exits if the query execution fails.

select($table, $columns = "*", $condition = "")

• Description: Retrieves data from the specified table.
• Parameters:
  • $table: The name of the table to select data from.
  • $columns: A comma-separated list of columns to retrieve (default is * for all columns).
  • $condition: An optional SQL condition (e.g., WHERE id = 1).
• Returns: An associative array of the result set.
• Details:
  • Constructs and executes a SELECT query.

insert($table, $data)

• Description: Inserts data into the specified table.
• Parameters:
  • $table: The name of the table to insert data into.
  • $data: An associative array of column names and values.
• Returns: The result of the query execution.
• Details:
  • Constructs and executes an INSERT query.

update($table, $data, $condition = "")

• Description: Updates data in the specified table.
• Parameters:
  • $table: The name of the table to update.
  • $data: An associative array of column names and new values.
  • $condition: An optional SQL condition (e.g., WHERE id = 1).
• Returns: The result of the query execution.
• Details:
  • Constructs and executes an UPDATE query.

delete($table, $condition = "")

• Description: Deletes data from the specified table.
• Parameters:
  • $table: The name of the table to delete data from.
  • $condition: An optional SQL condition (e.g., WHERE id = 1).
• Returns: The result of the query execution.
• Details:
  • Constructs and executes a DELETE query.

hashPassword($password)

• Description: Hashes a password using HMAC with SHA-256.
• Parameters:
  • $password: The plain text password to be hashed.
• Returns: The hashed password.
• Details:
  • Uses a hard-coded key "AccountPassword" for hashing.

authenticate($username, $password, $table)

• Description: Checks user credentials for login.
• Parameters:
  • $username: The username to authenticate.
  • $password: The plain text password to authenticate.
  • $table: The name of the table to check the credentials against.
• Returns: An associative array of user data if authentication is successful; otherwise, an empty array.
• Details:
  • Hashes the password and checks it against the stored hash in the specified table.

Usage Examples

Inserting Data

$query = new Query();

$data = [
    'name' => 'John',
    'last_name' => 'Doe',
    'birthday' => '1990-01-01',
    'gender' => 'Male',
    'username' => 'john_doe',
    'password' => 'password123',
    'phone_number' => '+1234567890',
    'email' => 'john@example.com',
    'profile_image' => 'profile.jpg'
];

$result = $query->insert('users', $data);

if ($result) {
    echo "Data added successfully!";
} else {
    echo "Error adding data!";
}

Updating Data

$query = new Query();

$data = [
    'password' => 'new_password123'
];

$result = $query->update('users', $data, "WHERE username = 'john_doe'");

if ($result) {
    echo "Data updated successfully!";
} else {
    echo "Error updating data!";
}

Selecting Data

$query = new Query();

$userData = $query->select('users', '*', "WHERE username = 'john_doe'");

if ($userData) {
    print_r($userData);
} else {
    echo "Error retrieving data!";
}

Deleting Data

$query = new Query();

$result = $query->delete('users', "WHERE username = 'john_doe'");

if ($result) {
    echo "Data deleted successfully!";
} else {
    echo "Error deleting data!";
}

Authenticating User

$query = new Query();

$userData = $query->authenticate('john_doe', 'password123', 'users');

if ($userData) {
    echo "Authentication successful!";
} else {
    echo "Authentication failed!";
}

Technologies Used

Contributing

Contributions are welcome! If you have suggestions or want to enhance the project, feel free to fork the repository and submit a pull request.

Connect with Me

I love connecting with new people and exploring new opportunities. Feel free to reach out to me through any of the platforms below: