[SOLVED] Copying rows and auto incrementing

[jsprague]

Hello.. Let me preface this question by saying that I am not at all a programmer, but can follow instructions.

 

I have a MySQL database for a php quiz application. Each row in the "questions' table is a quiz question each with a unique "id" which are grouped together by a "setid" field. I need to copy (not move) a group of questions from one setid to another so that these questions appear in both quizzes. While I don't even know the SQL to do this, I realized a bigger problem. How do I get the copied questions to auto-increment their "id"?

 

I can use PHPMyAdmin to edit each question and alter the "id" and "setid" manually and then save to a new row, but this will take a very long time. Was hoping there is some SQL I could run to speed up the process.

 

Thanks very much in advance and hope this made sense..

 

[PHP_PhREEEk]

Open phpMyAdmin. Load the database you want to copy. Select the tab in the main window frame labeled 'Operations'. Tick what you need, give it a new table name, and have at it. Instant copy in seconds or less...

 

PhREEEk

[jsprague]

Thanks for the response.. That would create a new table and wouldn't work.. Let me try to illustrate better..

 

Questions table in quiz database:

id | setid | question | answer

1  |  38  |  xyz      |  abc

2  |  38  |  sdf      |    llkj

3  |  38  |  qwe      |  lkj

 

So, what I need to do is copy these questions (for example) from setid 38 to setid 62 and the id has to auto-increment because it has to be unique. They will still be located in the questions table. Hopefully that makes more sense..

 

Thanks!

[PHP_PhREEEk]

ah, ok, a copy within the same table... well what do we have to work with here that is always static? Is this always going to be from 38 -> 62?

 

PhREEEk

[jsprague]

Thanks for the reply PhREEEk..

 

It is basically a one off thing. So right now, I need to move 72 questions from setid 38->62. Yes, that will be static amongst all of the rows being copied.

[PHP_PhREEEk]

example 38 -> 62

 

<?php
$sql = "SELECT * FROM `questions`
       WHERE `setid` = '38'
      ";
if ( !$result = mysql_query($result) ) {
   die('MySQL Error: ' . mysql_error());
}
while ( $row = mysql_fetch_assoc($result) ) {
   $sql = "INSERT INTO `questions`
          (`id`, `setid`, `question`, `answer`)
           VALUES
          ('', '62', '" . $row['question'] . "', '" . $row['answer'] . "')
          ";
   if ( !result2 = mysql_query($sql) ) {
       die('MySQL INSERT Error: ' . mysql_error());
   }
}

 

Now you will have:

id | setid | question | answer
1  |   38  |  xyz       |   abc
2  |   38  |  sdf       |    llkj
3  |   38  |  qwe      |   lkj
4  |   62  |  xyz       |   abc
5  |   62  |  sdf       |    llkj
6  |   62  |  qwe      |   lkj

 

PhREEEk

[PHP_PhREEEk]

If you need them to start at id 1:

 

<?php
$sql = "SELECT * FROM `questions`
        WHERE `setid` = '38'
       ";
if ( !$result = mysql_query($result) ) {
    die('MySQL Error: ' . mysql_error());
}
while ( $row = mysql_fetch_assoc($result) ) {
    $copy_rows[] = $row;
}
$sql = "DELETE FROM `questions`
        WHERE `setid` = '38'
       ";
if ( !$result = mysql_query($result) ) {
    die('MySQL Error: ' . mysql_error());
}
for ( $x = 0 ; $x < count($copy_rows) ; $x++ ) {
    $sql = "INSERT INTO `questions`
           (`id`, `setid`, `question`, `answer`)
            VALUES
           ('', '62', '" . $copy_rows[$x]['question'] . "', '" . $copy_rows[$x]['answer'] . "')
           ";
    if ( !result2 = mysql_query($sql) ) {
        die('MySQL INSERT Error: ' . mysql_error());
    }
}

 

PhREEEk

[jsprague]

Wow.. You rule.. Now I can just substitute the actual values in and should be all set.. Am I correct that there is an apostrophe missing in the values string.. I added it and highlighted it in blue.

 

('', '" . $row['id'] . "', '62', '" . $row['question'] . "', '" . $row['answer'] . "')

 

Thanks very much for the help!!!

[PHP_PhREEEk]

There was an error in the first code posted.. it has been fixed (post modified).

 

Error in the second code too... lol  changed $row to $copy_rows in the VALUES

 

PhREEEk

[PHP_PhREEEk]

Please make sure you BACKUP your database before performing any of these operations on it!! = ^)

 

PhREEEk