Iqbolshoh/php-database-manager

The Query class is a versatile PHP tool using mysqli for database management. It handles essential CRUD operations (Create, Read, Update, Delete) and includes methods for user authentication and password hashing, making database interactions and user management simple and secure.

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: