...

View Full Version : Help with MySQL REPLACE...



justincredible
08-09-2006, 03:09 PM
I want to be able to take a record from student A and modify it slightly and replace a few things in the record of a student B with the modified info from student A. Does that make sense? Essentially I am taking everything from student A except for pntr and studentPntr, changing the percentComplete, courseStatus and percentComplete fields, and replacing student B's record with that information.

Here is the code of what I am trying to do now with a REPLACE...SELECT:

$courseReportQuery = "REPLACE INTO courseReports (pntr, courseCode, '$newPntr', coursePntr,
percentComplete, startDateTime, endDateTime, courseStatus, timeSpent, reviewResults,
createdOn, createdBy, updatedOn, instanceNumber, reportType, userId)
SELECT pntr, courseCode, '$newPntr', coursePntr, '$percent',startDateTime, endDateTime,
'in-progress', '$time', reviewResults, NOW(), createdBy, updatedOn, instanceNumber,
reportType, userId
FROM courseReports
WHERE studentPntr = '$_POST[pntr]'
AND courseCode = '$_POST[ref]'";

This is the error I am getting:


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 ''38', coursePntr, percentComplete, startDateTime, endDateTime,' at line 1

From the look of the error I am assuming I cannot $newPntr in the REPLACE part of the query but it was the only thing I could think of to get this to work how I want it to.

Any suggestions?

Fumigator
08-09-2006, 04:22 PM
Using REPLACE is really not necessary here, since you aren't replacing the original row; you're just inserting a row based on another row's data. You can use the same INSERT INTO table SELECT syntax (assuming your version of MySQL is > 4.0.14).

Insert...select syntax (http://dev.mysql.com/doc/refman/4.1/en/insert-select.html)

justincredible
08-09-2006, 04:25 PM
I don't want to insert a new row, though, as this query is only going to be executed if the student already has a record in the table for that particular class. I want to replace that data with the new data I got from messing around with the other students data.

Fumigator
08-09-2006, 04:28 PM
Whoops, I'm sorry, I totally misunderstood!

So what is the output of "echo $courseReportQuery"?

justincredible
08-09-2006, 04:33 PM
Output of $courseReportQuery:


REPLACE INTO courseReports (pntr, courseCode, '38', coursePntr, percentComplete, startDateTime, endDateTime, courseStatus, timeSpent, reviewResults, createdOn, createdBy, updatedOn, instanceNumber, reportType, userId) SELECT pntr, courseCode, '38', coursePntr, '846.15384615385', startDateTime, endDateTime, 'in-progress', '33396', reviewResults, NOW(), createdBy, updatedOn, instanceNumber, reportType, userId FROM courseReports WHERE studentPntr = '8' AND courseCode = 'mym3224BK'

justincredible
08-09-2006, 04:37 PM
Output of $courseReportQuery:

REPLACE INTO courseReports (pntr, courseCode, '38', coursePntr, percentComplete, startDateTime, endDateTime, courseStatus, timeSpent, reviewResults, createdOn, createdBy, updatedOn, instanceNumber, reportType, userId) SELECT pntr, courseCode, '38', coursePntr, '846.15384615385', startDateTime, endDateTime, 'in-progress', '33396', reviewResults, NOW(), createdBy, updatedOn, instanceNumber, reportType, userId FROM courseReports WHERE studentPntr = '8' AND courseCode = 'mym3224BK'

The percentComplete part of the select should only be 83.333 and the timeSpent should only be 2398 but that really isn't that important. For whatever reason though it is giving me outrageous values for those two fields. That is an entirely different issue though.

Fumigator
08-09-2006, 05:00 PM
OK I believe the problem (which I should have caught before) is you're listing one of the column names to insert into as '38', where it should be the name of the column you're wanting to insert '38' into. It's what you mentioned in the first post, that $newpntr deal.

If I understand you right, the column 'pntr' is your primary key, and you want to replace the existing row where pntr = '38' with this other data, so I think you can just remove the $newpntr from the into() area, and in the select area, use the literal '38' instead of column name pntr. That should load up '38' into column pntr as the row you are replacing.



$courseReportQuery = "REPLACE INTO courseReports (pntr, courseCode, coursePntr,
percentComplete, startDateTime, endDateTime, courseStatus, timeSpent, reviewResults,
createdOn, createdBy, updatedOn, instanceNumber, reportType, userId)
SELECT '$newPntr', courseCode, coursePntr, '$percent',startDateTime, endDateTime,
'in-progress', '$time', reviewResults, NOW(), createdBy, updatedOn, instanceNumber,
reportType, userId
FROM courseReports
WHERE studentPntr = '$_POST[pntr]'
AND courseCode = '$_POST[ref]'";

