1

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.

Levi Morrison
  • 19,116
  • 7
  • 65
  • 85
Reham Fahmy
  • 4,937
  • 15
  • 50
  • 71
  • You could do the same thing with a query, an alias, and [REPLACE](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace). – Jared Farrish Oct 22 '11 at 15:49

4 Answers4

1

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:

Michael
  • 4,786
  • 11
  • 45
  • 68
0

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

Max Allan
  • 640
  • 5
  • 18
  • I don't think you actually need column names: http://stackoverflow.com/questions/1871331/php-mysql-insert-into-without-using-column-names-but-with-autoincrement-field – Jared Farrish Oct 22 '11 at 15:56
0

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
Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
0

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', ' ', '-'))
Nick Clark
  • 4,439
  • 4
  • 23
  • 25