Results 1 to 1 of 1
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unhappy Unanswered: MySQL stored procedure (PLZ solve my problem)

    Hello Friends!!! could anyone of you help me, I need the following mysql function code in mysql stored procedures format, I tried my best upto this(code marked under the line)
    ----------------------------------------------------------------------------------------------------------------------------
    //code I tried and is working fine upto selecting and i need to insert and delete
    mysql_query('DROP PROCEDURE IF EXISTS getscoes;
    CREATE PROCEDURE getscoes( IN user BIGINT( 10 ) , IN courid BIGINT( 10 ))

    SET @sql_text=CONCAT("SELECT * FROM mdl_grade_grades gg, mdl_grade_items gi, mdl_scorm sc, mdl_scorm_scoes_track ssc WHERE gg.userid = user
    AND usermodified = user
    AND gi.courseid = courid
    AND gi.id = gg.itemid
    AND sc.id = gi.iteminstance
    AND ssc.scormid = sc.id
    AND ssc.userid = user");
    SELECT @sql_text');
    ----------------------------------------------------------------------------------------------------------------------------
    //code i need in mysql stored procedures
    function scormtrack($user,$courid)
    {
    $scormfile= get_records_sql('SELECT * FROM mdl_grade_grades gg, mdl_grade_items gi, mdl_scorm sc, mdl_scorm_scoes_track ssc WHERE gg.userid ='.$user.' AND usermodified ='.$user.' AND gi.courseid ='.$courid.' AND gi.id

    = gg.itemid AND sc.id

    = gi.iteminstance AND ssc.scormid = sc.id

    AND ssc.userid ='.$user.'');
    $sq='SELECT * FROM mdl_grade_grades gg, mdl_grade_items gi, mdl_scorm sc, mdl_scorm_scoes_track ssc WHERE gg.userid ='.$user.' AND usermodified ='.$user.' AND gi.courseid ='.$courid.' AND gi.id

    = gg.itemid AND sc.id

    = gi.iteminstance AND ssc.scormid = sc.id

    AND ssc.userid ='.$user.'';
    echo $sq;

    foreach($scormfile as $scormval)
    {

    $userid=$scormval->userid;
    $scormid=$scormval->scormid;
    $attempt=$scormval->attempt;
    $element=$scormval->element;
    $value=$scormval->value;
    $timemodified=$scormval->timemodified;

    $scoid=$scormval->scoid;

    $sqlabc="INSERT INTO mdl_scorm_scoes_tract_bk (userid,scormid,scoid,attempt,element,value,timemo dified) values($userid,$scormid,$scoid,$attempt,'$element' ,'$value',$timemodified)";
    echo $sqlabc;
    $myquresult = mysql_query($sqlabc ) or die ('MySQL Error processing courses' . mysql_error());

    $delsql='DELETE FROM mdl_scorm_scoes_track where userid='.$userid.' AND scormid='.$scormid.'';

    echo $delsql;
    $myquresult = mysql_query($delsql ) or die ('MySQL Error processing courses' . mysql_error());
    }

    }
    Last edited by bangaru1234; 05-01-10 at 14:24. Reason: slight error in posting

Posting Permissions

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