PHP Classes

Advanced PHP MySQL Search Controller: Compose SQL to perform MySQL database searches

Recommend this page to a friend!
  Info   Documentation   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2023-12-27 (9 months ago) RSS 2.0 feedNot enough user ratingsTotal: 136 All time: 9,271 This week: 38Up
Version License PHP version Categories
php-search-controlle 1.6GNU General Publi...5PHP 5, Databases, Searching
Description 

Author

This class can compose SQL to perform MySQL database searches.

It takes parameters that define database table fields and field values to search.

The class generates SQL conditions used to compose SQL queries that perform the searches for records that match the criteria based on the table fields and field values defined by the parameters.

The class can combine several conditions using SQL operators like AND, OR, NAND, NOR and FIND_IN_SET.

Innovation Award
PHP Programming Innovation award nominee
May 2022
Number 7
Many PHP developers use MySQL as a database to store application information dynamically.

PHP developers often need to perform search queries to filter the database table records with field values matching given criteria. Sometimes there is the need to filter records that match multiple criteria.

SQL allows composing complex search queries to combine multiple search criteria.

MySQL has a particular function named FIND_IN_SET that allows defining a condition that can check if string values have a given number of occurrences of another string value.

This class allows composing complex MySQL search queries that combine multiple search conditions. Some conditions can use the FIND_IN_SET function to find database table records with particular values in larger text fields.

This possibility allows PHP developers to perform complex searches to find given text values inside database table field values that have text values the PHP application users want to see.

Manuel Lemos
Picture of Ujah Chigozie peter
  Performance   Level  
Name: Ujah Chigozie peter <contact>
Classes: 28 packages by
Country: Nigeria Nigeria
Age: 33
All time rank: 198810 in Nigeria Nigeria
Week rank: 10 Up1 in Nigeria Nigeria Up
Innovation award
Innovation award
Nominee: 11x

Documentation

PHPSearchController

PHPSearchController is a simple PHP class to search MySQL database, it can build SQL queries to perform MySQL database searches by taking parameters that define database table fields and field values to search.

The class generates SQL conditions used to build SQL queries to perform database searches for records that match the criteria based on the table fields and field values defined by the parameters, It then combines several conditions using SQL operators such as AND, OR, NAND, LIKE NOR & FIND_IN_SET and return the appropriate queries for the search.

Installation

Installation is super-easy via Composer:

composer require peterujah/php-search-controller

Usages

Initialize the class with your preferred search method the default method is SearchController::OR.

use Peterujah\NanoBlock\SearchController;
$search = new SearchController();

Set your preferred search operator the default is SearchController::END_WITH_QUERY.

$search->setOperators(SearchController::HAVE_ANY_QUERY);

To perform a database search, you can just build your search query like the one below.

$searchQuery = "PHP Code";
$search->setQuery($searchQuery)->split();
$search->setParameter(array(
    'code_title', 
    'code_description', 
    'code_info'
));
//var_export($search->getQuery());

To search by tag using MySQL FIND_IN_SET, build a query like the example below.

$searchQuery = "PHP Code";
$search->setQuery($searchQuery)->split();
$search->setTags("code_tags");
//var_export($search->getQuery());

Set the initial query and pass the search query to your MySQL connection

$search->setIniQuery("SELECT * FROM code WHERE id = 1323");
$db->conn()->prepare($search->getQuery());
$db->conn()->execute();		
$result = $db->conn()->getAll();
$db->conn()->free();

OR build it with other sql queries like the below in your MySQL connection

$db->conn()->prepare("
    SELECT * FROM code 
    {$search->getQuery()}
    AND id = 1323
");
$db->conn()->execute();		
$result = $db->conn()->getAll();
$db->conn()->free();

Other Methods

Returns the computed sql search queries by checking if the initial query was specified or not to determine which start clause is needed.

$search->getQuery()

Set your search keyword

$search->setQuery("Foo Bar")

Split search keyword Foo Bar into Foo, Bar as separate search terms

$search->split()

Mapping your database column keys to perform a search on

$search->setParameter(array)

Set the initial SQL query before appending the search after your query string

$search->setIniQuery('SELECT * FROM ...')

Reference

Specify search operator $search->setOperators(SearchController::HAVE_ANY_QUERY)

| Search Operators | Description | |--------------------------|-----------------------------------------------------------------------------------| | START_WITH_QUERY | Finds any values that start with "query" | | END_WITH_QUERY | Finds any values that end with "query" | | HAVE_ANY_QUERY | Finds any values that have "query" in any position | | HAVE_SECOND_QUERY | Finds any values that have "query" in the second position | | START_WITH_QUERY_2LENGTH | Finds any values that start with "query" and are at least 2 characters in length | | START_WITH_QUERY_3LENGTH | Finds any values that start with "query" and are at least 3 characters in length | | START_END_WITH_QUERY | Finds any values that start with "query" and ends with "query" |

Initialize search class with a method new SearchController(SearchController::OR)

| Search Methods | Description | |------------------------|-------------------------------------------------------------------------------------| | OR | Retrieve result with any one of search query | | AND | Retrieve result with the exact of search quer | | NAND | Retrieve result without the exact search query | | NOR | Retrieve result without any on of the search query |


  Files folder image Files (5)  
File Role Description
Files folder imagesrc (1 file)
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file package.json Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (5)  /  src  
File Role Description
  Plain text file SearchController.php Class Class source

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:136
This week:0
All time:9,271
This week:38Up