By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

copy rows

100+
P: 155
Hi,

i want to duplicate rows in a table.

i got this for example:



what im trying to do is duplicate all rows in the same table.

what i got so far is:

Expand|Select|Wrap|Line Numbers
  1. $sql=mysql_query("SELECT `ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, 'ans_q_id'   FROM `MY_TABLE`  where ans_su_id='2'");
  2. $row=mysql_fetch_assoc($sql);
  3. $num=mysql_num_rows($sql);
  4.  
  5. for ($i=1; $i<=$num; $i++)
  6. {
  7. $ans_caption=     $row['ans_caption'];
  8. $ans_position=    $row['ans_position'];
  9. $ans_su_id=       $row['ans_su_id'];
  10. $ans_q_id=       $row['ans_q_id'];
  11.  
  12. $copy_AM=mysql_query("INSERT INTO `MY_TABLE`
  13.  
  14. (`ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, `ans_q_id`) 
  15.  
  16. VALUES 
  17.  
  18. ('','$ans_caption','$ans_position','$suid','$ans_q_id')")or die(mysql_error());
  19.  
  20.  
  21.  
  22. }
ans_id is auto increment.

what i want to do while inserting the rows is find the MAX(ans_q_id) and add +1 one to it and the +1 is for 3 rows ,then the other 3 rows will will be +1 too

so the new ans_q_id + the old one will be like that:
33
33
33
34
34
34
35
35
35
36
36
36

thanks
Dec 1 '11 #1

✓ answered by zorgi

Is A_Q_ID starting from 1 and growing every few records or it starts from from some different number than 1?

If it starts from number 1 than my previous explanation should work.

If it starts from some other number like 33 in you last example than after you find your max A_Q_ID you need to calculate how big are your steps:


max_A_Q_ID = x
min_A_Q_ID = y

than

next_A_Q_ID = max_A_Q_ID + 1
step = next_A_Q_ID - min_A_Q_ID

Lets extend your example and test this:

---pos----A_Q_ID
----1-------33--
----2-------33--
----3-------33--
----1-------34--
----2-------34--
----3-------34--
----1-------35--
----2-------35--
----3-------35--

max_A_Q_ID = 35
min_A_Q_ID = 33

next_A_Q_ID = max_A_Q_ID + 1 = 35 + 1 = 36
step = next_A_Q_ID - min_A_Q_ID = 36 - 33 = 3

---pos----A_Q_ID
----1-------33 + 3 = 36 --
----2-------33 + 3 = 36 --
----3-------33 + 3 = 36 --
----1-------34 + 3 = 37 --
----2-------34 + 3 = 37 --
----3-------34 + 3 = 37 --
----1-------35 + 3 = 38 --
----2-------35 + 3 = 38 --
----3-------35 + 3 = 38 --

IF A_Q_ID is some sort of random number (NOT sequential) than this logic doesn't work.

Share this Question
Share on Google+
13 Replies


100+
P: 155
i will ask again but differently so it will be easier for understanding what i need.

i got this rows right now

id----------position-------------A_Q_ID
1---------------1--------------------5-----
2---------------2--------------------5-----
3---------------3--------------------5-----
4---------------1--------------------6-----
5---------------2--------------------6-----
6---------------3--------------------6-----

what i need is to duplicat all the rows and have them inserted after the rows i allready have like that:

id------------position-------------A_Q_ID
7-----------------1--------------------7-----
8-----------------2--------------------7-----
9-----------------3--------------------7-----
10---------------1--------------------8-----
11---------------2--------------------8-----
12---------------3--------------------8-----

i tryed few ways without success .
any idea how to do that please? i cannot get the logic on doing it :(

thanks
Dec 1 '11 #2

zorgi
Expert 100+
P: 431
Expand|Select|Wrap|Line Numbers
  1. $sql = "SELECT * FROM yourtable";
  2. $result = mysql_query($sql);
  3. while ($row = mysql_fetch_assoc($result)) {
  4.  //You can do your inserts here
  5. }
  6.  
Dec 1 '11 #3

100+
P: 155
thanks, this i allready done.

the problem is that A_Q_ID is not auto increment.

if you look at the position it got 1,2,3 this is 3 options ,and the A_Q_ID is the same.

what im trying to accomplish is to duplcate the 3 options but have them with different A_Q_ID.

so if i got it like that:

pos-----A_Q_ID
1--------3----
2--------3----
3--------3----
1--------4----
2--------4----
3--------4----

and what i need after duplicating is:

pos-----A_Q_ID
1--------3----
2--------3----
3--------3----
1--------4----
2--------4----
3--------4----

1--------5----
2--------5----
3--------5----
1--------6----
2--------6----
3--------6----

thanks
Dec 1 '11 #4

zorgi
Expert 100+
P: 431
You could do this in couple of ways. More obvious approach is to take the largest A_Q_ID and increment every third iteration. However more economic approach would be to take the larges A_Q_ID and add it to the existing value of A_Q_ID. Something like this:

Expand|Select|Wrap|Line Numbers
  1. $max_A_Q_ID_from_all; // I assume you know how to get this
  2.  
  3. while ($row = mysql_fetch_assoc($result)) {
  4.   $row['A_Q_ID'] += $max_A_Q_ID_from_all;
  5.  //You can do your inserts here
  6. }
  7.  
Dec 1 '11 #5

100+
P: 155
I hope it will do the trick .

Some have more rows like that:

Pos-----------A-q-id
1------------------5---
2------------------5---
3------------------5---
4------------------5---
1------------------6---
2------------------6---

How I can do it if it's like that?

Thanks
Dec 1 '11 #6

zorgi
Expert 100+
P: 431
In that case you can not use first option of incrementing on every third iteration. Second option was better anyway :D
Dec 1 '11 #7

100+
P: 155
Ok , I will try it when I'm In front of my computer.
Thanks man
Dec 1 '11 #8

100+
P: 155
Second option not working too :(

any idea?
Dec 1 '11 #9

zorgi
Expert 100+
P: 431
What do you mean by not working?
Dec 2 '11 #10

100+
P: 155
i tryd with the MAX(A_Q_ID) to add +1 to the new created rows ,but it's not working.

im going to write my question and ask how would you do that.

if you got this for example:

---pos----A_Q_ID
----1-------33--
----2-------33--
----3-------33--
----1-------34--
----2-------34--
----3-------34--


now, i want to duplicate those rows to have new rows like that:

---pos----A_Q_ID
----1-------35--
----2-------35--
----3-------35--
----1-------36--
----2-------36--
----3-------36--

as you can see from the example ,the A_Q_ID add +1 to the A_Q_ID.

what i think should be done is first find the MAX(A_Q_ID)
then while duplicating on every new created row to have +1.

the new rows can be different if the MAX(A_Q_ID) is allready been used.

so if in some reason A_Q_ID 35,36 was used then it will be 37,38.

how would you do that?

thanks
Dec 2 '11 #11

zorgi
Expert 100+
P: 431
Is A_Q_ID starting from 1 and growing every few records or it starts from from some different number than 1?

If it starts from number 1 than my previous explanation should work.

If it starts from some other number like 33 in you last example than after you find your max A_Q_ID you need to calculate how big are your steps:


max_A_Q_ID = x
min_A_Q_ID = y

than

next_A_Q_ID = max_A_Q_ID + 1
step = next_A_Q_ID - min_A_Q_ID

Lets extend your example and test this:

---pos----A_Q_ID
----1-------33--
----2-------33--
----3-------33--
----1-------34--
----2-------34--
----3-------34--
----1-------35--
----2-------35--
----3-------35--

max_A_Q_ID = 35
min_A_Q_ID = 33

next_A_Q_ID = max_A_Q_ID + 1 = 35 + 1 = 36
step = next_A_Q_ID - min_A_Q_ID = 36 - 33 = 3

---pos----A_Q_ID
----1-------33 + 3 = 36 --
----2-------33 + 3 = 36 --
----3-------33 + 3 = 36 --
----1-------34 + 3 = 37 --
----2-------34 + 3 = 37 --
----3-------34 + 3 = 37 --
----1-------35 + 3 = 38 --
----2-------35 + 3 = 38 --
----3-------35 + 3 = 38 --

IF A_Q_ID is some sort of random number (NOT sequential) than this logic doesn't work.
Dec 2 '11 #12

100+
P: 155
this is what i got so far, it's working well , i need a little more tweaking .
Expand|Select|Wrap|Line Numbers
  1. // geting max 
  2. $sqlSURVEY_Amax=mysql_query("SELECT max(ans_q_id) as maxq  FROM `xp_survey_answer` ")or die(mysql_error());
  3. $rooo=mysql_fetch_assoc($sqlSURVEY_Amax);
  4.  
  5. $maxq=$rooo['maxq'];
  6.  
  7. // end geting max
  8.  
  9. // puting all in arrays
  10. $sqlSURVEY_Ac=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
  11.  
  12.  
  13. while ($rowSURVEY_Ac=mysql_fetch_array($sqlSURVEY_Ac))
  14. {
  15. $ans_q_id[]=$rowSURVEY_Ac['ans_q_id'];
  16.  
  17. }
  18.  
  19. $str1 = implode(',',array_unique($ans_q_id));
  20.  
  21. $zz= count(explode(",",$str1)); 
  22.  
  23.  
  24. $zz1=explode(",",$str1);
  25.  
  26. // end puting all in arrays
  27.  
  28.  
  29.  
  30. $sqlSURVEY_A=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
  31. while ($rowSURVEY_A=mysql_fetch_assoc($sqlSURVEY_A))
  32. {
  33.  
  34.  
  35. $ans_caption=     $rowSURVEY_A['ans_caption'];
  36. $ans_position=    $rowSURVEY_A['ans_position'];
  37. $ans_su_id=       $rowSURVEY_A['ans_su_id'];
  38.  
  39.  
  40. $ans_q_id=$rowSURVEY_A['ans_q_id'];
  41.  
  42. for ($i=0; $i<=$zz; $i++)
  43. {
  44. if ($ans_q_id==$zz1[$i]){$ans_q_id=($ans_q_id+($maxq / $zz) + $zz -1);}
  45.  
  46. }
  47.  
  48.  
  49.  
  50. $copy_AM=mysql_query("INSERT INTO `xp_survey_answer`
  51.  
  52. (`admin_id`, `ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, `ans_q_id`) 
  53.  
  54. VALUES 
  55.  
  56. ('$adminid','','$ans_caption','$ans_position','$suid','$ans_q_id')")or die(mysql_error());
  57.  
  58.  
  59.  
  60.  
  61. }
Dec 2 '11 #13

100+
P: 155
Thanks to all for helping .
this is the final result! working perfect!

i know it looks much more complicated from what i ask :

Expand|Select|Wrap|Line Numbers
  1. $sqlSURVEY_AnsSUid=mysql_query("SELECT *  FROM `xp_survey_answer` ")or die(mysql_error());
  2.  
  3. while ($rowSURVEY_Ans=mysql_fetch_array($sqlSURVEY_AnsSUid))
  4. {
  5. $ans_su_id[]=$rowSURVEY_Ans['ans_su_id'];
  6. }
  7.  
  8. $str2 = implode(',',array_unique($ans_su_id));
  9. $zz4= count(explode(",",$str2));
  10.  
  11. echo $zz4."<br>";
  12.  
  13. $sqlSURVEY_Amax=mysql_query("SELECT max(ans_q_id) as maxq  FROM `xp_survey_answer` ")or die(mysql_error());
  14. $rooo=mysql_fetch_assoc($sqlSURVEY_Amax);
  15.  
  16. $maxq=$rooo['maxq'];
  17.  
  18.  
  19.  
  20.  
  21. $sqlSURVEY_Ac=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
  22. $a=0;
  23.  
  24. while ($rowSURVEY_Ac=mysql_fetch_array($sqlSURVEY_Ac))
  25. {
  26. $ans_q_id[]=$rowSURVEY_Ac['ans_q_id'];
  27. echo $ans_q_id[$a]."<br>";
  28.  
  29. $a++;
  30. }
  31.  
  32. $str1 = implode(',',array_unique($ans_q_id)); // add quote to seprate number of strings
  33. echo "<br>".$str1; // 33,34
  34. $zz= count(explode(",",$str1)); // count the number to add plus
  35. echo "<br>".$zz; // 2
  36.  
  37. $zz1=explode(",",$str1);
  38.  
  39.  
  40. $sqlSURVEY_A=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
  41. while ($rowSURVEY_A=mysql_fetch_assoc($sqlSURVEY_A))
  42. {
  43.  
  44.  
  45. $ans_caption=     $rowSURVEY_A['ans_caption'];
  46. $ans_position=    $rowSURVEY_A['ans_position'];
  47. $ans_su_id=       $rowSURVEY_A['ans_su_id'];
  48.  
  49.  
  50. $ans_q_id=$rowSURVEY_A['ans_q_id'];
  51.  
  52. for ($i=0; $i<=$zz; $i++)
  53. {
  54. if ($ans_q_id==$zz1[$i]){$ans_q_id=($ans_q_id+($maxq / $zz) )+ $zz4;}
  55.  
  56. }
  57.  
  58.  
  59.  
  60. $copy_AM=mysql_query("INSERT INTO `xp_survey_answer`
  61.  
  62. (`admin_id`, `ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, `ans_q_id`) 
  63.  
  64. VALUES 
  65.  
  66. ('$adminid','','$ans_caption','$ans_position','$suid','$ans_q_id')")or die(mysql_error());
  67.  
  68.  
  69.  
  70.  
  71. }
  72.  
  73.  
Dec 2 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.