# The PHP-MySQL/MySQLi/PostgreSQL/SQLite Magic Spells File
# by Elf Qrin - https://labs.geody.com/
# v3.6 r2020-11-12 fr2016-09-29 (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
<![CDATA[
$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_affected_rows($dbx,$dbxcon,$dbo) {
$dbx=strtolower(trim($dbx));
$r=false;
switch ($dbx) {
case 'mysql':
$r=mysql_affected_rows();
break;
case 'mysqli':
$r=mysqli_affected_rows($dbxcon);
break;
case 'postgresql':
$r=pg_affected_rows($dbo);
break;
case 'sqlite':
$r=sqlite_changes($dbxcon);
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"; # Count all records matching a condition and stores the number into the given alias variable
$qdb="SELECT field1, COUNT(*) as count FROM `table` GROUP BY field1 ORDER BY count DESC;"; // Count occurrences of values in the column field1 in table
$qdb="SELECT field1, ROUND ( ( LENGTH(field1) - LENGTH( REPLACE ( field1, 'STRING', '') ) ) / LENGTH('STRING') ) AS count FROM `table`"; // Count occurrences of STRING in every cell of field1 in table
$qdb="SELECT item,SUM(amount) FROM sales GROUP BY item"; # Group records with the identical specified field
$qdb="SELECT item,SUM(amount) FROM sales GROUP BY item HAVING SUM(amount)>value"; # Group 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 selected by the operation
$dbna=dbx_affected_rows($dbx,$dbxcon,$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 -no-data -u MYSQLUSER -pPASSWORD DATABASE > MYSQLDUMP.sql
Backup a database to a MySQL dump file (data only):
$ mysqldump -c --no-create-db --no-create-info -u MYSQLUSER -pPASSWORD DATABASE > MYSQLDUMP.sql
Backup a database to a MySQL dump file (structure and data):
$ mysqldump -c --add-drop-database --add-drop-table --add-locks -u MYSQLUSER -pPASSWORD DATABASE > MYSQLDUMP.sql
Back up a MySQL database into a GZIP file:
$ mysqldump -c --add-drop-database --add-drop-table --add-locks -u MYSQLUSER -pPASSWORD DATABASE | gzip -9v > MYSQLDUMP_`date +%Y%m%d`.sql.gz
Restore a database from a MySQL dump file:
$ mysql -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
*/
]]>
Please DONATE to support the development of Free and Open Source Software (PayPal, Credit Card, Bitcoin, Ether)
Page issued on 25-Apr-2024 10:02 GMT
Copyright (c) 2024 Geody - Legal notices: copyright, privacy policy, disclaimer
