CODEWITHSUNDEEP
mysqlviewphpmyadmin
MarcinWolny
MarcinWolny

Reputation: 1645

How to update a view in phpMyAdmin?

How can I update (or edit) a View in MySQL database using phpMyAdmin.

I got a view that's made of columns from two tables - I added a new column to one of these, but the view doesn't have it. I can't find the MySQL query I used to get this view (it's quite obscure one) - so how can I edit the MySQL query that created this view to add a new column into it?

Upvotes: 9

Views: 32348

Answers (6)

Brane
Brane

Reputation: 3339

I know that this is a very old question, but I think this will be helpful to many people as I faced this difficulty finding the Views SQL query.

I'm using Version information: 4.9.11 of phpMyAdmin and you can find views query in Structure section of the view and there is Edit View button under the columns. For better visibility here is screenshot of it:

enter image description here

After this, you can modify your query to show multiple columns in your view.

Upvotes: 1

Henrik Erlandsson
Henrik Erlandsson

Reputation: 3831

A Simpler Way

Most of the time, not being able to edit views etc. is due to the DEFINER being set to root@localhost, and if you're coming from a web host control panel, that user is not you.

  1. Click Home (house icon) top left, copy your username to the right.
  2. Click your view on the left > Structure > Edit View and edit.
  3. Paste your username in place of 'root@localhost' and click Go.

This has been tested on PHPMyAdmin 4.9.0.1, it may also work on earlier and later versions. PHPMyAdmin seems to remember your username for the rest of the session, so that you can edit freely.

Upvotes: 2

Aris
Aris

Reputation: 5057

You can also use CREATE OR REPLACE VIEW, to avoid the step of dropping the view:

  • show create view viewname. Find definition in 'Create View' column
  • use below command to add new columns: CREATE OR REPLACE VIEW

Upvotes: 2

user2775080
user2775080

Reputation: 73

To take the view for edit/update,we have used two way:

Step 1: 

select your view in phpmyadmin and click Export(make sure click check box of Structure& Add DROP VIEW) & GO.you'll see a CREATE VIEW  query like as:CREATE ALGORITHM=UNDEFINED DEFINER=`dbname`@`localhost` SQL SECURITY DEFINER VIEW `vw_name` AS select ..etc..And then remove 'ALGORITHM.....to...DEFINER' part from this query and update/added required field/make changes to the query.and then execute modified view.`


step 2:

    Run the query: SHOW CREATE VIEW `vw_name`
    Expand the result and choose Full Texts.        
    Copy entire contents of the Create View column.
    Make changes to the query.
    Run the query directly (with out the CREATE VIEW... syntax) to make sure it runs as you expect it to.

Upvotes: 3

Jason
Jason

Reputation: 15335

How about using (Your view is called viewname)

  1. SHOW CREATE VIEW viewname to get the SQL for the view as it is
  2. DROP VIEW viewname to remove the view
  3. Modify the SQL from the first step to add the new column to that SQL
  4. Run the modified SQL

That would create the view with the additional column(s)

http://dev.mysql.com/doc/refman/5.0/en/show-create-view.html

Upvotes: 9

Nahser Bakht
Nahser Bakht

Reputation: 930

On phpMyADmin Go to Export -> (select) 'Quick - display only the minimal options' -> GO.

That will give you the create statements that you made in a text file, view or save that text file and you should have all the info there.

Upvotes: 1

Related Questions