Tagged with phpmyadmin

PHPMyAdmin: deleting all rows from MySql table

0

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.

select Query

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.

row_after_delete_query

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:

TRUNCATE user

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.
truncate_query
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.

  Comments or questions are welcome  

PhpMyAdmin: No activity within 1440 seconds, please log in again!!!

-3

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.

phpmyadmin_logout

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:

phpmyadmin

PhpMyAdmin version: 4.6.4

  Comments or questions are welcome  

Mysql Event scheduler

-1

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.

SELECT @@event_scheduler;

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
ON SCHEDULE
EVERY 10 SECOND
DO
INSERT INTO test_scheduler (`datetime`) VALUES (CURRENT_TIMESTAMP);

The above scheduler will runs every 10 second and insert a row in “test_scheduler” table.

  Comments or questions are welcome  

Generating ERD using PHPMyAdmin

-51

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.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”.Import_ExportcoordinatesforPDFschema
  • 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: 4.0.4.1
My MySql Version: 5.5
That all!!! Its very easy and simple

  Comments or questions are welcome  

How to access remote mysql server using local phpmyadmin

-17

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:

$i++;
$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:

phpmyadmin

phpmyadmin

My PHPMyAdmin version: 4.0.4.1
My MySql Version: 5.5

  Comments or questions are welcome