You should have Database name, Database Username, Database Password and command line access to your server Export database to local file system, for example /var/www/html
Category: MySQL
MYSQL – Useful commands you must know – Part-1
To know the current MYSQL version: SELECT version(); To format and formulate a float value: FORMAT(SUM(amount) * 0.6, 2) The above line sums up entire amount value, cut it down to 60% of total value and returns value precised up…
MySQL ‘show tables’: How to list tables in a MySQL database?
This is very commonly asked question: How do I show or list all the tables in a MySQL (or MariaDB) database using mysql command line tool? In brief, to show the tables in a MySQL database, run the command:
How to search replace string text in a table field in mysql
I have a date field in my mysql database table. I want to replace year 2017 in this field to 2019 Following command would do this. UPDATE races SET date =REPLACE( date , ‘2018’, ‘2019’ ) WHERE INSTR( date , ‘2018’ ) >0 Just saving it for reference here.
Updating two columns with a subquery in MySQL
Sometimes you wanted to update columns of one MySQL table by quering another MySQL table and getting a column value from the later table. For example you have a ‘students’ table and you wanted to create a new field ‘totalMarks’…
To print the table structure of MySQL table in PHP
You can print the table structure of MySQL table in PHP using simple php script. All you need to do is to make a ‘describe table’ call and process it using php script, similar to one you use to print…
Changing database character set
Why to consider changing database character set? Why do i need to do this? As to find a more straight answer please look at the image below, taken from one of my websites built in WordPress:
How to delete duplicate MySQL records with duplicate field value (as ID)
For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then…
Conditional order by sorting using two fields in MySQL
Here’s the scenario. I have two fields in my “listings” table with help of which i wanted to sort (order by) results. First field is “activation_date”. It is the activation date of a listing. Second one is “featured” which stores…
How to update a mysql table with the count of another table while using like statement
While working on a web links directory i wanted to update categories table’s num_listings column with the count of listings under each category. The category id was stored something like this in listings table: