CODEWITHSUNDEEP
phpmysqlregex
Reham Fahmy
Reham Fahmy

Reputation: 5063

How to insert new field in mysql database

Let assume we've database table My_table with (id-name)

CREATE TABLE `drink` (
`id` int(5) NOT NULL auto_increment,
`name` varchar(64) NOT NULL default '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9034 ;

INSERT INTO `My_table` VALUES (1, 'my name is someone');

How to automate adding new fields using php or something else so that it can be (id-new-name)

and where new = name after we replace spaces to - using

$new = str_replace(' ', '-', trim($name));

so the table become

CREATE TABLE `drink` (
`id` int(5) NOT NULL auto_increment,
`new` varchar(64) NOT NULL default '',
`name` varchar(64) NOT NULL default '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9034 ;

INSERT INTO `My_table` VALUES (1, 'my-name-is-someone', 'my name is someone');

I'd like to operate it cause it has half million of lines and impossible to do it manually so any idea!

Thanks for help.

Upvotes: 1

Views: 3174

Answers (4)

Nick Clark
Nick Clark

Reputation: 4467

Similar to what Macovei suggested, you can generate the new name when you insert a new record by doing this:

INSERT INTO drink (new, name) VALUES ('the name', REPLACE('the name', ' ', '-'))

Upvotes: 0

Michael
Michael

Reputation: 4876

You might want to do that:

  1. Adding a new column to the table:

    ALTER TABLE drink ADD new varchar(100) after id;

  2. Setting values for the new column using the pattern you described:

    UPDATE drink SET new = REPLACE(name, ' ', '-');

RESOURCES:

Upvotes: 1

Jared Farrish
Jared Farrish

Reputation: 49208

If you only need the value back, and don't need to search by new, you can:

SELECT id, REPLACE(name, ' ','-') AS new, name
FROM drink

Upvotes: 0

Max Allan
Max Allan

Reputation: 640

When you insert anything i MySQL you have to specify the columns..Like this INSERT INTObanans(id,sort) VALUES ('1','Green')

Upvotes: 0

Related Questions