copy rows

[canabatz]

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:

$sql=mysql_query("SELECT `ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, 'ans_q_id'   FROM `MY_TABLE`  where ans_su_id='2'");
$row=mysql_fetch_assoc($sql);
$num=mysql_num_rows($sql);

for ($i=1; $i<=$num; $i++)
{
$ans_caption=     $row['ans_caption'];
$ans_position=    $row['ans_position'];
$ans_su_id=       $row['ans_su_id'];
$ans_q_id=       $row['ans_q_id'];

$copy_AM=mysql_query("INSERT INTO `MY_TABLE`

(`ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, `ans_q_id`) 

VALUES 

('','$ans_caption','$ans_position','$suid','$ans_q_id')")or die(mysql_error());
 

 
}

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

[canabatz]

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

[zorgi]

$sql = "SELECT * FROM yourtable";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
 //You can do your inserts here
}

[canabatz]

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

[zorgi]

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:

$max_A_Q_ID_from_all; // I assume you know how to get this

while ($row = mysql_fetch_assoc($result)) {
  $row['A_Q_ID'] += $max_A_Q_ID_from_all;
 //You can do your inserts here
}

[canabatz]

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

[zorgi]

In that case you can not use first option of incrementing on every third iteration. Second option was better anyway :D

[canabatz]

Ok , I will try it when I'm In front of my computer.
Thanks man

[canabatz]

Second option not working too :(

any idea?

[zorgi]

What do you mean by not working?

[canabatz]

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

[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.

[canabatz]

this is what i got so far, it's working well , i need a little more tweaking .

// geting max 
$sqlSURVEY_Amax=mysql_query("SELECT max(ans_q_id) as maxq  FROM `xp_survey_answer` ")or die(mysql_error());
$rooo=mysql_fetch_assoc($sqlSURVEY_Amax);

$maxq=$rooo['maxq'];

// end geting max

// puting all in arrays
$sqlSURVEY_Ac=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());


while ($rowSURVEY_Ac=mysql_fetch_array($sqlSURVEY_Ac))
{
$ans_q_id[]=$rowSURVEY_Ac['ans_q_id'];

}

$str1 = implode(',',array_unique($ans_q_id));

$zz= count(explode(",",$str1)); 


$zz1=explode(",",$str1);

// end puting all in arrays



$sqlSURVEY_A=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
while ($rowSURVEY_A=mysql_fetch_assoc($sqlSURVEY_A))
{
	

$ans_caption=     $rowSURVEY_A['ans_caption'];
$ans_position=    $rowSURVEY_A['ans_position'];
$ans_su_id=       $rowSURVEY_A['ans_su_id'];


$ans_q_id=$rowSURVEY_A['ans_q_id'];

for ($i=0; $i<=$zz; $i++)
{
if ($ans_q_id==$zz1[$i]){$ans_q_id=($ans_q_id+($maxq / $zz) + $zz -1);}

}



$copy_AM=mysql_query("INSERT INTO `xp_survey_answer`

(`admin_id`, `ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, `ans_q_id`) 

VALUES 

('$adminid','','$ans_caption','$ans_position','$suid','$ans_q_id')")or die(mysql_error());

 
 
 
}

[canabatz]

Thanks to all for helping .
this is the final result! working perfect!

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

$sqlSURVEY_AnsSUid=mysql_query("SELECT *  FROM `xp_survey_answer` ")or die(mysql_error());

while ($rowSURVEY_Ans=mysql_fetch_array($sqlSURVEY_AnsSUid))
{
$ans_su_id[]=$rowSURVEY_Ans['ans_su_id'];
}

$str2 = implode(',',array_unique($ans_su_id));
$zz4= count(explode(",",$str2));

echo $zz4."<br>";

$sqlSURVEY_Amax=mysql_query("SELECT max(ans_q_id) as maxq  FROM `xp_survey_answer` ")or die(mysql_error());
$rooo=mysql_fetch_assoc($sqlSURVEY_Amax);

$maxq=$rooo['maxq'];




$sqlSURVEY_Ac=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
$a=0;

while ($rowSURVEY_Ac=mysql_fetch_array($sqlSURVEY_Ac))
{
$ans_q_id[]=$rowSURVEY_Ac['ans_q_id'];
echo $ans_q_id[$a]."<br>";

$a++;
}

$str1 = implode(',',array_unique($ans_q_id)); // add quote to seprate number of strings
echo "<br>".$str1; // 33,34
$zz= count(explode(",",$str1)); // count the number to add plus
echo "<br>".$zz; // 2

$zz1=explode(",",$str1);


$sqlSURVEY_A=mysql_query("SELECT *   FROM `xp_survey_answer`  where ans_su_id='".$_GET['SUR_ID']."'")or die(mysql_error());
while ($rowSURVEY_A=mysql_fetch_assoc($sqlSURVEY_A))
{
	

$ans_caption=     $rowSURVEY_A['ans_caption'];
$ans_position=    $rowSURVEY_A['ans_position'];
$ans_su_id=       $rowSURVEY_A['ans_su_id'];


$ans_q_id=$rowSURVEY_A['ans_q_id'];

for ($i=0; $i<=$zz; $i++)
{
if ($ans_q_id==$zz1[$i]){$ans_q_id=($ans_q_id+($maxq / $zz) )+ $zz4;}

}



$copy_AM=mysql_query("INSERT INTO `xp_survey_answer`

(`admin_id`, `ans_id`, `ans_caption`, `ans_position`, `ans_su_id`, `ans_q_id`) 

VALUES 

('$adminid','','$ans_caption','$ans_position','$suid','$ans_q_id')")or die(mysql_error());

 
 
 
}