Basically, you can delete rows in MySQL table by using below two queries, which are explained as :
Example: I have createded a “user” table with some sample data.
Now if run delete query as below, it will delete all rows in it but structure and next auto increment value will remain unchange.
DELETE FROM `user`
When you insert a new record it will take next auto increment value i.e 5.
Additionally if you want to delete specific rows you can add conditions to your query like below:
DELETE FROM `user` WHERE `id` = 1
Now if you look at TRUNCATE it will reset your auto increment id. next time you insert a new record it will start with 1 again. Now run the below query to truncate the data:
And browse the rows in table after inserting a new record, it will start again with 1.
It is usefull when you want to start the application as a freash. It will remove all your test/junk data inserted while testing.
With Truncate you can’t delete specific rows as it is with delete. So Truncate will restric user from doing so but delete allow user to delete rows based on conditions provided in where clause.
Hope this will help some to understand better DELETE and TRUNCATE statements.
If you do not do any activity in your PHPMyAdmin it will log out automatically after 1440 seconds or say 24 minutes and shows error as “No activity within 1440 seconds, please log in again”.
This feature is not available in old versions of PHPMyAdmin. If you are working on your local environment it is very annoying to logging again and again. So to over come, you can increase your log out time with some greater value.
Follow below steps to update your log out time:
1) Go to your phpMyAdmin and login.
2) Now navigate to “settings” tab from the top.
3) Now you will see few more sub menu items. Click on “Features”.
4) On this page select “General” tab (which is default tab).
5) Now update the “Login cookie validity” value, so your log out time will increase.
6) Update the value and click on “Apply” button.
Note: PhpMyAdmin will take the new value as a log out time. But if any case it is not working try to restart the MySQL services.
PhpMyAdmin Documentation Reference.
See the screen shot below:
I have already posted a blog (Event Scheduler an alternative to cronjob) with different advantages of Even scheduler over cron job, here I am sharing how you can configure MySql server to enable or disable scheduler and writing a sample event scheduler.
You can check with below query as your server is up for event scheduler or not.
Query will gives your “ON” or “OFF”. if it is off you can enable it as:
SET GLOBAL event_scheduler = ON;
Now you can create your own event scheduler as below:
CREATE EVENT suresh_test
EVERY 10 SECOND
INSERT INTO test_scheduler (`datetime`) VALUES (CURRENT_TIMESTAMP);
The above scheduler will runs every 10 second and insert a row in “test_scheduler” table.
Today I need to generate ERD for my Database. If you search on net you will get number of people suggest Workbench for it but as i am using PHPMyAdmin as an GUI for my Mysql database I just don’t want to install workbench only for generating ERD. And above all when PHPMyAdmin support it (from 3.4 version) than what more I need.
So here I am sharing few steps to generate ERD from PHPMyAdmin:
Go to your PHPMyAdmin setup, usually it is “http://localhost/phpmyadmin” in your local machine.
Select the database for which you want to generate.
Select “Designer” from last menu item i.e. “More”. You will land on page with different options to create ERD.
Once you are done with ERD creation click on “Import/Export coordinates for PDF schema” icon which is second last, you will land on page which will ask you to create the page with some name say “ERD V1.0”.
Than Click on “Edit or export relational schema” link at the bottom of operation Menu of PHPMyAdmin. On this page select the page as “ERD V1.0” and generate the ERD diagram as PDF document.
My PHPMyAdmin version: 22.214.171.124 My MySql Version: 5.5
That all!!! Its very easy and simple
It is very tedious to connect your remote mysql box especially when you are working with VPN. Initially I used telnet to connect to mysql box than I try to use workbench. Workbench is good and better than using telnet but again it consume more memory and if VPN got disconnected it just hanged. It so irritating.
Than I try to configure my PHPMyAdmin to access remote mysql server and it is really very easy to setup. Just you need to configure/modify a single in PHPMyAdmin folder i.e. “config.inc.php”.
Just add below lines to your “config.inc.php” file add the bottom:
$cfg['Servers'][$i]['host'] = 'HostName:port'; //provide hostname and port if other than default
$cfg['Servers'][$i]['user'] = 'userName'; //user name for your remote server
$cfg['Servers'][$i]['password'] = 'Password'; //password
$cfg['Servers'][$i]['auth_type'] = 'config'; // keep it as config
If your mysql server is in remote than make sure that you are very much connected to box and than try to access PHPMyAdmin using url. You will get “Current Server:” drop down with both “127.0.0.1” and one what you have provided with “$cfg[‘Servers’][$i][‘host’]” cam switch between the servers.
Click to See the demo screen:
My PHPMyAdmin version: 126.96.36.199 My MySql Version: 5.5