1

It is not as easy as the title itself. I have a table users which has a field assignedlessons. Data stored in this field is like 69|308|50|91. As you may have already know, it keep several lessons of a user has at the same time. What I am going to do is to export data from this field along with user_id and import to another newly created table user_assigned_elearning_lessons. The structure of this table is: id, user_id, elearning_lesson_id, created_at. After the importing, the structure in the new table should be like this:

id        user_id       elearning_lesson_id        created_at
1         1             69                         2011-01-12
2         1             308                        2011-04-11
3         2             50                         2011-05-18
4         3             91                         2011-05-21
5         3             50                         2011-07-18
6         3             308                        2011-07-18

How do I do that?

stivlo
  • 83,644
  • 31
  • 142
  • 199
庆峰 扈
  • 221
  • 1
  • 3
  • 13

3 Answers3

1

Probably you'll want to look into using INSERT ... SELECT FROM syntax. Without full details of the source and destination tables, we can't help beyond that.

Marc B
  • 356,200
  • 43
  • 426
  • 500
0

You could use syntax like:

INSERT INTO tbl2 (col1, col2, col3, ....)
SELECT col1, col2, col3
FROM tbl1

or check http://dev.mysql.com/doc/refman/5.1/en/insert-select.html for further details.

Ghazanfar Mir
  • 3,493
  • 2
  • 26
  • 42
  • Thanks for your reply Mir. Except exporting and importing itself, there are also some transformation need to be done. All lessons for a user were stored in field assignedlessons of table users with | to separate them. Now I want to separate those assigned lessons to different records in table user_assigned_elearning_lessons. A user can have multiple records depending on how many lesson he has. Can I do that with this INSERT SELECT MYSQL clause? – 庆峰 扈 Oct 17 '11 at 15:56
0

Despite what others have suggested, you can't do what you want with INSERT ... SELECT syntax, because you've to split values and insert more than one row for each row of the source table.

You can instead write a short PHP script to do the job, I've assumed the following table structure and test data:

CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(11) NOT NULL,
`assignedlessons` varchar(100) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `users` (`user_id`, `assignedlessons`) VALUES
(1, '69|308|50|91'),
(2, '56|34|7');

CREATE TABLE IF NOT EXISTS `user_assigned_elearning_lessons` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`elearning_lesson_id` int(11) NOT NULL,
`created_at` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

With a PHP script you can loop over all the rows of the first table, explode the composite field into its parts and do an INSERT statement for each of those part. I've used mysqli object oriented style with prepared statements to build the insert queries.

<?php
main();

function main() {
    $source = new mysqli("localhost", "username", "password", "database");
    $destin = new mysqli("localhost", "username", "password", "database");
    $stmt = prepareInsertStatement($destin);
    $result = $source->query("SELECT user_id, assignedlessons FROM users");
    while ($user = $result->fetch_object()) {
        insertUser($stmt, $user);
    }
    $result->close();
    $source->close();
    $destin->close();
}

function insertUser(&$stmt, &$user) {
    $lessons = explode('|', $user->assignedlessons);
    foreach ($lessons AS $lesson) {
        $stmt->bind_param("ii", $user->user_id, $lesson);
        $stmt->execute();
        echo "User " . $user->user_id . " lesson $lesson<br/>";
    }
}

function &prepareInsertStatement(&$destin) {
    $sql = "INSERT INTO user_assigned_elearning_lessons
        (user_id, elearning_lesson_id, created_at) VALUES (?, ?, NOW())";
    $stmt = $destin->stmt_init();
    $stmt->prepare($sql);
    return $stmt;
}
?>

I bind the parameters to the the prepared statement with bind_param:

$stmt->bind_param("ii", $user->user_id, $lesson);

Where the "ii" means that the type of those parameters is integer and integer.

The output with this test data will be:

User 1 lesson 69
User 1 lesson 308
User 1 lesson 50
User 1 lesson 91
User 2 lesson 56
User 2 lesson 34
User 2 lesson 7
stivlo
  • 83,644
  • 31
  • 142
  • 199