Mysql

we can execute a query that will list all tables in a specific database along with the disk space (size) of each. We can list all the tables along with each table size (disk space) by using a query. SELECT table_name AS Table, ROUND(((data_length + index_length) / 1024 / 1024), 2) Size (MB) FROM information_schema.TABLES WHERE table_schema = “$Database_Name”; Query to get list the size of every table in every database, with of the order of size: SELECT table_schema as Da...

Get a list of user who are inside the circle or square of a given latitude and longitude with given distance or radius. For example my input Lat= 75.1545 and Long = 64.7643 and distance = 10 miles. I would like to get the list of users who are inside 10 miles distance from the point 75.1545 and 64.7643. Now its possible to solve this by single Mysql query. So we need  a user  table with user name, latitude and longitude of the users address. The Mysql query that will find the closest 50 l...

How to get second highest or nth maximum salary of an Employee table is one of the most frequently asked Mysql/SQL interview question. PHP Mysql Query Suppose we have the following simple database table called Employee that has 3 columns named Employee ID,Employee Name and Salary:  Employee ########################################## Employee_ID —– Employee_Name ——- Salary 1: —————– Avanish——— ——- 1000 2: —————– Abhishek——- ——- 1500 3:———...

“How to Export MySQL data to Excel in PHP ?” This script will used to export data into .xls format from mysql database.You need to just copy this code and update database connection details. Below code will export mysql data into excel (.xls format) with every column name and value from your database . Export MySQL data to Excel in PHP <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $conn = mysql_connect($dbhost, $dbus...

How can I find non-ASCII characters in MySQL?   Query to find data as  “ASCII”, SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]'; 1 SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]'; Query to find the “Non ASCII ” SELECT * FROM my_table WHERE NOT HEX(my_column) REGEXP '^([0-7][0-9A-F])*$' 1 ...

If you looking for “How to get the number of rows about to get deleted.Please try small tricks to avoid unwanted data deletion. You can use SELECT  to get the number of rows about to get deleted. PHP Mysql Query By this query we check  the number of the deleted row before running the delete query to avoid the unwanted records deletion. For example yours delete query is: Table Name :employee Delete Query : “DELETE FROM employee WHERE employee_country=’India'” Now you can check total...

How to manage international languages in MySQL database using php ? If you want to store multiple languages like Arabic, French, Hindi or Urdu to your MySQL database, default settings is not valid to do that to insert all languages in database. If you insert data in your database and database is not configured as I am going to show you in this tutorial then your data will be look like special characters निम and your data will lost .   So now i am going to tell you how to confi...

Below Mysql query is very useful to Get list of column name from table with its datatype. You can get list of all the tables name and all the fields count with help of this query. PHP Mysql Query In below query just change <–YoursDatabaseName–> to your database and <–YoursTableName–> to your table name where you just want Field values of DESCRIBE statement //Get total number of TABLES IN a DATABASE SELECT COUNT(*) AS totalTables FROM information_schem...