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:

PHP Code:


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

Quote:

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

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:

Code:

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

Quote:

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.

PHP Code:


			
$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

Quote:

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:

Code:

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

Code:

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?

PHP Code:


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

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

justincredible · 08-09-2006, 06:26 PM

Another small problem I ran into, here is the query I came up with:

PHP Code:


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