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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help with MySQL REPLACE...

    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:

    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?

  • #2
    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
    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

  • #3
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #4
    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
    Whoops, I'm sorry, I totally misunderstood!

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

  • #5
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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'
    Last edited by guelphdad; 08-09-2006 at 05:54 PM.

  • #6
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #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
    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]'"


  • #8
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.
    Last edited by guelphdad; 08-09-2006 at 05:55 PM.

  • #9
    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
    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.

  • #10
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #11
    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
    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

  • #12
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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'
    Last edited by justincredible; 08-09-2006 at 06:31 PM.

  • #13
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    usually that error means you havent quoted a value but they seem to be ok. echo out the query and see

  • #14
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    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

  • #15
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    NancyJ, thanks for the reply.

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


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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