?
Solved

Insert fields into exisiting mysql tables via phpmyadmin

Posted on 2010-11-30
8
Medium Priority
?
540 Views
Last Modified: 2012-05-11
Hi there,

I need to insert the following fields into a mysql database via phpmyadmin:
 Example
When doing this I want to target only the tables ending with:
_main

What is the best way to do this?

Many thanks in Advance.

Paul
0
Comment
Question by:8bitBoom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 21

Expert Comment

by:K V
ID: 34237889
To doing it manually you can:

show tables like '%_main';

For each table you can:

ALTER TABLE YOURTABLENAME ADD COLUMN `num` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT AFTER `id2`,
 ADD COLUMN `latitude` FLOAT AFTER `num`,
 ADD COLUMN `longitude` FLOAT AFTER `latitude`,
 ADD PRIMARY KEY (`num`);

or you can write a Stored procedure to do the same :)
0
 
LVL 1

Author Comment

by:8bitBoom
ID: 34237961
Hi Ghost :0)

Thanks for the reply. I am pretty much a noob with mysql. So please excuse me if I mis-understand! Is the stored procedure an automated approach ?

I have hundreds of tables and really need to have it automated if possible. I only need to run it once as future tables will have the new info included.

Also how exactly would I implement the manual approach in myphpadmin?

Thanks!
0
 
LVL 1

Author Comment

by:8bitBoom
ID: 34238048
If it helps. I am happy to use PHP code to achieve my goal.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 1

Author Comment

by:8bitBoom
ID: 34238697
I tried the solution above but got the following error:

Error

SQL query:

ALTER TABLE cms_rgm_main ADD COLUMN `num` INTEGER( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT AFTER `id2` ,
ADD COLUMN `latitude` FLOAT AFTER `num` ,
ADD COLUMN `longitude` FLOAT AFTER `latitude` ,
ADD PRIMARY KEY ( `num` )

MySQL said: Documentation
#1054 - Unknown column 'id2' in 'cms_rgm_main'

To try and help further here is an example of the table structure:
 Table Structure Example
0
 
LVL 4

Expert Comment

by:lyubov
ID: 34247158
You can remove "AFTER `id2`"  from the query or replace 'id2' with the name of the column after which the columns should be inserted
0
 
LVL 21

Accepted Solution

by:
K V earned 2000 total points
ID: 34247208
Sorry 8bitBoom, that's my mistake in copy pasting stuff:

ALTER TABLE cms_rgm_main ADD COLUMN `num` INTEGER( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT
ADD COLUMN `latitude` FLOAT AFTER `num` ,
ADD COLUMN `longitude` FLOAT AFTER `latitude` ,
ADD PRIMARY KEY ( `num` );

Meanwhile yes it's a manual approach. if you know any other scripting language it'd be easy for you to generate this string for your huge list of tables if you're not comfortable with SP!

Thanks lyubov for updating.
0
 
LVL 3

Expert Comment

by:mkiredjian
ID: 34247339
0
 
LVL 1

Author Comment

by:8bitBoom
ID: 34256487
Hi,

I am back at work tomorrow (not been well) and will check out the solutions and give feedback. Looks good from here though and I have a feeling I can use Ghost's solution with some PHO I wrote a little while back.

Paul
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question