justincredible
08-09-2006, 05:19 PM
If I understand you right, the column 'pntr' is your primary key, and you want to replace the existing row where pntr = '38' with this other data, so I think you can just remove the $newpntr from the into() area, and in the select area, use the literal '38' instead of column name pntr. That should load up '38' into column pntr as the row you are replacing.

You are right, the column 'pntr' is my primary key, but it is just an auto-increment value so it's value is not important to the actually record really.

I guess to make it more clear as to what I need it to do I will just say I want to take the info from a course that student A has/is taking and copy it to student B. In order to do this, student B must also have a record for this course i.e. they must already be enrolled in order for us to get the payment. I want to replace all the values in student Bs record except for pntr, studentPntr. Those two will need to stay the same.

These are the two rows in the table I am working with in my example right now:


pntr, courseCode, studentPntr, coursePntr, percentComplete, startDateTime, endDateTime, courseStatus, timeSpent, reviewResults, createdOn, createdBy, updatedOn, instanceNumber, reportType, userId, adminPntr
6, 'mym3224BK', 8, 2, 100, '2006-05-16 10:39:31', '0000-00-00 00:00:00', 'complete', 2932, 0, '2006-08-09 09:32:44', '', '2006-08-09 09:41:19', 1, 'course', '0', ''
66, 'mym3224BK', 38, 2, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'in-progress', 0, 0, '2006-08-09 09:43:05', '', '2006-08-09 09:42:47', 1, 'course', '0', ''


