# The PHP/MySQL Magic Spells File
# by Elf Qrin - http://labs.geody.com/
# v1.3.2 r18jan2007 fr-----2005
# "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[
$db_host=''; $db_user=''; $db_pwd='';
$db_name='';
$mysql=mysql_connect($db_host,$db_user,$db_pwd);
/* */
# 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="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 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=mysql_db_query($db_name,$qdb,$mysql);
$dbn=mysql_num_rows($dbo); # count rows affected by the operation
# show two fields, retrieved as objects
while ($row=mysql_fetch_object($dbo)) {
echo $row->field1.', '.$row->field2; # show selected values
}
# show all fields, retrieved as array
$cp=1;
while (($row=mysql_fetch_array($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++;
}
mysql_close($mysql);
]]>
Page issued on 31-Jul-2010 08:42 GMT
Copyright (c) 2010 Geody - Legal notices: copyright, privacy policy, disclaimer
