Getting values from MySQL using PHP

[evergrey]

Hi guys,

 

I am currently using a piece of code that transfers specific data from Moodle E-learning system to Joomla.

 

Now it transfers grades and names of the test items for currently logged in user. I am trying to achieve that even the date of

the test will be transfered.

 

This is the code I have:

 function get_user_grades ($user,$cid) 
{
        global $CFG, $DB;

	$user = utf8_decode ($user);
        $user = get_complete_user_data ('username', $user);
        $uid = $user->id;


	$SQL = "SELECT g.itemid, g.finalgrade,gi.courseid,gi.itemname,gi.id, g.timemodified
                      FROM {$CFG->prefix}grade_items gi
                           JOIN {$CFG->prefix}grade_grades g      ON g.itemid = gi.id
                           JOIN {$CFG->prefix}user u              ON u.id = g.userid
                           JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id
                     WHERE g.finalgrade IS NOT NULL
		   AND u.id =  ?
		   AND gi.courseid = ?
                  GROUP BY g.itemid";

    $sum_array = array();
	$params = array ($uid, $cid);
    if ($sums = $DB->get_records_sql($SQL, $params)) 
	{
		$i = 0;
		$rdo = array ();
		foreach ($sums as $sum)
		{
			 if (! $grade_grade = grade_grade::fetch(array('itemid'=>$sum->id,'userid'=>$uid))) {
				$grade_grade = new grade_grade();
				$grade_grade->userid = $this->user->id;
				$grade_grade->itemid = $grade_object->id;
				}

				$grade_item = $grade_grade->load_grade_item();

				$sums2[$i] = $sum;
				$scale = $grade_item->load_scale();
				$formatted_grade = grade_format_gradevalue($sums2[$i]->finalgrade, &$grade_item, true, GRADE_DISPLAY_TYPE_REAL);
          
          $timemodified =  time($sums2[$i]->timemodified);

          $sums2[$i]->finalgrade = $formatted_grade;

				$sums2[$i]->timemodified = $timemodified;

				$rdo[$i]['itemname'] = $sum->itemname;
				$rdo[$i]['finalgrade'] = $formatted_grade;
                                        $rdo[$i]['timemodified'] = $timemodified;
				$i++;
		}
		return $rdo;
		return $sums2;
	}

	return array();
    }

 

I have trouble with the $timemodified variable, I am not sure how to tell the code to display the time when the grade was given (or the test taken) and not the current time.

 

I hope I made myseld clear, I am happy to provide further info of needed.

Thank you for your answers!!!

[evergrey]

hello, sorry to post a new post, but I can't seem to find the edit button.

 

I have figured it out!!! It works now, but now I am not sure how to display the time in format like 2009-12-3 and not like 1302145207 which is what is written in the db.

 

Here is the working code:

function get_user_grades ($user,$cid) 
{
        global $CFG, $DB;

	$user = utf8_decode ($user);
        $user = get_complete_user_data ('username', $user);
        $uid = $user->id;


	$SQL = "SELECT g.itemid, g.finalgrade,gi.courseid,gi.itemname,gi.id, g.timemodified
                      FROM {$CFG->prefix}grade_items gi
                           JOIN {$CFG->prefix}grade_grades g      ON g.itemid = gi.id
                           JOIN {$CFG->prefix}user u              ON u.id = g.userid
                           JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id
                     WHERE g.finalgrade IS NOT NULL
		   AND u.id =  ?
		   AND gi.courseid = ?
                  GROUP BY g.itemid";

    $sum_array = array();
	$params = array ($uid, $cid);
    if ($sums = $DB->get_records_sql($SQL, $params)) 
	{
		$i = 0;
		$rdo = array ();
		foreach ($sums as $sum)
		{
			 if (! $grade_grade = grade_grade::fetch(array('itemid'=>$sum->id,'userid'=>$uid))) {
				$grade_grade = new grade_grade();
				$grade_grade->userid = $this->user->id;
				$grade_grade->itemid = $grade_object->id;

          }

				$grade_item = $grade_grade->load_grade_item();

				$sums2[$i] = $sum;
				$scale = $grade_item->load_scale();
				$formatted_grade = grade_format_gradevalue($sums2[$i]->finalgrade, &$grade_item, true, GRADE_DISPLAY_TYPE_REAL);
          
        
         
          $sums2[$i]->finalgrade = $formatted_grade;


				$rdo[$i]['itemname'] = $sum->itemname;
				$rdo[$i]['finalgrade'] = $formatted_grade;
          $rdo[$i]['timemodified'] = $sum->timemodified;
				$i++;
		}
		return $rdo;
		return $sums2;
	}

	return array();
    }

 

I think I know how to write the code but I am not sure where to add it.

 

Thank you again!!!

 

[Halldor]

You can use the function date.

 

E.g. date(d/m/Y, $timestamp);  (where $timestamp includes your timestamp of course  )

[evergrey]

Thank you for your answer! It worked for me

 

The last thing for now I am trying to achieve is to display feedback as well.

 

There is a feedback column in the same table in the database as the rest of the items,

but for some reason I can't display it.

 

I get this error:

 

Invalid external api response: feedback (Invalid external api response)

 

There is an error in the code somewhere here:

 

$rdo[$i]['feedback'] = $sum->feedback;

 

I use the same way I used for displaying the time but I guess  I would have to find another way to display the feedback as well.

Thanks for any input and hints.

[Halldor]

I get this error:

 

Invalid external api response: feedback (Invalid external api response)

 

There is an error in the code somewhere here:

 

$rdo[$i]['feedback'] = $sum->feedback;

 

 

You should that a look at the code inside $sum and the call feedback. I think I'll need more code to help you with that.

[evergrey]

ok, basically I have this setup:

 

the code is divided into three separate files, one handles the information from Moodle, second translates it to Joomla and the third displays what I want on the site.

 

The first part of code is above.

 

The second part of the code is this:

/* get_user_grades */
    public static function get_user_grades_parameters() {
        return new external_function_parameters(
                        array(
                            'user' => new external_value(PARAM_TEXT, 'username'),
                            'id' => new external_value(PARAM_INT, 'course id'),
                        )
        );
    }

    public static function get_user_grades_returns() {
	 return new external_multiple_structure(
			new external_single_structure(
				array(
					'itemname' => new external_value(PARAM_TEXT, 'item name'),
					'finalgrade' => new external_value(PARAM_TEXT, 'final grade'),
					'timecreated' => new external_value(PARAM_INT, 'timecreated'),
					'feedback' => new external_value(PARAM_TEXT, 'feedback'),
				)
			)
            );
    }

    public static function get_user_grades($user, $id) {
        global $CFG, $DB;

        $params = self::validate_parameters(self::get_user_grades_parameters(), array('user' => $user, 'id'=>$id));

	$auth = new  auth_plugin_joomdle ();
	$return = $auth->get_user_grades ($user, $id);


        return $return;
    }

 

The last code is regular HTML with this line of code in it:

 

 <?php echo $tarea['feedback']; ?>

 

The problem arrives after I typed the feedback option into the second part of the code, that is when I received the API error.