PHP Classes

PHP MySQL Handling Class: Execute common MySQL database queries using MySQLi

Recommend this page to a friend!
  Info   View files Example   Screenshots Screenshots   View files View files (12)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2024-01-04 (2 months ago) RSS 2.0 feedNot enough user ratingsTotal: 58 All time: 10,472 This week: 106Up
Version License PHP version Categories
php-mysql-handling-c 1.0MIT/X Consortium ...5PHP 5, Databases
Description 

Author

This class can execute common MySQL database queries using MySQLi.

It can connect to a given MySQL database server using the MySQLi extension.

The class can also execute common SQL queries using given parameters, for instance:

- Get the list of database tables and the respective columns

- Delete records of a table that match a given criteria

- Insert table records using an array to set the column values

- Retrieve column values of records of a given table

Picture of Cuthbert Martin Lwinga
  Performance   Level  
Name: Cuthbert Martin Lwinga <contact>
Classes: 3 packages by
Country: Canada Canada
Age: ???
All time rank: 419890 in Canada Canada
Week rank: 45 Up2 in Canada Canada Up

Instructions

Instructions for Using the MySql PHP Class

This document provides instructions and examples for using the MySql PHP class to perform database operations such as single insert, select, and join. These examples assume that you have already set up the MySql class and have the necessary database credentials.

  1. Single Insert Operation: --------------------------- This operation is used to insert a single record into a specified table in your database.
  • First, initialize the MySql class with your database credentials.
  • Select the table where you want to insert data using the Table method.
  • Use the insert method of the table object to insert data. Pass an associative array where keys are column names and values are the data to insert.
  • Optionally, use a method like CheckOutInsert to verify if the insertion was successful.
  1. Select Operation: --------------------- This operation is used to select records from a specified table.
  • Initialize the MySql class with your database credentials.
  • Select the desired table.
  • Use the select method and pass an associative array where keys are the column names and values are the values to match. Use an empty string for selecting all values of that column.
  • Use CheckOutSelect to fetch the result set and process it as needed.
  1. Join Operation (e.g., INNER JOIN): -------------------------------------- This operation is used to perform a join (e.g., INNER JOIN) between two tables.
  • Initialize the MySql class for both tables involved in the join.
  • Perform a select operation on the primary table.
  • Perform a select operation on the secondary table. Indicate that a join is intended (usually with an additional parameter).
  • Use the INNERJOIN method to join the two tables.
  • Specify the join condition using the ON method.
  • Fetch the joined data using CheckOutSelect and process it as required.

Below are the PHP code snippets for each of these operations:

Single Insert Operation

Function: singleInsertTest

function singleInsertTest() {
    $sql = new MySql(Database::test, UserConnection::username, UserConnection::password);
    $table = MySql::Table("your_table_name");
    $table->insert(array("ColumnName1" => "Value1", "ColumnName2" => "Value2"));
    if ($table->CheckOutInsert()) {
        echo "Insert successful";
    } else {
        echo "Insert not successful";
    }
}

Select Operation

Function: selectTest

function selectTest() {
    $sql = new MySql(Database::test, UserConnection::username, UserConnection::password);
    $table = MySql::Table("your_table_name");
    $table->select(array("ColumnName1" => "", "ColumnName2" => "SpecificValue"));
    $output = $table->CheckOutSelect();
    if ($output != null) {
        while ($row = $output->fetch_assoc()) {
            echo "Data: " . $row["ColumnName1"] . "
";
        }
    }
}

Join Operation (e.g., INNER JOIN)

Function: innerJointest

function innerJointest() {
    $sql1 = new MySql(Database::test, UserConnection::username, UserConnection::password);
    $table1 = MySql::Table("first_table_name");
    $table1->select(array("id" => "", "ColumnName" => "Value"));

    $sql2 = new MySql(Database::test, UserConnection::username, UserConnection::password);
    $table2 = MySql::Table("second_table_name");
    $table2->select(array("ForeignKey" => "", "AnotherColumn" => ""), true);

    $table1->INNERJOIN($table2);
    $table1->ON([[$table1->getTablecol("id"), $table2->getTablecol("ForeignKey")]]);

    $output = $table1->CheckOutSelect();
    if ($output != NULL && $output->num_rows > 0) {
        while ($row = $output->fetch_assoc()) {
            var_dump($row);
        }
    }
}

Example

<?PHP
include_once("headers.php");

function
singleInputtest(){
   
$sql = new MySql(Database::test, UserConnection::username, UserConnection::password); // set up your username and password for ur mysql
   
$sql = MySql::Table("accounts");
   
$sql->insert(array("FirstName"=>"Neema","LastName"=>"Lymo"));
    if(
$sql->CheckOutInsert()){
        echo
"insert successful";
        return;
    }
    echo
"insert not successful";
}


