Notes

ALTER TABLE prices
ADD COLUMN inventory smallint AFTER invoice;

ALTER TABLE eqMaintenance ADD COLUMN filter2id smallint AFTER filterid

ALTER TABLE filters ADD COLUMN model varchar(75) AFTER description
ALTER TABLE filters ADD COLUMN code varchar(10) AFTER model

ALTER TABLE filters ADD COLUMN cost decimal (10,2) AFTER type_of
ALTER TABLE fertilizer_applied ADD COLUMN crop_year smallint AFTER acres

=================================


increase size:
ALTER TABLE table_name MODIFY COLUMN column_name SMALLINT;
ALTER TABLE `bible` MODIFY COLUMN `chapter` SMALLINT;
ALTER TABLE `transactions` MODIFY `description` varchar (400);
ALTER TABLE `lastbackup` MODIFY `source` varchar (500);
ALTER TABLE `fertilizer_applied` MODIFY `comments` varchar (400);

ALTER TABLE `purchases` MODIFY `description` varchar (400);
ALTER TABLE `equipment_photo` MODIFY `caption` varchar (100);


MySQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the "Customers" table:

ExampleGet your own SQL Server
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the "Email" column from the "Customers" table:

Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - MODIFY COLUMN
To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

TRUNCATE TABLE parts

When you truncate a table, the AUTO_INCREMENT counters on the table will be reset.
MySQL truncates the table by dropping and creating the table. Thus, the DELETE triggers for the table do not fire during the truncation.
Starting in MySQL 5.5, you can not truncate an InnoDB table that is referenced by a foreign key in another table.
Starting in MySQL 5.6, you can not truncate a NDB table that is referenced by a foreign key in another table.

Example

In MySQL, truncating a table is a fast way to clear out records from a table if you don't need to worry about rolling back. In most cases, MySQL handles the process of table truncation a bit differently than other databases such as Oracle or SQL Server.

Let's look at an example of how to use the TRUNCATE TABLE statement in MySQL.

For example:

TRUNCATE TABLE customers;

This example would truncate the table called customers and remove all records from that table.

It would be equivalent to the following DELETE statement in MySQL:

DELETE FROM customers;

Both of these statements would result in all data from the customers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you can't roll back the TRUNCATE TABLE statement.

Let's look at one more example where we prefix the table name with the database name.

For example:

CREATE TABLE `cash` (
`cashid` mediumint(9) NOT NULL AUTO_INCREMENT,
`amount_total` smallint(6) NOT NULL,
`amount_split` smallint(6) NOT NULL,
`caleb` smallint(6) NOT NULL,
`jarrod` smallint(6) NOT NULL,
`received_on` datetime NOT NULL,
`farm` tinyint(4) NOT NULL,
`cutting` tinyint(4) NOT NULL,
`chk_cash` tinyint(4) NOT NULL,
`bale_count` smallint(6) NOT NULL,
`comment` varchar(50) NOT NULL,
`ave_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`cashid`),
KEY `received_on` (`received_on`)
) ENGINE=MyISAM AUTO_INCREMENT=1683 DEFAULT CHARSET=latin1

CREATE TABLE `lastbackup` (
`backupid` mediumint(9) NOT NULL AUTO_INCREMENT,
`to` tinyint(4) NOT NULL,
`type` tinyint(4) NOT NULL,
`method` tinyint(4) NOT NULL,
`device` varchar(30) NOT NULL,
`table_name` varchar(30) NOT NULL,
` database_name` varchar(30) NOT NULL,
`destination` varchar(50) NOT NULL,
`received_on` datetime NOT NULL,
PRIMARY KEY (`backupid`),
KEY `received_on` (`received_on`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

---

Programmatically with PHP
You can write a PHP script to connect to your MySQL database and execute queries to generate the CREATE TABLE statements. This is useful for building custom backup tools.
The following approach involves fetching table metadata and reconstructing the SQL, or leveraging PHP's ability to execute shell commands like mysqldump for simplicity:

Executing mysqldump from PHP: This method executes the command-line utility within your PHP script, which is often the most reliable way to get a perfect SQL structure dump.

php

=?php
$db_user = "your_username";
$db_pass = "your_password";
$db_name = "your_database";
$table_name = "your_table";
$output_file = "structure_export.sql";

// Command to export only the structure (--no-data)
$command = "mysqldump -u" . $db_user . " -p" . $db_pass . " " . $db_name . " " . $table_name . " --no-data > " . $output_file;

// Execute the command
exec($command, $output, $return_var);

if ($return_var === 0) {
echo "Table structure exported successfully to " . $output_file;
} else {
echo "Error exporting table structure.";
}
?=

---

=?php
ALL TABLES
function backup_mysql_database($host, $user, $pass, $dbname, $filename) {
// Ensure the output file has a .sql extension for clarity
if (substr($filename, -4) !== '.sql') {
$filename .= '.sql';
}

// Command to execute mysqldump:
// --opt is enabled by default and is recommended for speed and size
// --single-transaction allows a consistent backup without locking tables for InnoDB tables
$command = sprintf(
'mysqldump -h%s -u%s -p%s --opt --single-transaction %s > %s',
escapeshellarg($host),
escapeshellarg($user),
escapeshellarg($pass),
escapeshellarg($dbname),
escapeshellarg($filename)
);

$output = [];
$result_code = 0;

// Execute the command
exec($command, $output, $result_code);

if ($result_code === 0) {
return "Database dump successful. File saved as: $filename";
} else {
return "Error creating database dump. Command output: " . implode("n", $output);
}
}

// Configuration variables
$DBHOST = 'localhost';
$DBUSER = 'your_username';
$DBPASS = 'your_password';
$DBNAME = 'your_database_name';
$BACKUP_FILE = 'backup_' . date('Y-m-d_H-i-s') . '.sql';

// Run the backup function
$message = backup_mysql_database($DBHOST, $DBUSER, $DBPASS, $DBNAME, $BACKUP_FILE);

echo $message;
?=

---

=?php
$command = "ls -l"; // Command to list files and directories (on Unix-like systems)
$output_lines = [];
$return_status;

// Execute the command, capture output lines into $output_lines,
// and capture the exit code into $return_status
exec($command, $output_lines, $return_status);

echo "Command Executed:";
echo "$command";

echo "Output Lines:";

print_r($output_lines);
echo "Return Status:";
echo "$return_status"; // 0 usually means success
?=

SELECT category,categories.categoryid, COUNT(ads.categoryid) AS junk FROM categories LEFT JOIN ads ON categories.categoryid=ads.categoryid GROUP BY category ORDER BY category


SELECT products.productname, soldout, restock, count( orderedproducts.productid )
FROM categories, products, orderedproducts, orders
WHERE filled = 'Y'
AND orders.orderid = orderedproducts.orderid
AND orderedproducts.productid = products.productid
AND categories.catid = products.catid
AND orders.date>='2010-04-01' AND orders.date<='2010-04-30'
GROUP BY productname


SELECT * FROM `orderedproducts INNER JOIN orders ON orders.orderid=orderedproducts.orderid WHERE orders.date>='2010-04-01' AND orders.date<='2010-04-30'

SUM

SELECT SUM(amount) AS total_amount FROM orders;