


A complete example of generating MYSQL statement class through parameters in PHP, mysql statement
This article describes the example of generating MYSQL statement class through parameters in PHP. Share it with everyone for your reference, the details are as follows:
This class can create SELECT, INSERT, UPDATE and DELETE statements by specifying table and field parameters.
This class can create WHERE conditions of SQL statements, such as LIKE query statements, using LEFT JOIN and ORDER statements
<?php /* ******************************************************************* Example file This example shows how to use the MyLibSQLGen class The example is based on the following MySQL table: CREATE TABLE customer ( id int(10) unsigned NOT NULL auto_increment, name varchar(60) NOT NULL default '', address varchar(60) NOT NULL default '', city varchar(60) NOT NULL default '', PRIMARY KEY (cust_id) ) TYPE=MyISAM; ******************************************************************* */ require_once ( " class_mylib_SQLGen-1.0.php " ); $fields = Array ( " name " , " address " , " city " ); $values = Array ( " Fadjar " , " Resultmang Raya Street " , " Jakarta " ); $tables = Array ( " customer " ); echo " <b>Result Generate Insert</b><br> " ; $object = new MyLibSQLGen(); $object -> clear_all_assign(); // to refresh all property but it no need when first time execute $object -> setFields( $fields ); $object -> setValues( $values ); $object -> setTables( $tables ); if ( ! $object -> getInsertSQL()){ echo $object -> Error; exit ;} else { $sql = $object -> Result; echo $sql . " <br> " ;} echo " <b>Result Generate Update</b><br> " ; $fields = Array ( " name " , " address " , " city " ); $values = Array ( " Fadjar " , " Resultmang Raya Street " , " Jakarta " ); $tables = Array ( " customer " ); $id = 1 ; $conditions [ 0 ][ " condition " ] = " id='$id' " ; $conditions [ 0 ][ " connection " ] = "" ; $object -> clear_all_assign(); $object -> setFields( $fields ); $object -> setValues( $values ); $object -> setTables( $tables ); $object -> setConditions( $conditions ); if ( ! $object -> getUpdateSQL()){ echo $object -> Error; exit ;} else { $sql = $object -> Result; echo $sql . " <br> " ;} echo " <b>Result Generate Delete</b><br> " ; $tables = Array ( " customer " ); $conditions [ 0 ][ " condition " ] = " id='1' " ; $conditions [ 0 ][ " connection " ] = " OR " ; $conditions [ 1 ][ " condition " ] = " id='2' " ; $conditions [ 1 ][ " connection " ] = " OR " ; $conditions [ 2 ][ " condition " ] = " id='4' " ; $conditions [ 2 ][ " connection " ] = "" ; $object -> clear_all_assign(); $object -> setTables( $tables ); $object -> setConditions( $conditions ); if ( ! $object -> getDeleteSQL()){ echo $object -> Error; exit ;} else { $sql = $object -> Result; echo $sql . " <br> " ;} echo " <b>Result Generate List</b><br> " ; $fields = Array ( " id " , " name " , " address " , " city " ); $tables = Array ( " customer " ); $id = 1 ; $conditions [ 0 ][ " condition " ] = " id='$id' " ; $conditions [ 0 ][ " connection " ] = "" ; $object -> clear_all_assign(); $object -> setFields( $fields ); $object -> setTables( $tables ); $object -> setConditions( $conditions ); if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;} else { $sql = $object -> Result; echo $sql . " <br> " ;} echo " <b>Result Generate List with search on all fields</b><br> " ; $fields = Array ( " id " , " name " , " address " , " city " ); $tables = Array ( " customer " ); $id = 1 ; $search = " Fadjar Nurswanto " ; $object -> clear_all_assign(); $object -> setFields( $fields ); $object -> setTables( $tables ); $object -> setSearch( $search ); if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;} else { $sql = $object -> Result; echo $sql . " <br> " ;} echo " <b>Result Generate List with search on some fields</b><br> " ; $fields = Array ( " id " , " name " , " address " , " city " ); $tables = Array ( " customer " ); $id = 1 ; $search = Array ( " name " => " Fadjar Nurswanto " , " address " => " Tomang Raya " ); $object -> clear_all_assign(); $object -> setFields( $fields ); $object -> setTables( $tables ); $object -> setSearch( $search ); if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;} else { $sql = $object -> Result; echo $sql . " <br> " ;} ?>
Class code:
<?php /* Created By : Fadjar Nurswanto <fajr_n@rindudendam.net> DATE : 2006-08-02 PRODUCTNAME : class MyLibSQLGen PRODUCTVERSION : 1.0.0 DESCRIPTION : class yang berfungsi untuk menggenerate SQL DENPENCIES : */ class MyLibSQLGen { var $Result ; var $Tables = Array (); var $Values = Array (); var $Fields = Array (); var $Conditions = Array (); var $Condition ; var $LeftJoin = Array (); var $Search ; var $Sort = " ASC " ; var $Order ; var $Error ; function MyLibSQLGen(){} function BuildCondition() { $funct = " BuildCondition " ; $className = get_class ( $this ); $conditions = $this -> getConditions(); if ( ! $conditions ){ $this -> dbgDone( $funct ); return true ;} if ( ! is_array ( $conditions )) { $this -> Error = " $className::$funct Variable conditions not Array " ; return ; } for ( $i = 0 ; $i < count ( $conditions ); $i ++ ) { $this -> Condition .= $conditions [ $i ][ " condition " ] . " " . $conditions [ $i ][ " connection " ] . " " ; } return true ; } function BuildLeftJoin() { $funct = " BuildLeftJoin " ; $className = get_class ( $this ); if ( ! $this -> getLeftJoin()){ $this -> Error = " $className::$funct Property LeftJoin was empty " ; return ;} $LeftJoinVars = $this -> getLeftJoin(); $hasil = false ; foreach ( $LeftJoinVars as $LeftJoinVar ) { @ $hasil .= " LEFT JOIN " . $LeftJoinVar [ " table " ]; foreach ( $LeftJoinVar [ " on " ] as $var ) { @ $condvar .= $var [ " condition " ] . " " . $var [ " connection " ] . " " ; } $hasil .= " ON ( " . $condvar . " ) " ; unset ( $condvar ); } $this -> ResultLeftJoin = $hasil ; return true ; } function BuildOrder() { $funct = " BuildOrder " ; $className = get_class ( $this ); if ( ! $this -> getOrder()){ $this -> Error = " $className::$funct Property Order was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} $Fields = $this -> getFields(); $Orders = $this -> getOrder(); if ( ereg ( " , " , $Orders )){ $Orders = explode ( " , " , $Order );} if ( ! is_array ( $Orders )){ $Orders = Array ( $Orders );} foreach ( $Orders as $Order ) { if ( ! is_numeric ( $Order )){ $this -> Error = " $className::$funct Property Order not Numeric " ; return ;} if ( $Order > count ( $this -> Fields)){ $this -> Error = " $className::$funct Max value of property Sort is " . count ( $this -> Fields); return ;} @ $xorder .= $Fields [ $Order ] . " , " ; } $this -> ResultOrder = " ORDER BY " . substr ( $xorder , 0 ,- 1 ); return true ; } function BuildSearch() { $funct = " BuildSearch " ; $className = get_class ( $this ); if ( ! $this -> getSearch()){ $this -> Error = " $className::$funct Property Search was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} $Fields = $this -> getFields(); $xvalue = $this -> getSearch(); if ( is_array ( $xvalue )) { foreach ( $Fields as $field ) { if (@ $xvalue [ $field ]) { $Values = explode ( " " , $xvalue [ $field ]); foreach ( $Values as $Value ) { @ $hasil .= $field . " LIKE '% " . $Value . " %' OR " ; } if ( $hasil ) { @ $hasil_final .= " ( " . substr ( $hasil , 0 ,- 4 ) . " ) AND " ; unset ( $hasil ); } } } $hasil = $hasil_final ; } else { foreach ( $Fields as $field ) { $Values = explode ( " " , $xvalue ); foreach ( $Values as $Value ) { @ $hasil .= $field . " LIKE '% " . $Value . " %' OR " ; } } } $this -> ResultSearch = substr ( $hasil , 0 ,- 4 ); return true ; } function clear_all_assign() { $this -> Result = null ; $this -> ResultSearch = null ; $this -> ResultLeftJoin = null ; $this -> Result = null ; $this -> Tables = Array (); $this -> Values = Array (); $this -> Fields = Array (); $this -> Conditions = Array (); $this -> Condition = null ; $this -> LeftJoin = Array (); $this -> Sort = " ASC " ; $this -> Order = null ; $this -> Search = null ; $this -> fieldSQL = null ; $this -> valueSQL = null ; $this -> partSQL = null ; $this -> Error = null ; return true ; } function CombineFieldValue( $manual = false ) { $funct = " CombineFieldsPostVar " ; $className = get_class ( $this ); $fields = $this -> getFields(); $values = $this -> getValues(); if ( ! is_array ( $fields )) { $this -> Error = " $className::$funct Variable fields not Array " ; return ; } if ( ! is_array ( $values )) { $this -> Error = " $className::$funct Variable values not Array " ; return ; } if ( count ( $fields ) != count ( $values )) { $this -> Error = " $className::$funct Count of fields and values not match " ; return ; } for ( $i = 0 ; $i < count ( $fields ); $i ++ ) { @ $this -> fieldSQL .= $fields [ $i ] . " , " ; if ( $fields [ $i ] == " pwd " || $fields [ $i ] == " password " || $fields [ $i ] == " pwd " ) { @ $this -> valueSQL .= " password(' " . $values [ $i ] . " '), " ; @ $this -> partSQL .= $fields [ $i ] . " =password(' " . $values [ $i ] . " '), " ; } else { if ( is_numeric ( $values [ $i ])) { @ $this -> valueSQL .= $values [ $i ] . " , " ; @ $this -> partSQL .= $fields [ $i ] . " = " . $values [ $i ] . " , " ; } else { @ $this -> valueSQL .= " ' " . $values [ $i ] . " ', " ; @ $this -> partSQL .= $fields [ $i ] . " =' " . $values [ $i ] . " ', " ; } } } $this -> fieldSQL = substr ( $this -> fieldSQL , 0 ,- 1 ); $this -> valueSQL = substr ( $this -> valueSQL , 0 ,- 1 ); $this -> partSQL = substr ( $this -> partSQL , 0 ,- 1 ); return true ; } function getDeleteSQL() { $funct = " getDeleteSQL " ; $className = get_class ( $this ); $Tables = $this -> getTables(); if ( ! $Tables || ! count ( $Tables )) { $this -> dbgFailed( $funct ); $this -> Error = " $className::$funct Table was empty " ; return ; } for ( $i = 0 ; $i < count ( $Tables ); $i ++ ) { @ $Table .= $Tables [ $i ] . " , " ; } $Table = substr ( $Table , 0 ,- 1 ); $sql = " DELETE FROM " . $Table ; if ( $this -> getConditions()) { if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;} $sql .= " WHERE " . $this -> getCondition(); } $this -> Result = $sql ; return true ; } function getInsertSQL() { $funct = " getInsertSQL " ; $className = get_class ( $this ); if ( ! $this -> getValues()){ $this -> Error = " $className::$funct Property Values was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property Tables was empty " ; return ;} if ( ! $this -> CombineFieldValue()){ $this -> dbgFailed( $funct ); return ;} $Tables = $this -> getTables(); $sql = " INSERT INTO " . $Tables [ 0 ] . " ( " . $this -> fieldSQL . " ) VALUES ( " . $this -> valueSQL . " ) " ; $this -> Result = $sql ; return true ; } function getUpdateSQL() { $funct = " getUpdateSQL " ; $className = get_class ( $this ); if ( ! $this -> getValues()){ $this -> Error = " $className::$funct Property Values was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property Tables was empty " ; return ;} if ( ! $this -> CombineFieldValue()){ $this -> dbgFailed( $funct ); return ;} if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;} $Tables = $this -> getTables(); $sql = " UPDATE " . $Tables [ 0 ] . " SET " . $this -> partSQL . " WHERE " . $this -> getCondition(); $this -> Result = $sql ; return true ; } function getQuerySQL() { $funct = " getQuerySQL " ; $className = get_class ( $this ); if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property Tables was empty " ; return ;} $Fields = $this -> getFields(); $Tables = $this -> getTables(); foreach ( $Fields as $Field ){@ $sql_raw .= $Field . " , " ;} foreach ( $Tables as $Table ){@ $sql_table .= $Table . " , " ;} $this -> Result = " SELECT " . substr ( $sql_raw , 0 ,- 1 ) . " FROM " . substr ( $sql_table , 0 ,- 1 ); if ( $this -> getLeftJoin()) { if ( ! $this -> BuildLeftJoins()){ $this -> dbgFailed( $funct ); return ;} $this -> Result .= " " . $this -> ResultLeftJoin; } if ( $this -> getConditions()) { if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;} $this -> Result .= " WHERE ( " . $this -> Condition . " ) " ; } if ( $this -> getSearch()) { if ( ! $this -> BuildSearch()){ $this -> dbgFailed( $funct ); return ;} if ( $this -> ResultSearch) { if ( eregi ( " WHERE " , $this -> Result)){ $this -> Result .= " AND " . $this -> ResultSearch;} else { $this -> Result .= " WHERE " . $this -> ResultSearch;} } } if ( $this -> getOrder()) { if ( ! $this -> BuildOrder()){ $this -> dbgFailed( $funct ); return ;} $this -> Result .= " " . $this -> ResultOrder; } if ( $this -> getSort()) { if (@ $this -> ResultOrder) { $this -> Result .= " " . $this -> getSort(); } } return true ; } function getCondition(){ return @ $this -> Condition;} function getConditions(){ if ( count (@ $this -> Conditions) && is_array (@ $this -> Conditions)){ return @ $this -> Conditions;}} function getFields(){ if ( count (@ $this -> Fields) && is_array (@ $this -> Fields)){ return @ $this -> Fields;}} function getLeftJoin(){ if ( count (@ $this -> LeftJoin) && is_array (@ $this -> LeftJoin)){ return @ $this -> LeftJoin;}} function getOrder(){ return @ $this -> Order;} function getSearch(){ return @ $this -> Search;} function getSort(){ return @ $this -> Sort ;} function getTables(){ if ( count (@ $this -> Tables) && is_array (@ $this -> Tables)){ return @ $this -> Tables;}} function getValues(){ if ( count (@ $this -> Values) && is_array (@ $this -> Values)){ return @ $this -> Values;}} function setCondition( $input ){ $this -> Condition = $input ;} function setConditions( $input ) { if ( is_array ( $input )){ $this -> Conditions = $input ;} else { $this -> Error = get_class ( $this ) . " ::setConditions Parameter input not array " ; return ;} } function setFields( $input ) { if ( is_array ( $input )){ $this -> Fields = $input ;} else { $this -> Error = get_class ( $this ) . " ::setFields Parameter input not array " ; return ;} } function setLeftJoin( $input ) { if ( is_array ( $input )){ $this -> LeftJoin = $input ;} else { $this -> Error = get_class ( $this ) . " ::setFields Parameter input not array " ; return ;} } function setOrder( $input ){ $this -> Order = $input ;} function setSearch( $input ){ $this -> Search = $input ;} function setSort( $input ){ $this -> Sort = $input ;} function setTables( $input ) { if ( is_array ( $input )){ $this -> Tables = $input ;} else { $this -> Error = get_class ( $this ) . " ::setTables Parameter input not array " ; return ;} } function setValues( $input ) { if ( is_array ( $input )){ $this -> Values = $input ;} else { $this -> Error = get_class ( $this ) . " ::setValues Parameter input not array " ; return ;} } } ?>
Readers who are interested in more PHP-related content can check out the special topics on this site: "Summary of PHP database operation skills based on pdo", "Summary of PHP operations and operator usage", "Summary of PHP network programming skills", "PHP basic Grammar introductory tutorial", "php office document operation skills summary (including word, excel, access, ppt)", "php date and time usage summary", "php object-oriented programming introductory tutorial", "php string (string) Usage summary", "php mysql database operation introductory tutorial" and "php common database operation skills summary"
I hope this article will be helpful to everyone in PHP programming.
Articles you may be interested in:
- Mysql class implemented by PHP based on singleton mode
- PHP encapsulated connection Mysql class and usage analysis
- a You can refer to the php Mysql class to learn and get familiar with
- Twelve common PHP MySql class free CMS systems
- PHP implements a complete example of the Model base class based on mysqli
- PHP uses Mysqli Class library method to achieve perfect paging effect
- PHP method to format MYSQL to return float type
- php method to implement Mysql simple operation class
- php method to simply operate mysql database

PHP type prompts to improve code quality and readability. 1) Scalar type tips: Since PHP7.0, basic data types are allowed to be specified in function parameters, such as int, float, etc. 2) Return type prompt: Ensure the consistency of the function return value type. 3) Union type prompt: Since PHP8.0, multiple types are allowed to be specified in function parameters or return values. 4) Nullable type prompt: Allows to include null values and handle functions that may return null values.