function
multipleInputtest(){
   
$sql = new MySql(Database::test, UserConnection::username, UserConnection::password); // set up your username and password for ur mysql
   
$sql = MySql::Table("accounts");
   
$data = TestData(100);
   
$sql->insert($data);
    if(
$sql->CheckOutInsert()){
        echo
"insert successful";
        return;
    }
    echo
"insert not successful";
}

function
TestData($count){
   
$names = array(
   
"John",
   
"Jane",
   
"Michael",
   
"Emily",
   
"William",
   
"Olivia",
   
"James",
   
"Cuthbert",
   
"Neema",
   
"Stephanie",
   
"Deven",
   
"Danny",
   
"Marion",
   
"Ben",
   
"Sophia",
   
"Alexander",
   
"Emma",
   
"Jacob",
   
"Ava",
   
"Mia",
   
"Noah",
   
"Isabella",
   
"Ethan",
   
"Oliver",
   
"Liam",
   
"Charlotte",
   
"Amelia",
   
"Harper",
   
"Elijah",
   
"Lucas",
   
"Matthew",
   
"Abigail",
   
"Emily",
   
"Scarlett",
   
"Victoria",
   
"Daniel",
   
"Logan",
   
"Grace",
   
"David",
   
"Christopher",
   
"Henry",
   
"Aiden",
   
"Jackson"
   
// Add more names as needed
);


$people = array();

$peopleIndexed = array();

for (
$i = 0; $i < $count;) {
   
$firstName = $names[rand(0, count($names) - 1)];
   
$lastName = $names[rand(0, count($names) - 1)];
   
$index = $firstName.$lastName;
    if (!
in_array($index, $peopleIndexed)) {
       
$person = array(
           
"FirstName" => $firstName,
           
"LastName" => $lastName
       
);
       
       
$people[] = $person;
       
$peopleIndexed[] = $index;
       
$i++;
        }

}

return
$people;

}

// UnComment for Testing

//singleInputtest();
//multipleInputtest();



?>


Details

MySQL PHP Handling Class

This repository contains a PHP class for handling MySQL database connections and operations. The class provides a convenient way to connect to a MySQL server, execute queries, and perform common database operations.

Overview

The MySql class is designed to simplify the interaction with a MySQL database in PHP. It provides methods for establishing a connection to the database, executing SQL queries, and handling exceptions. The class also supports customizable error handling and provides functionality for constructing common SQL statements such as SELECT, INSERT, and DELETE.

Features

  • Database Connection: The class allows you to establish a connection to a MySQL server using the provided database, username, and password.
  • Error Handling: Custom exception handling is implemented to catch and handle SQL-related exceptions.
  • SQL Statement Construction: The class provides methods for constructing common SQL statements such as SELECT, INSERT, and DELETE based on the provided data.
  • Table Management: The class supports loading table information from the connected database and provides access to individual tables for further operations.
  • Serialization and Deserialization: Ability to save and load the database connection configuration for easy reuse.

Usage

To use the MySql class, follow these steps:

  1. Include the `MySql.php` file in your PHP project.
  2. Create an instance of the `MySql` class by passing the database, username, and password as parameters to the constructor.
  3. Use the provided methods to perform database operations such as executing queries and constructing SQL statements.
  4. Handle any exceptions thrown by the class for error reporting and debugging purposes.
  5. Customize the error handling and exception messages as per your application's requirements.

Please refer to the code documentation and examples for detailed usage instructions and API references.

Acknowledgments

This project was developed to simplify MySQL database handling in PHP applications. We would like to acknowledge the developers and contributors of PHP and MySQL for their excellent work in creating these powerful technologies.

Contributing

Contributions to this project are welcome! If you have any suggestions, bug reports, or feature requests, please open an issue or submit a pull request. Let's make this MySQL PHP handling class even better together.

License

This project is licensed under the MIT License. Feel free to use, modify, and distribute the code in accordance with the terms of the license.


Screenshots  
  • simple select
  Files folder image Files  
File Role Description
Plain text file AbstractClasses.php Class Class source
Accessible without login Plain text file headers.php Aux. Auxiliary script
Plain text file IException.php Class Class source
Accessible without login Plain text file insertTest.php Example Example script
Accessible without login Plain text file LICENSE Lic. License text
Plain text file MySql.php Class Class source
Plain text file MySqlKeyword.php Class Class source
Accessible without login Plain text file README.md Doc. Documentation
Accessible without login Plain text file selectTest.php Example Example script
Plain text file Table.php Class Class source
Plain text file TableColumn.php Class Class source
Plain text file Utility.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 92%
Total:58
This week:0
All time:10,472
This week:106Up