Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Duplicating rows in a table...

    Here is my select query:
    Code:
    $getCourseQuery = "SELECT sC.*
    FROM studentCourses sC, profileChapters pC
    WHERE sC.courseCode = '$_GET[ref]' 
    AND sC.studentPntr = '$_GET[pntr]' 
    AND sC.chapterPntr = pC.pntr 
    AND pC.assessmentType = 'chapterReview'";
    I want to take all of the rows that were selected and duplicate them with a new studentPntr. Is there an easier and cleaner way of doing this than putting all of the fields and all of the values into the insert query? There are 23 fields in the table and I don't want to have to type them all out if I don't have to.

    Thanks for any help!

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    You want to insert these into a new table, or into the same table?

  • #3
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    They will be inserted into the same table, studentCourses.

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    You could loop through the recordset and run an insert query for each row:
    PHP Code:
    $getCourseQuery "SELECT sC.*
    FROM studentCourses sC, profileChapters pC
    WHERE sC.courseCode = '$_GET[ref]' 
    AND sC.studentPntr = '$_GET[pntr]' 
    AND sC.chapterPntr = pC.pntr 
    AND pC.assessmentType = 'chapterReview'"
    ;

    $result mysql_query($getCourseQuery) or die(mysql_error());
    while(
    $row mysql_fetch_assoc($result))
    {
       
    // of course list all of your column names you are inserting here...
       
    $query 'insert into studentCourses(courseCode) values(' $row['courseCode'] . ')';
       
    mysql_query($query) or die(mysql_error());

    Good luck;

  • #5
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks! I'll give that a try.

  • #6
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I wasn't able to get that to work the way I wanted so I went ahead and just did it the way I was planning to at first. I am not getting the following error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 13

    Here is my query:
    Code:
    INSERT into studentCourses (pntr, courseCode, studentPntr,
    coursePntr, modulePntr, chapterPntr, lastElementPntr, percentComplete,
    startDateTime, lastSessionStart, lastSessionEnd, createdBy, createdOn, updatedOn, lookupStatus, lastSeenElementPntr,lastSeenChapterFlag, moduleOrder, chapterOrder, timeSpent, reviewCompleted, frameWorkMode, instanceNumber) 
    VALUES ('','mym3224BK','100','2','2','12','0','98','2006-08-03','2006-08-03','2006-08-03',''
    '2006-08-03','2006-08-03','A','13','Y','1','12','34','N', 'element','1');

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You are missing a comma after the value for the createdBy column.

  • #8
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question

    Thanks. I was using that query for testing purposes with MySQL Query Browser and it was driving me crazy, but when I ran the actual query online it worked perfectly so I just gave up. The missing comma's get me at least once or twice a month.


    I changed my query this morning cause I didn't realize you could do an insert and select in the same query. Here is how I had it originally:

    PHP Code:
    $getCourseQuery "SELECT sC.*
                        FROM studentCourses sC, profileChapters pC
                        WHERE sC.courseCode = '$_POST[ref]' 
                        AND sC.studentPntr = '$_POST[pntr]' 
                        AND sC.chapterPntr = pC.pntr 
                        AND pC.assessmentType = 'chapterReview'"
    ;
                                                        
    $qryResult mysql_query($getCourseQuery) or die(mysql_error());
    $numCourses mysql_num_rows($qryResult);
    $fetchCourse mysql_fetch_array($qryResult);
                                    
    for(
    $i 0$i $numCourses$i++){
        
    $copyQuery "INSERT into studentCourses (pntr, courseCode, studentPntr, coursePntr,
            modulePntr, chapterPntr, lastElementPntr, percentComplete,
        startDateTime, lastSessionStart, lastSessionEnd, createdBy,
            createdOn, updatedOn, lookupStatus, lastSeenElementPntr,
        lastSeenChapterFlag, moduleOrder, chapterOrder, timeSpent, 
        reviewCompleted, frameWorkMode, instanceNumber) 
    VALUES ('','$fetchCourse[courseCode]','$newPntr',
    '$fetchCourse[coursePntr]','$fetchCourse[modulePntr]','$fetchCourse[chapterPntr]',
    '$fetchCourse[lastElementPntr]','$fetchCourse[percentComplete]','$fetchCourse[startDateTime]',
    '$fetchCourse[lastSessionStart]','$fetchCourse[lastSessionEnd]','$fetchCourse[createdBy]',
    '$fetchCourse[createdOn]','$fetchCourse[updatedOn]','$fetchCourse[lookupStatus]',
    '$fetchCourse[lastSeenElementPntr]','$fetchCourse[lastSeenChapterFlag]','$fetchCourse[moduleOrder]', 
    '$fetchCourse[chapterOrder]','$fetchCourse[timeSpent]','$fetchCourse[reviewCompleted]',
    '$fetchCourse[frameWorkMode]','$fetchCourse[instanceNumber]')"
    ;
                                                                   
    $copyCourse mysql_query($copyQuery) or die(mysql_error());
                                        
    $fetchCourse mysql_fetch_array($qryResult);

    Here is my new query:
    PHP Code:
    $getCourseQuery "INSERT INTO studentCourses(pntr, courseCode, studentPntr, coursePntr, modulePntr, chapterPntr, 
                    lastElementPntr, percentComplete,startDateTime, lastSessionStart, 
                    lastSessionEnd, createdBy, createdOn, updatedOn, lookupStatus, 
                    lastSeenElementPntr, lastSeenChapterFlag, moduleOrder, chapterOrder, 
                    timeSpent, reviewCompleted, frameWorkMode, instanceNumber)
    SELECT sC.courseCode, '$newPntr', sC.studentPntr, sC.coursePntr, sC.modulePntr, sC.chapterPntr, sC.lastElementPntr,
            sC.percentComplete, sC.startDateTime, sC.lastSessionStart, sC.lastSessionEnd, sC.createdBy, 
            sC.createdOn, sC.updatedOn, sC.lookupStatus, sC.lastSeenElementPntr, sC.lastSeenChapterFlag, 
            sC.moduleOrder, sC.chapterOrder, sC.timeSpent, sC.reviewCompleted, sC.frameWorkMode, sC.instanceNumber
    FROM studentCourses sC, profileChapters pC
    WHERE sC.courseCode = '$_POST[ref]'
    AND sC.studentPntr = '$_POST[pntr]'
    AND sC.chapterPntr = pC.pntr
    AND pC.assessmentType = 'chapterReview'"

    (sorry for the alignment issues)

    My question is how would I go about running my second query so it would work like my first solution? I know its probably really simple as I tend to over think things. Would I need to do a small query first to figure out the number of rows I can to insert and then do a for loop or is there something where I don't have to run another query before hand?

  • #9
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Anything?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •