Solved

Insert fields into exisiting mysql tables via phpmyadmin

Posted on 2010-11-30
8
538 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: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
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
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:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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