In PHP, use the clone keyword to create a copy of the object and customize the cloning behavior through the \_\_clone magic method. 1. Use the clone keyword to make a shallow copy, cloning the object's properties but not the object's properties. 2. The \_\_clone method can deeply copy nested objects to avoid shallow copying problems. 3. Pay attention to avoid circular references and performance problems in cloning, and optimize cloning operations to improve efficiency.

PHP is suitable for web development and content management systems, and Python is suitable for data science, machine learning and automation scripts. 1.PHP performs well in building fast and scalable websites and applications and is commonly used in CMS such as WordPress. 2. Python has performed outstandingly in the fields of data science and machine learning, with rich libraries such as NumPy and TensorFlow.

Key players in HTTP cache headers include Cache-Control, ETag, and Last-Modified. 1.Cache-Control is used to control caching policies. Example: Cache-Control:max-age=3600,public. 2. ETag verifies resource changes through unique identifiers, example: ETag: "686897696a7c876b7e". 3.Last-Modified indicates the resource's last modification time, example: Last-Modified:Wed,21Oct201507:28:00GMT.

In PHP, password_hash and password_verify functions should be used to implement secure password hashing, and MD5 or SHA1 should not be used. 1) password_hash generates a hash containing salt values to enhance security. 2) Password_verify verify password and ensure security by comparing hash values. 3) MD5 and SHA1 are vulnerable and lack salt values, and are not suitable for modern password security.

PHP is a server-side scripting language used for dynamic web development and server-side applications. 1.PHP is an interpreted language that does not require compilation and is suitable for rapid development. 2. PHP code is embedded in HTML, making it easy to develop web pages. 3. PHP processes server-side logic, generates HTML output, and supports user interaction and data processing. 4. PHP can interact with the database, process form submission, and execute server-side tasks.

PHP has shaped the network over the past few decades and will continue to play an important role in web development. 1) PHP originated in 1994 and has become the first choice for developers due to its ease of use and seamless integration with MySQL. 2) Its core functions include generating dynamic content and integrating with the database, allowing the website to be updated in real time and displayed in personalized manner. 3) The wide application and ecosystem of PHP have driven its long-term impact, but it also faces version updates and security challenges. 4) Performance improvements in recent years, such as the release of PHP7, enable it to compete with modern languages. 5) In the future, PHP needs to deal with new challenges such as containerization and microservices, but its flexibility and active community make it adaptable.

The core benefits of PHP include ease of learning, strong web development support, rich libraries and frameworks, high performance and scalability, cross-platform compatibility, and cost-effectiveness. 1) Easy to learn and use, suitable for beginners; 2) Good integration with web servers and supports multiple databases; 3) Have powerful frameworks such as Laravel; 4) High performance can be achieved through optimization; 5) Support multiple operating systems; 6) Open source to reduce development costs.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 English version
Recommended: Win version, supports code prompts!

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function