Solved

Insert fields into exisiting mysql tables via phpmyadmin

Posted on 2010-11-30
8
524 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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