PHP, SQL & MySQL howto

Rename a table:

RENAME TABLE table_name TO new_table_name;

Add new column at the end of the table:

ALTER TABLE table_name ADD column_name datatype;

For example: ALTER TABLE contacts ADD email VARCHAR(60);

Insert a new column between two columns in a table:

ALTER TABLE table_name ADD column_name datatype AFTER column_name;

For example: ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

Delete a column:

ALTER TABLE table_name DROP COLUMN column_name;

To change the data type of a column in a table, use the following syntax (for MySQL):

ALTER TABLE table_name MODIFY column_name datatype;

Add a column that uses the datatype DOUBLE:

ALTER TABLE table_name ADD column_name DOUBLE;

Add a column that uses the datatype DATETIME:

ALTER TABLE table_name ADD added_date DATETIME;

There is no way to insert current date or time in MySQL while creating tables.U have to insert the values urself.
ie. this is wrong: ALTER TABLE table_name ADD added_date DATETIME DEFAULT NOW()

Rename a column:

ALTER TABLE table_name CHANGE old_column_name new_column_name varchar (10);

SQL datatypes:
W3Schools - SQL Data Types
zetcode.com - MySQL data types

SQL:
ALTER TABLE table_name ADD new_column VARCHAR(60);
- creates new target column
UPDATE TABLE table_name SET new_column = old_column;
- copies old_column to new_column

To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:
ALTER TABLE table_name ALTER COLUMN column_name datatype;

My SQL / Oracle:
ALTER TABLE table_name MODIFY column_name datatype;

UPDATE tablename SET fieldname = 'newvalue' WHERE fieldname = 'oldvalue'

So, for the default Post Notications it should look like this...

Update wp_post_notification_emails SET gets_mail = '0' WHERE gets_mail = '1'

That will disable all email addresses until it's all back up and running. Then reverse the process to enable them again.

Update wp_post_notification_emails SET gets_mail = '1' WHERE gets_mail = '0'

Example:
OLD=dog
NEW=1

The general form of the command you need to use to update a single row's column:

UPDATE my_table SET my_column='new value' WHERE something='some value';

And here's an example:

BEFORE

mysql> select aet,port from ae;
+------------+-------+
| aet        | port  |
+------------+-------+
| DCM4CHEE01 | 11112 | 
| CDRECORD   | 10104 | 
+------------+-------+
2 rows in set (0.00 sec)

MAKING THE CHANGE

mysql> update ae set port='10105' where aet='CDRECORD';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

AFTER

mysql> select aet,port from ae;
+------------+-------+
| aet        | port  |
+------------+-------+
| DCM4CHEE01 | 11112 | 
| CDRECORD   | 10105 | 
+------------+-------+
2 rows in set (0.00 sec)

For instance maybe you want to change all instances of the word “colour” (from string) with “color” (to string).

UPDATE table_name SET column_name = REPLACE(column_name, "from string", "to string") WHERE column_name LIKE "%from string%";

Use GROUP BY:

select value, count(*) from table group by value
Use HAVING to further reduce the results, e.g. only values that occur more than 3 times:

select value, count(*) from table group by value having count(*) > 3

Tizag.com - mysql aggregate functions - count()

table products:

<?php
// Make a MySQL Connection

$query = "SELECT type, COUNT(name) FROM products GROUP BY type"; 
	 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";
	echo "<br/>";
}
?>

There are 2 Clothing items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.

How to count values with MySQL queries

SELECT person,
SUM(IF(vote = "yes", 1,0)) AS `yes_votes`,
SUM(IF(vote = "no", 1,0)) AS `no_votes`,
COUNT(vote) AS `total`
FROM votes
GROUP BY person
ORDER BY yes_votes DESC