These are what the two should look like after I am done (the only things that will change are percentComplete and timeSpent but for this example they aren't important so I just copied them over without modification):



pntr, courseCode, studentPntr, coursePntr, percentComplete, startDateTime, endDateTime, courseStatus, timeSpent, reviewResults, createdOn, createdBy, updatedOn, instanceNumber, reportType, userId, adminPntr
6, 'mym3224BK', 8, 2, 100, '2006-05-16 10:39:31', '0000-00-00 00:00:00', 'complete', 2932, 0, '2006-08-09 09:32:44', '', '2006-08-09 09:41:19', 1, 'course', '0', ''
66, 'mym3224BK', 38, 2, 100, '2006-05-16 10:39:31', '0000-00-00 00:00:00', 'complete', 2932, 0, '2006-08-09 09:43:05', '', '2006-08-09 09:42:47', 1, 'course', '0', ''


I haven't mentioned it yet but thanks a ton for your help so far. Hopefully this explanation is a little better.

Fumigator
08-09-2006, 05:48 PM
That explains it very well...

so.... how sold are you on this REPLACE...SELECT stuff? Would you consider an UPDATE instead?


$query = "
UPDATE courseReports as source, courseReports as target
SET target.percentComplete = source.percentComplete,
target.startDateTime = source.startDateTime,
.
.
.
WHERE source.studentPntr = '$_POST[pntr]'
AND source.courseCode = '$_POST[ref]'
and target.studentPntr = '$newPntr'
and target.courseCOde = '$_POST[ref]'
";


From what I read, UPDATE is faster and just seems to make more sense for what you are doing.

justincredible
08-09-2006, 05:59 PM
That is what I was originally going to go with but my boss told me to check out REPLACE so thats why I have been trying it out. It looks like I was correct in my thinking before hand.

Thanks a lot for the help! Hopefully this works and I can leave you alone.:D

Fumigator
08-09-2006, 06:16 PM
If your boss insists REPLACE is better, have him read the user comments on this page:

http://dev.mysql.com/doc/refman/4.1/en/replace.html

And then go "nah nah nah" to him/her :D

justincredible
08-09-2006, 06:26 PM
Another small problem I ran into, here is the query I came up with:


$courseReportQuery = "UPDATE courseReports cR1, courseReports cR2
SET cR2.percentComplete = '$percent', cR2.startDateTime = cR1.startDateTime,
cR2.courseStatus = 'in-progress', cR2.timeSpent = '$time',
cR2.createdOn = NOW()
WHERE cR1.studentPntr = '$_POST[pntr]'
AND cR1.courseCode = '$_POST[ref]'
AND cR2.studentPntr = '$newPntr'
AND cR2.courseCode = '$_POST[ref]'";

I get this error:

Unknown column 'mym3224BK' in 'where clause'

NancyJ
08-09-2006, 06:43 PM
usually that error means you havent quoted a value but they seem to be ok. echo out the query and see

NancyJ
08-09-2006, 06:46 PM
As for REPLACE INTO, its fine to use basically on anything that is 'user updated' ie anything that is taking place at the speed of a human being's update capability - if you're updating thousands of records with it in an automated process or have hundreds of users updating records, then the performance hit might be noticeable.
That is ofcourse assuming your tables are indexed correctly

justincredible
08-09-2006, 06:55 PM
NancyJ, thanks for the reply.

I have my echo in the code right after the query but it isn't echoing it out. Wierd.

justincredible
08-09-2006, 07:13 PM
Eh, nevermind. I had a problem with a query in my code before this one. All is good now. Thanks for the help to both of you!!

justincredible
08-10-2006, 03:05 PM
Looks like I am back for some more help on the situation, but with a different query as the last one is working excellent.

In my last problem I was only wanting to update one row but in this one I am gonna need to update multiple rows.

Here is the query:


$getCourseQuery = "UPDATE studentCourses sC1, studentCourses sC2
SET sC1.courseCode = sC2.courseCode, sC1.studentPntr = '$newPntr', sC1.coursePntr = sC2.coursePntr,
sC1.modulePntr = sC2.modulePntr, sC1.chapterPntr = sC2.chapterPntr, sC1.lastElementPntr = sC2.lastElementPntr,
sC1.percentComplete = sC2.percentComplete, sC1.startDateTime = sC2.startDateTime,
sC1.lastSessionStart = sC2.lastSessionStart, sC1.lastSessionEnd = sC2.lastSessionEnd,
sC1.createdBy = sC2.createdBy, sC1.createdOn = sC2.createdOn, sC1.updatedOn = sC2.updatedOn,
sC1.lookupStatus = sC2.lookupStatus, sC1.lastSeenElementPntr = sC2.lastSeenElementPntr,
sC1.lastSeenChapterFlag = sC2.lastSeenChapterFlag, sC1.moduleOrder = sC2.moduleOrder,
sC1.chapterOrder = sC2.chapterOrder, sC1.timeSpent = sC2.timeSpent, sC1.reviewCompleted = sC2.reviewCompleted,
sC1.frameWorkMode = sC2.frameWorkMode, sC1.instanceNumber = sC2.instanceNumber
WHERE sC1.courseCode = '$_POST[ref]'
AND sC1.studentPntr = '$newPntr'
AND sC2.studentPntr = '$_POST[pntr]'
AND sC2.courseCode = '$_POST[ref]'
AND sC1.chapterPntr = sC2.chapterPntr";

That didn't do what I wanted because of the last AND clause it just updated all of the records with the info from the sC2.chapterPntr = 1. I realized I needed to put it in a for loop and change "AND sC1.chapterPntr = sC2.chapterPntr" to "AND sC1.chapterPntr = $num_row", but the problem is the chapterPntr field doesn't neccessarily contain all every number i.e. in my example the chapters are 1,2,3,4,5,6,7,8,9,10,11,12,13,15. I have a feeling this is not gonna do what it needs to especially since down the road it may end up that the chapters are 1,3,4,5,8,9,10,11,...etc.

I originally had this one as a replace but when you guys talked me out of using it for the last query I decided not to use it for this one. It was also giving me some wierd results as it wasn't replacing the rows it was creating new ones. It was creating the correct data in the new rows, I'm just not sure why it wasn't replacing the old ones.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum