MySql query to get tables size, rows

Most of the time we need information about our tables in a DB, so can check which table is getting bigger in size, or bigger in number of rows or records.

So here is a simple query to get all these information

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "databasename"

Just change “databasename” with your DB name.

If you want to get information just for a table, you can specify your table in query just like this

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "databasename" AND table_name = "yourtablenamehere"

Replace “yourtalbenamehere” with your table name.

There can be a satuation where you want information of more than a one table so query will be

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "databasename" AND table_name IN ('table_1', 'table_2', 'table_3');

 

 

MySql Query to Remove Tab, New Line, White Space from Start/End of a Field

The simplest query is

UPDATE Tbl_Name set field_name = TRIM(Replace(Replace(Replace(field_name,'\t',''),'\n',''),'\r',''));

PHP Check if Already a Database(MySql) Connection exists

If you want to check on a page there is already a mysql connection exists, and if not connect to database.
You must have to pass connection string. Otherwise “mysql_ping” return wrong value.

$mysqlcon =  mysql_ping($connection_string) ? 'true' : 'false';

if(!$mysqlcon)
    mysql_connect(host,user,pass);

No thanks, as i am great while helping others.

MySql delete duplicate recoreds

Let suppose we want to delete duplicate emails in a table

(delete recored which has same email address and have greater id)

Delete from tblname t1, tblname t2 where t1.id > t2.id and t1.email = t2.email

if you want to delete first record and want to keep 2nd one.
(delete record which has same email and has lower id)

Delete from tblname t1, tblname t2 where t1.id < t2.id and t1.email = t2.email


Mysql query to get data last week, next week, last year, next year, 30 days

To get data of yesterday (we use php function )
1. “select * from table where date = “.date(“Y-m-d”, strtotime(“-1 day”))
OR
2. “select * from table where date = NOW()-INTERVAL 1 DAY”

——————————————————————
To get data of tomorrow

1. “select * from table where date = “.date(“Y-m-d”, strtotime(“+1 day”))
OR
2. “select * from table where date = NOW()+INTERVAL 1 DAY”

——————————————————————
To get data of Last Week

“select * from tbl where date between NOW() – INTERVAL DAYOFWEEK(NOW())+6 DAY AND NOW() – INTERVAL DAYOFWEEK(NOW())-1 DAY”

Mysql query to get data of last month

You have to select date between the “1st of last month” and the “last of last month”

“select * from table where date BETWEEN date_format(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01’) AND last_day(NOW() – INTERVAL 1 MONTH)”

PHP Mysql query to get last 30 days records

This query will get recoreds of last 30 days

SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

Where DATE_SUB Subtract a time value (interval) from a date.

Mysql Query to get Data of Last week

Mysql query

SELECT id FROM tbl
WHERE date >= curdate() – INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() – INTERVAL DAYOFWEEK(curdate())-1 DAY

or

select id from tbl
where date between date_sub(now(),INTERVAL 1 WEEK) and now();

PHP Mysql query to get ‘tomorrow’, ‘yesterday’ records

Query for Yesterday

Using PHP get yesterday date like this
$date = date("Y-m-d", strtotime("-1 day"))
select * from table where date = ‘$date’

Query for Tomorrow

Using PHP get tomrrow date like this
$date = date("Y-m-d", strtotime("+1 day"))
select * from table where date = ‘$date’