MySQL

Short Commands

Change column position

 
ALTER TABLE table_name MODIFY COLUMN column_last_name VARCHAR(40) AFTER column_first_name;

Count rows in a table

 
SELECT COUNT(*) FROM table_name;

Create a new table in the database

 
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Insert data into an existing table in the database

 
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Select data from an existing table in the database

 
SELECT * FROM table_name;

Select specific columns from an existing table in the database

 
SELECT column1, column2 FROM table_name;

Strip HTML tags

 
$content = '<h1>Lorem Ipsum</h1> <p><a href="#">Lorem ipsum dolor sit amet... </a><p>';
$content_without_tags = strip_tags($content);

Strip all HTML and PHP tags, except <p> and <a>

 
$content = '<h1>Lorem Ipsum</h1> <p><a href="#">Lorem ipsum dolor sit amet... </a><p>';
$content_without_tags = strip_tags($content, '<p><a>');

Strip HTML tags from a MySQL table with MySQL's REGEXP_REPLACE() (MySQL 8.0+)

 
SELECT REGEXP_REPLACE(column_name, '<[^>]+>', '') FROM table_name;

For MySQL versions before 8.0 (which doesn't support REGEXP_REPLACE()), use the REPLACE() function:

 
UPDATE table_a SET column_a = REPLACE(REPLACE(column_a, '<p>', ''), '</p>', ''); 
UPDATE table_a SET column_a = REPLACE(REPLACE(column_a, '<em>', ''), '</em>', ''); 
UPDATE table_a SET column_a = REPLACE(REPLACE(column_a, '<strong>', ''), '</strong>', '');

Strip all HTML and PHP tags from a MySQL table row

 
$content = $row["content"];
$content_without_tags = strip_tags($content);

Update a column to lowercase

 
UPDATE table_name SET column_name = LOWER(column_name);

Update data in an existing table in the database

 
UPDATE table_name SET column1 = value1 WHERE condition;
Comma after each value, except the last one Display records Display records in a Bootstrap Table Display records in columns Display records with Bootstrap pagination Display records with CSS styles Escape special characters in a string Highlight the search term in results Replace unicode characters by utf-8 Search autocomplete with Typeahead Search records by an option Select rows for the current day and month Short commands