...

View Full Version : Duplicating rows in a table...



justincredible
08-03-2006, 08:34 PM
Here is my select query:

$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!:thumbsup:

Brandoe85
08-03-2006, 08:40 PM
You want to insert these into a new table, or into the same table?

justincredible
08-03-2006, 08:45 PM
They will be inserted into the same table, studentCourses.

Brandoe85
08-03-2006, 08:52 PM
You could loop through the recordset and run an insert query for each row:


$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;

justincredible
08-03-2006, 08:53 PM
Thanks! I'll give that a try.:thumbsup:

justincredible
08-03-2006, 10:21 PM
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:

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');

Fumigator
08-03-2006, 10:48 PM
You are missing a comma after the value for the createdBy column.

justincredible
08-04-2006, 02:58 PM
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:


$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:

$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?

justincredible
08-07-2006, 02:58 PM
Anything?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum