Geody Labs

# The PHP-MySQL/MySQLi/PostgreSQL/SQLite Magic Spells File
# by Elf Qrin - http://labs.geody.com/
# v3.3 r21dec2016 fr29sep2016 (PHP-MySQL fr2005)


# "A script kiddie is someone who thinks of code as magical incantations and asks only 'what do I need to type to make this happen?'" -- anonymous

$dbx="mysqli"; # MySQL, MySQLi, PostgreSQL $db_host=''; $db_user=''; $db_pwd=''; $db_name=''; # Functions function explodex($s,$nx='.',$ord=0,$mod=false) { $rt=array(false,false); switch ($ord) { default: case 0: # truncate the string keeping characters up to the last X matching character (excluded: will be removed) $r=strrpos($s,$nx); if ($r!==false) {$rt[0]=substr($s,0,$r);} # truncate the string keeping characters from the last X matching character (excluded: will be removed) $rn=strlen($s); $r=strrpos($s,$nx); if ($r!==false && $r<$rn) {$rt[1]=substr($s,$r+1,$rn);} break; case 1: # truncate the string keeping characters up to the first X matching character (excluded: will be removed) $r=strpos($s,$nx,0); if ($r!==false) {$rt[0]=substr($s,0,$r);} # truncate the string keeping characters from the first X matching character (excluded: will be removed) $rn=strlen($s); $r=strpos($s,$nx,0); if ($r!==false && $r<$rn) {$rt[1]=substr($s,$r+1,$rn);} break; } if ($mod && $rt[0]===false && $rt[1]===false) {$rt[0]=$s;} return $rt; } # DB Functions # Requires function explodex() function dbx_connect($dbx,$db_host,$db_user,$db_pwd,$db_name='') { $dbx=strtolower(trim($dbx)); $r=false; switch ($dbx) { case 'mysql': $r=mysql_connect($db_host,$db_user,$db_pwd); break; case 'mysqli': $db_host_1=explodex($db_host,':',0,true); if ($db_host_1[1]!==false) {$r=mysqli_connect($db_host,$db_user,$db_pwd,$db_name,$db_host_1[1]);} else {$r=mysqli_connect($db_host,$db_user,$db_pwd,$db_name);} break; case 'postgresql': # $r=pg_connect($db_host1, $db_port, , , $db_name); # deprecated $db_host_1=explodex($db_host,':',0,true); if ($db_host_1[1]!==false) {$db_portz=' port='.$db_host_1[1].' ';} else {$db_portz=' ';} $r=pg_connect("host=".$db_host_1[0].$db_portz."dbname=".$db_name." user=".$db_user." password=".$db_pwd); break; case 'sqlite': $r=sqlite_open($db_host); # $db_host contains the file name of the SQLite Database break; } return $r; } function dbx_connect_pt($dbx,$db_host,$db_port=false,$db_user,$db_pwd,$db_name='') { $dbx=strtolower(trim($dbx)); if ($db_port!==false) { $r=dbx_connect($dbx,$db_host.':'.$db_port,$db_user,$db_pwd,$db_name); } else { $r=dbx_connect($dbx,$db_host,$db_user,$db_pwd,$db_name); } return $r; } function dbx_close($dbx,$dbxcon) { $dbx=strtolower(trim($dbx)); switch ($dbx) { case 'mysql': mysql_close($dbxcon); break; case 'mysqli': mysqli_close($dbxcon); break; case 'postgresql': pg_close($dbxcon); break; case 'sqlite': sqlite_close($dbxcon); break; } } function dbx_query($dbx,$dbxcon,$dbq,$db_name='') { $dbx=strtolower(trim($dbx)); $r=false; switch ($dbx) { case 'mysql': $r=mysql_db_query($db_name,$dbq,$dbxcon); break; case 'mysqli': $r=mysqli_query($dbxcon,$dbq); break; case 'postgresql': # $dbxcon=pg_connect("dbname=".$db_name); $r=pg_query($dbxcon,$dbq); break; case 'sqlite': $r=sqlite_query($dbxcon,$dbq); break; } return $r; } function dbx_num_rows($dbx,$dbo) { $dbx=strtolower(trim($dbx)); $r=false; switch ($dbx) { case 'mysql': $r=mysql_num_rows($dbo); break; case 'mysqli': $r=mysqli_num_rows($dbo); break; case 'postgresql': $r=pg_num_rows($dbo); break; case 'sqlite': $r=sqlite_num_rows($dbo); break; } return $r; } function dbx_fetch_array($dbx,$dbo) { $dbx=strtolower(trim($dbx)); $r=false; switch ($dbx) { case 'mysql': $r=mysql_fetch_array($dbo); break; case 'mysqli': $r=mysqli_fetch_array($dbo); break; case 'postgresql': $r=pg_fetch_array($dbo); break; case 'sqlite': $r=sqlite_fetch_array($dbo); break; } return $r; } function dbx_fetch_object($dbx,$dbo) { $dbx=strtolower(trim($dbx)); $r=false; switch ($dbx) { case 'mysql': $r=mysql_fetch_object($dbo); break; case 'mysqli': $r=mysqli_fetch_object($dbo); break; case 'postgresql': $r=pg_fetch_object($dbo); break; case 'sqlite': $r=sqlite_fetch_object($dbo); break; } return $r; } function dbx_last_error($dbx,$dbxcon) { $dbx=strtolower(trim($dbx)); switch ($dbx) { case 'mysql': $r=mysql_error($dbxcon); break; case 'mysqli': $r=mysqli_error($dbxcon); break; case 'postgresql': $r=pg_last_error($dbxcon); break; case 'sqlite': $r=sqlite_last_error($dbxcon); break; } return $r; } function dbx_server_info($dbx,$dbxcon) { $dbx=strtolower(trim($dbx)); switch ($dbx) { case 'mysql': $r=mysql_get_server_info($dbxcon); break; case 'mysqli': $r=mysqli_get_server_info($dbxcon); break; case 'postgresql': $r=pg_version($dbxcon); break; case 'sqlite': $r=sqlite_libversion($dbxcon); break; } return $r; } $dbxcon=dbx_connect($dbx,$db_host,$db_user,$db_pwd,$db_name); /* */ # Data Operations: # Note: before a massive insertion or update is better to lock tables to prevent access during the update process: use "LOCK table WRITE" to lock a specific table or "LOCK TABLES WRITE" to lock them all, use "UNLOCK table" or "UNLOCK TABLES" to unlock locked tables when the update is complete. # Retrieve records (Selection) $qdb="SELECT * FROM table"; # Select all fields from all records in table $qdb="SELECT * FROM table WHERE 1=1"; # Select all fields from all records in table (in case you have a preformatted query that already contains WHERE, expecting thus a condition) $qdb="SELECT field1,field2 FROM table"; # Select 2 fields from all records in table $qdb="SELECT * FROM table WHERE test = 1"; # Select all records matching a condition (operators for the condition: numbers: "<, >, >=, <=, =, !=, LIKE", text: "=, !=, LIKE" ; boolean operators: AND (&&) OR (||) NOT (~) ) $qdb="SELECT * FROM table WHERE word LIKE 'test%'"; # Select all records where the field `word` contains a string of text beginning with 'test' $qdb="SELECT * FROM table WHERE fruit IN ('apple','orange')"; # Select all records where the field `fruit` contains 'apple' or 'orange' $qdb="SELECT * FROM table WHERE test = 1 LIMIT 10"; # Select all records matching a condition and sort them and limit the result to the first 10 matching records (head) $qdb="SELECT * FROM table WHERE test = 1 LIMIT 10,20"; # Select all records matching a condition and sort them and limit the result in the range from the 10th to the next 20 matching records (that is, up to the 30th matching record) (inclusive) $qdb="SELECT * FROM table WHERE test = 1 LIMIT 5000,18446744073709551615"; # Select all records matching a condition and sort them and limit the result in the range from the 5000th to the last matching record (inclusive) (tail) (note: 18'446'744'073'709'551'615 is the maximum large integer possible) $qdb="SELECT * FROM table WHERE test = 1 ORDER BY field1 ASC,field2 DESC"; # Select all records matching a condition and sort them $qdb="SELECT * FROM table WHERE test = 1 ORDER BY RAND(now()) LIMIT 1"; # Extract 1 random record matching a condition $qdb="SELECT MIN(v) AS mn FROM table"; # Select the minimum value of field v in the given table $qdb="SELECT MAX(v) AS mx FROM table"; # Select the maximum value of field v in the given table $qdb="SELECT MIN(v) AS mn FROM table WHERE test = 1"; # Select the minimum value of field v in the given table among the records matching the condition $qdb="SELECT MAX(v) AS mx FROM table WHERE test = 1"; # Select the maximum value of field v in the given table among the records matching the condition $qdb="SELECT COUNT(*) AS c FROM table WHERE test = 1"; # Counts all records matching a condition and stores the number into the given alias variable $qdb="SELECT item,SUM(amount) FROM sales GROUP BY item"; # Groups records with the identical specified field $qdb="SELECT item,SUM(amount) FROM sales GROUP BY item HAVING SUM(amount)>value"; # Groups records with the identical specified field, selecting only the ones matching the condition $qdb="(SELECT * FROM table WHERE test1 = 1 AND test2 IN ('A','P')) UNION (SELECT * FROM table WHERE test1 = 1 AND test2 NOT IN ('A','P')) ORDER BY field1,field2 LIMIT 100"; # Unite the results of queries with shared ordering and limit $qdb="(SELECT * FROM table WHERE test1 = 1 AND test2 IN ('A','P') ORDER BY field1,field2 LIMIT 100) UNION (SELECT * FROM table WHERE test1 = 3 OR (test2 NOT IN ('A','P')) ORDER BY field3 LIMIT 50)"; # Unite the results of queries with independent ordering and limit # Insert a new record $qdb="INSERT INTO table (field1,field2) VALUES ('value1','value2')"; # Update records matching a condition $qdb="UPDATE table SET field1 = 'value1', field2 = 'value2' WHERE field = '$fieldidupd'"; # Increment the value of a field $qdb="UPDATE table SET field = field + 1 WHERE field = '$fieldidupd'"; # Delete records matching a condition $qdb="DELETE FROM table WHERE field = '$fieldiddel'"; # Structure Operations: $qdb="CREATE DATABASE `dbname`"; # Create a new database $qdb="ALTER DATABASE `dbname` DEFAULT CHARACTER SET latin1"; # Set a default character set (latin1) for the database $qdb=" CREATE TABLE `table` ( `id` BIGINT(20) NOT NULL auto_increment, `field1` CHAR(2) NOT NULL, `field2` VARCHAR(8) NOT NULL, PRIMARY KEY (`id`), KEY (`field1`) ) CHARACTER SET = latin1; "; # Creates a new table with three fields, one primary index, and another index for one field $qdb="ALTER TABLE table ADD PRIMARY KEY ( field1 ) ;" # Set a Primary Key for the table $qdb="ALTER TABLE table DROP PRIMARY KEY , ADD PRIMARY KEY ( field1 ) ;" # Change a Primary Key for the table, deleting the existing one before to set the new one $qdb="ALTER TABLE table DROP INDEX field ;"; # Drop an index for the given field $qdb="ALTER TABLE table ADD INDEX (field) ;"; # Create a new index for the given field $qdb="ALTER TABLE table ADD UNIQUE (field1) ;"; # Create a new field and declare it as "unique" (can't contain more than an instance of the same value) $qdb="SHOW INDEX FROM table ;"; # Show indexes of the given table $qdb="ALTER TABLE table ADD field1b VARCHAR( 8 ) NOT NULL default '0' ;" # Add a new field to the table $qdb="ALTER TABLE table ADD field1b VARCHAR( 8 ) NOT NULL default '0' AFTER field1 ;" # Add a new field to the table placing it after the specified field $qdb="ALTER TABLE table ADD field1b VARCHAR( 8 ) NOT NULL default '0' AFTER field1, ADD INDEX field1b ;" # Add a new field to the table placing it after the specified field and create an index for it $qdb="ALTER TABLE table CHANGE field1 field1 TINYINT( 4 ) NOT NULL default '0';" # Change the properties of a field $qdb="ALTER TABLE table CHANGE field1 fielda TINYINT( 4 ) NOT NULL default '0';" # Change the properties of a field and rename it $qdb="ALTER TABLE table DROP field ;"; # Delete a field $qdb="TRUNCATE TABLE `table`;"; # Empty a table $qdb="DROP TABLE `table`;"; # Delete a table $qdb="ALTER TABLE `table` TYPE = MYISAM;" # Change the engine type (to MyISAM in this example) $qdb="REPAIR TABLE `table`;" # Attempt to repair a broken table $qdb="OPTIMIZE TABLE `table`;" # Optimize a table (defragment and rebuild indexes) $qdb="DROP DATABASE `dbname`;"; # Delete a database # MySQL user privileges # Remove ''@'localhost' (any user from localhost with no password) $qdb=" REVOKE ALL PRIVILEGES ON * . * FROM ''@ 'localhost'; DELETE FROM `user` WHERE CONVERT( User USING utf8 ) = CONVERT( '' USING utf8 ) AND CONVERT( Host USING utf8 ) = CONVERT( 'localhost' USING utf8 ) ; "; # Remove 'root'@'%' (root user from any server with no password) $qdb=" REVOKE ALL PRIVILEGES ON * . * FROM 'root'@ '%'; DELETE FROM `user` WHERE CONVERT( User USING utf8 ) = CONVERT( 'root' USING utf8 ) AND CONVERT( Host USING utf8 ) = CONVERT( '%' USING utf8 ) ; "; # Create a new MySQL user $qdb=" insert into user (Host, User, Password, Select_priv) values ('localhost', 'USERNAME', password('USER_PASSWORD'), 'N'); "; # Create a new MySQL user who can read (but not modify) all databases $qdb=" insert into user (Host, User, Password, Select_priv) values ('localhost', 'USERNAME', password('USER_PASSWORD'), 'Y'); "; # Create a new MySQL user with root-like privileges $qdb=" insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv) values ('localhost', 'USERNAME', password('USER_PASSWORD'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); "; # Assign a database to a MySQL user $qdb=" insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv) values ('localhost', 'USER_DATABASE', 'USER_NAME', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); "; /* */ $qdb=""; // echo '<p><b>['.$qdb.']</b><p>'; // die(); $dbo=dbx_query($dbx,$dbxcon,$qdb,$db_name); $dbn=dbx_num_rows($dbx,$dbo); # count rows affected by the operation # show two fields, retrieved as objects while ($row=dbx_fetch_object($dbx,$dbo)) { echo $row->field1.', '.$row->field2; # show selected values } # show all fields, retrieved as array $cp=1; while ($row=dbx_fetch_array($dbx,$dbo)) { echo '# '.$cp.'. '; $rlen=count($row); for ($i=0;$i<=$rlen-1;$i++) { // echo ''; echo '"'.addslashes($row[$i]).'"'; if ($i<$rlen) {echo ', ';} } echo '<br />'; $cp++; } dbx_close($dbx,$dbxcon); /* # MySQL line commands Show MySQL version: $ mysql -V Set MySQL root password: $ mysql mysql> SET PASSWORD FOR root@localhost = PASSWORD('newpassword'); Create a new mysql user and assign a database to him $ mysql -u root -pROOT_PASSWORD mysql mysql> insert into user (Host, User, Password, Select_priv) values ('localhost', 'USERNAME', password('USER_PASSWORD'), 'N'); Query OK, 1 row affected (0.00 sec) mysql> insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv) values ('localhost', 'USER_DATABASE', 'USER_NAME', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); Query OK, 1 row affected (0.00 sec) mysql> quit $ mysqladmin -u root -pROOT_PASSWORD reload The user can access the assigned database: $ mysql -u USERNAME -pUSER_PASSWORD USER_DATABASE Remove an user: mysql> drop user USERNAME Or mysql> delete from mysql.user where User='USERNAME' and Host='HOSTNAME'; mysql> revoke all privileges on *.* from USERNAME@HOSTNAME; mysql> revoke grant option on *.* from USERNAME@HOSTNAME; mysql> flush privileges; Show all MySQL users: mysql> select user, host from mysql.user; # show only user name and related host name for every user mysql> select * from mysql.user; # show detailed information for every user Show all databases: mysql> show databases; Show all tables in the current database: mysql> show tables; mysql> show table status; # provide information about every table Show all fields (coloumns) of a table: mysql> show fields from TABLE; # same as describe TABLE; mysql> show fields from TABLE from DB; # show fields of a table from another database mysql> show full fields from TABLE; # also lists privileges available to the current user Show all indexes (keys) of a table: mysql> show index from TABLE; mysql> show index from TABLE from DB; # show indexes of a table from another database Create an index (key) for a field (coloumn): mysql> alter table TABLE add index (`FIELD`) ; Count all records within a table: mysql> select count(*) from TABLE; Select a database: mysql> use DATABASE; Delete a database: mysql> drop database if exists DATABASE; Delete a table: mysql> drop table if exists TABLE; Delete a field: mysql> alter table TABLE drop FIELD; Delete an index: mysql> drop index INDEX on TABLE; Empty a table without deleting it: mysql> truncate table TABLE; Change engine type: mysql> alter table TABLE type = MYISAM; # change the engine tipe of TABLE to MyISAM Analyzes indexes of a table: # for MyISAM and BDB tables only mysql> analyze table TABLE; Check a table for errors: mysql> check table TABLE; Attempt to repair a corrupted table mysql> repair table TABLE; Optimize a table (defragment and rebuild indexes): mysql> optimize table TABLE; Show MySQL server status: mysql> show status; Backup a database to a MySQL dump file (structure only): $ mysqldump -d -h localhost -u MYSQLUSER -pPASSWORD DATABASE > MYSQLDUMP.sql Backup a database to a MySQL dump file (structure and data): $ mysqldump -h localhost -u MYSQLUSER -pPASSWORD -c --add-drop-table --add-locks --all --quick --lock-tables DATABASE > MYSQLDUMP.sql Back up a MySQL database into a GZIP file: $ mysqldump -h localhost -u MYSQLUSER -pPASSWORD -c --add-drop-table --add-locks --all --quick --lock-tables DATABASE | gzip -9v > MYSQLDUMP_`date +%Y%m%d`.sql.gz Restore a database from a MySQL dump file: $ mysql -h localhost -u MYSQLUSER -pPASSWORD DATABASE < MYSQLDUMP.sql Or, from the MySQL console: mysql> use DATABASE; source MYSQLDUMP.sql; Check running MySQL processes: $ mysqladmin -u root -pROOT_PASSWORD processlist Or, from the MySQL console: mysql> show processlist; Kill a MySQL process: $ mysqladmin -u root -pROOT_PASSWORD kill ID Or, from the MySQL console: mysql> kill ID; Quit MySQL console: mysql> quit To restart MySQL: $ /etc/init.d/mysql restart Run a MySQL file and store output into another file: $ mysql -u USER --password=PASSWORD DATABASE_NAME < SOURCE.sql > DESTINATION.txt */






Page issued on 27-Feb-2017 11:40 GMT
Copyright (c) 2017 Geody - Legal notices: copyright, privacy policy, disclaimer