Mysql Insert data to table with duplicate data except one field

Question

I am developing a classroom website.

There is a form to insert student profile/data into the database table student.

This site has 5 class groups, IDs as id= 1, 2, 3, 4, 5.

Inserting data to database table succeeds.

But I have a question: Each student must be under classroom 1 and 2, so when we insert data I need the database to automatically create two database results for each times, both results all field are same data except classgroup_id, i mean one result must classgroup_id=1 and second result must be classgroup_id=2, i need mysql automatically generated this for when add each student... any idea.?

this is my table structure

student_id (int) AI
name
email
classgroup_id (default value=1)
user_id

this is my php code for insert data to table

$this->db->query("INSERT INTO " . DB_PREFIX . "student SET user_id = '" . (int)$this->user->getId() . "', name = '" . $this->db->escape($data['name']) . "',  email = '" . $this->db->escape($data['email']) . "'");

thanks... i have only a medium level php knowledge

you are inserting student_id? you don't have to insert AI fields. — Mark
– Mark, Commented Apr 4, 2013 at 7:22
@UlfricStormcloak... sorry that a mistake, i dnt insert student_id, mysql automatic generate student_id for each student, i edited my question.... thanks — mans
– mans, Commented Apr 4, 2013 at 7:25

Andrej · Accepted Answer · 2013-04-04 08:34:15Z

ClassGroups are in table or just static numbers?

If they are just static numbers, then i think simpliest way is to do another insert with duplicated data. For example for both rows should be:

$this->db->query("INSERT INTO " . DB_PREFIX . "student SET user_id = '" . (int)$this->user->getId() . "', name = '" . $this->db->escape($data['name']) . "',  email = '" . $this->db->escape($data['email']) . "'");

$this->db->query("INSERT INTO " . DB_PREFIX . "student SET user_id = '" . (int)$this->user->getId() . "', name = '" . $this->db->escape($data['name']) . "',  email = '" . $this->db->escape($data['email']) . "', classgroup_id =2");

If they are in some table, then you can do insert with one insert(code will be shorter) but with different insert syntax then yours. For example your ClassGroup table is just ClassGroups:

$this->db->query("INSERT INTO " . DB_PREFIX . "student (user_id, name, email, ClassGroup_id)
select " . (int)$this->user->getId() . ", '" . $this->db->escape($data['name']) . "',  '" . $this->db->escape($data['email']) . "',ClassGroup_id from ClassGroups where ClassGroup_id=1 or ClassGroup_id=2");

But i think it should be best if you do for each data(student, ClassGroup) different table and do relation table for them, it will not duplicate data and table student will be faster if you gather data from it by primary AI key and not by varchar type column name.

hd1 · Accepted Answer · 2013-04-04 07:44:23Z

1

You don't need PHP to do this... Pure SQL pseudosolution:

INSERT INTO student (student_id name, email) SELECT name, email from student where classgroup_id = ?

If you construct a fiddle and leave a comment as to where to find said fiddle, I'd be happy to tweak the query for your specific needs.

answered Apr 4, 2013 at 7:44

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Comments

Marty McVry · Accepted Answer · 2013-04-04 07:50:09Z

0

In order to avoid duplicate entries for students, you can make another table in which you link the students to their classes.

For example:

Students

student_id (primary key)
name
email
user_id (if still needed...)

Classgroups

classgroup_id (primary key)
classgroup_name

StudentsPerClassgroup

student_id (foreign key)
classgroup_id (foreign key)

answered Apr 4, 2013 at 7:50

Marty McVry

3,0811 gold badge22 silver badges24 bronze badges

Comments

alwaysLearn · Accepted Answer · 2013-04-04 08:33:18Z

0

You have to keep the record in temporary table first and then do the operations .. try it

//get last insertId
$last_insert_id = $this->db->insert_id();
$new_id      = $last_insert_id +1;    


$query = "CREATE TEMPORARY TABLE tmp SELECT * FROM yourtable WHERE your_primary_key =  $last_insert_id;

         UPDATE tmp SET your_primary_key= $new_id,classgroup_id  = 2 WHERE your_primary_key = $last_insert_id;

         INSERT INTO yourTable SELECT * FROM tmp WHERE your_primary_key = new_id";
$this->db->query($query);

Hope you get some idea

edited Apr 4, 2013 at 8:33

answered Apr 4, 2013 at 7:34

alwaysLearn

6,9507 gold badges45 silver badges69 bronze badges

2 Comments

mans Over a year ago

but my classgroup_id not primary key... i need classgroup_id only different from first result, 1 and 2... even this data 1 and 2 just a numeric number no problem... thanks

mans Over a year ago

i set classgroup_d default value 1, so each time insert data classgroup_d field already have data 1, but i need a duplicate result also. that result classgroup_id value will be 2... thats it

Collectives™ on Stack Overflow

Mysql Insert data to table with duplicate data except one field

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related