Solved

Insert fields into exisiting mysql tables via phpmyadmin

Posted on 2010-11-30
8
496 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
8 Comments
 
LVL 21

Expert Comment

by:theGhost_k8
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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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:
theGhost_k8 earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now