• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Insert fields into exisiting mysql tables via phpmyadmin

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
8bitBoom
Asked:
8bitBoom
1 Solution
 
theGhost_k8Database ConsultantCommented:
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
 
8bitBoomAuthor Commented:
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
 
8bitBoomAuthor Commented:
If it helps. I am happy to use PHP code to achieve my goal.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
8bitBoomAuthor Commented:
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
 
lyubovCommented:
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
 
theGhost_k8Database ConsultantCommented:
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
 
8bitBoomAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now