Accessing Course Files via PHP and MySQL

Accessing Course Files via PHP and MySQL

by Frank Troglauer -
Number of replies: 11

I am trying to access an image for a course that I uploaded to the course "Course Summary Files" section. I found a post on Stack Overflow which seemed like it would help, but doesn't return anything for me. 

https://stackoverflow.com/questions/15938185/selecting-all-the-files-along-with-their-paths-of-a-course-in-moodle

The query in SQL that they recommend is this. 

SELECT
    mdl_files.source,
    mdl_files.filename,
    mdl_files.contenthash,
    mdl_context.path,
    mdl_resource.course,
    mdl_resource.name,
    mdl_course.fullname, 
    mdl_course.shortname
FROM
    mdl_files
INNER JOIN mdl_context ON mdl_files.contextid = mdl_context.id
INNER JOIN mdl_resource ON mdl_context.instanceid = mdl_resource.id
INNER JOIN mdl_course ON mdl_resource.course = mdl_course.id
WHERE (mdl_course.fullname = 'XXXX')

That query returns 0 results for me because when I query the mdl_resources table it is entirely empty. Any idea why this may be or another way I can query the files for a course to try and get a course image? I would be up for any ideas on how I could customize the course or another way of going about this that doesn't require modifications to the Moodle core code. I am just wanting to find a way to display an image for course when users go to access it so they have a visual representation for the course. 

Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Gareth J Barnard -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Use the File API instead: https://docs.moodle.org/dev/File_API.

Average of ratings: -
In reply to Gareth J Barnard

Re: Accessing Course Files via PHP and MySQL

by Frank Troglauer -

Thanks for pointing me in sort of the right direction Gareth. Unfortunately, I haven't been 100% successful with it. Nowhere on that page does it talk about getting the base information for the queries on that page. Thankfully I found this post that had some hints at what I wanted. 

https://moodle.org/mod/forum/discuss.php?d=259458

From that page, I built my query. 

$sql = "SELECT f.id, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename

          FROM mdl_context cx

          JOIN mdl_course c ON cx.instanceid=c.id

          JOIN mdl_files f ON cx.id=f.contextid

          WHERE f.filename <> '.'

          AND f.component = 'course'

          AND c.id = $cid ";

  $result = $DB->get_records_sql($sql);

  

When that runs I get this. 

array (size=1)
  29669 => 
    object(stdClass)[264]
      public 'id' =>  '29669' (length=5)
      public 'contextid' =>  '96' (length=2)
      public 'component' =>  'course' (length=6)
      public 'filearea' =>  'overviewfiles' (length=13)
      public 'itemid' =>  '0' (length=1)
      public 'filepath' =>  '/' (length=1)
      public 'filename' =>  'CoursePhoto.jpg' (length=15)


Which is great because it is all of the information needed for some of the FileAPI scripts. The problem is I don't know how to build the link to display the file to users when they load a page. According to the warnings for the FileAPI Read section I should be using the File Browser instead. Here is my code showing use of file storage and file browser. 


if(count($result) == 0){

    return "images/centrifugal%20pump%2011.png";

  }else{

    $fs = get_file_storage();

    $fb = new file_browser();

    foreach($result as $key=>$value){

      $url = $fs->get_file($value->contextid, $value->component, $value->filearea, $value->itemid, $value->filepath, $value->filename);

      $context = context::instance_by_id($value->contextid);

      $url2 = $fb->get_file_info($context, $value->component, $value->filearea, $value->itemid, $value->filepath, $value->filename);

    }


$url var_dumps to this. 

object(stored_file)[262]
  private 'fs' => 
    object(file_storage)[253]
      private 'filedir' =>  'D:\ABC\wamp\moodledata2/filedir' (length=31)
      private 'trashdir' =>  'D:\ABC\wamp\moodledata2/trashdir' (length=32)
      private 'tempdir' =>  'D:\ABC\wamp\moodledata2/temp/filestorage' (length=40)
      private 'dirpermissions' =>  '511' (length=3)
      private 'filepermissions' =>  '438' (length=3)
  private 'file_record' => 
    object(stdClass)[266]
      public 'id' =>  '29669' (length=5)
      public 'contenthash' =>  'c99973ab39ab64550229ad896bcb31b07a4136d5' (length=40)
      public 'pathnamehash' =>  '6090dd889624ac111dfa4027bc218cbbbd339218' (length=40)
      public 'contextid' =>  '96' (length=2)
      public 'component' =>  'course' (length=6)
      public 'filearea' =>  'overviewfiles' (length=13)
      public 'itemid' =>  '0' (length=1)
      public 'filepath' =>  '/' (length=1)
      public 'filename' =>  'CoursePhoto.jpg' (length=15)
      public 'userid' =>  '6' (length=1)
      public 'filesize' =>  '16385' (length=5)
      public 'mimetype' =>  'image/jpeg' (length=10)
      public 'status' =>  '0' (length=1)
      public 'source' =>  'centrifugal-pump-11b.jpg' (length=24)
      public 'author' =>  'Willy Wonka' (length=15)
      public 'license' =>  'allrightsreserved' (length=17)
      public 'timecreated' =>  '1511229957' (length=10)
      public 'timemodified' =>  '1511229964' (length=10)
      public 'sortorder' =>  '0' (length=1)
      public 'referencefileid' => null
      public 'repositoryid' => null
      public 'reference' => null
      public 'referencelastsync' => null
  private 'filedir' =>  'D:\ABC\wamp\moodledata2/filedir' (length=31)
  private 'repository' => null

$url2 var_dumps to null. 


So my main problem right now is I am not getting a URL that I can use to show the image stored in the course overviewfiles area. Any additional help with this would be greatly appreciated. 

Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Gareth J Barnard -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers
Just call 'get_file' with the correct parameters - https://docs.moodle.org/dev/File_API#Read_file - e.g.: https://github.com/gjb2048/moodle-format_grid/blob/master/editimage.php#L94.
Average of ratings: -
In reply to Gareth J Barnard

Re: Accessing Course Files via PHP and MySQL

by Frank Troglauer -

Gareth,

I didn't even attempt get_file because the documentation clearly states

    This is a way to read a file, equivalent to file_get_contents. Please note your are allowed to do this ONLY from mod/mymodule/* code, it is not acceptable to do this anywhere else. Other code has to use file_browser interface instead.

and the code I am writing that would use it would be in the /local/ directory. That is why I looked into the file_browser interface but couldn't figure out a proper method from there. 


Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Frank Troglauer -

Gareth,

After more research I finally gave in and decided what I wanted to do had to be done with either get_file or make_plugin_file_url. I stuck with the get_file method and ended up with this. 

_______________________________________

  $sql = "SELECT f.id, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename

          FROM mdl_context cx

          JOIN mdl_course c ON cx.instanceid=c.id

          JOIN mdl_files f ON cx.id=f.contextid

          WHERE f.filename <> '.'

          AND f.component = 'course'

          AND c.id = $cid ";


  $result = $DB->get_records_sql($sql);


  if(count($result) == 0){

    return "images/centrifugal%20pump%2011.png";

  }else{

    $fs = get_file_storage();

    foreach($result as $key=>$value){

      $file = $fs->get_file($value->contextid, $value->component, $value->filearea, $value->itemid, $value->filepath, $value->filename);

      // $url3 = moodle_url::make_pluginfile_url($value->contextid, $value->component, $value->filearea, $value->itemid, $value->filepath, $value->filename);

    }

    $fileinfo = pathinfo($value->filename);

    $filename = $cid .'.'. $fileinfo['extension'];

    $filepath = '/local/courses/images/courses/';


    if(!file_exists($CFG->dirroot . $filepath . $filename)){

      $file->copy_content_to($CFG->dirroot . $filepath . $filename);

    }


    return $CFG->wwwroot . $filepath . $filename;

  }

__________________________________

My main concern is that I am copying the file with copy_content_to instead of serving it via some built-in Moodle method. To be 100% clear I just want to display the file to users, not send them the file for download. Is this the best way to tackle this?


On a side note how do I post code neatly in this forum? 

Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Gareth J Barnard -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Select the code, click on the 'AA' button and select 'pre-formatted'.

and for "To be 100% clear I just want to display the file to users, not send them the file for download. Is this the best way to tackle this?"

Use 'make_pluginfile_url' and echo the URL to users.  Examine how the Grid format works with its 'displayedimage'.

Average of ratings: -
In reply to Gareth J Barnard

Re: Accessing Course Files via PHP and MySQL

by Frank Troglauer -

Gareth,

Thanks for continuing to help with this. It is really appreciated!

I did try the 'make_pluginfile_url' which does make a url and when echoed out it looks like this. 

$url3 = moodle_url::make_pluginfile_url($value->contextid, $value->component, $value->filearea, $value->itemid, $value->filepath, $value->filename);
echo $url3;

http://localhost:8081/lms/pluginfile.php/96/course/overviewfiles/0/CoursePhoto.jpg 

However, if I try to put it into an img element it doesn't show anything and the console says the file cannot be found. 

echo "<img src='". $url3 ."'>";

Console errors

I took a look at your Grid Format plugin and it appears your doing the same thing I am but your using html_writer to create the html for display where I am just hard coding it. I did try it with the html_writer and got the same results. 

    $content = html_writer::empty_tag('img', array(
        'src' => $url3,
        'alt' => 'Course Image',
        'role' => 'img',
        'aria-label' => 'Course Image'));
    echo $content;

Course didn't display, just alt text.

Thanks for the heads up about the code formatting for forum posts!

Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Frank Troglauer -

Gareth, do you have any other suggestions for this and what I might be doing wrong?

Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Gareth J Barnard -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Hi Frank,

Sorry no, not without spending the time attempting to locally solve the problem of fetching the course summary file and displaying it.  Time = money.

Kind regards,

Gareth

Average of ratings: -
In reply to Frank Troglauer

Re: Accessing Course Files via PHP and MySQL

by Frank Troglauer -

I seem to have solved the problem. The issue with my old code is it was adding the itemid to the url and that was causing an invalid url. By doing some searching of other examples I found an article discussing the code in this github repo, for the flexibase theme, that helped with the solution. 

https://github.com/roelmann/moodle-theme_flexibase/blob/master/renderers/course_renderer.php#L171-L189

And the code. The course_get_overviewfiles function was borrowed from 


function course_get_overviewfiles($courseid) {
  global $CFG;
  if (empty($CFG->courseoverviewfileslimit)) {
      return array();
  }
  require_once($CFG->libdir. '/filestorage/file_storage.php');
  require_once($CFG->dirroot. '/course/lib.php');
  $fs = get_file_storage();
  $context = context_course::instance($courseid);
  $files = $fs->get_area_files($context->id, 'course', 'overviewfiles', false, 'filename', false);
  if (count($files)) {
      $overviewfilesoptions = course_overviewfiles_options($courseid);
      $acceptedtypes = $overviewfilesoptions['accepted_types'];
      if ($acceptedtypes !== '*') {
          // Filter only files with allowed extensions.
          require_once($CFG->libdir. '/filelib.php');
          foreach ($files as $key => $file) {
              if (!file_extension_in_typegroup($file->get_filename(), $acceptedtypes)) {
                  unset($files[$key]);
              }
          }
      }
      if (count($files) > $CFG->courseoverviewfileslimit) {
          // Return no more than $CFG->courseoverviewfileslimit files.
          $files = array_slice($files, 0, $CFG->courseoverviewfileslimit, true);
      }
  }
  return $files;
}
function course_image($courseid)
{
    global $CFG, $DB;
    $course_overview_files = course_get_overviewfiles($courseid);
    if($course_overview_files == null)
    {
        //Use default image
        return "images/course_default.png";
    }
    else if(is_array($course_overview_files) && count($course_overview_files) > 0)
    {
        $url = '';
        foreach($course_overview_files as $key=>$file)
        {
            if(strpos($file->get_filename(), 'CoursePhoto') !== false)
            {
                $isimage = $file->is_valid_image();
                $url = file_encode_url(
                    "$CFG->wwwroot/pluginfile.php",
                   '/'. $file->get_contextid(). '/'. $file->get_component(). '/'. $file->get_filearea(). $file->get_filepath(). $file->get_filename(),
                   !$isimage
                );
                break;
            }
        }
        // echo "<img src='$url'>";
        // echo "file exists = ". file_exists($url);
        //Last check to ensure it exists, otherwise use default image
        if($url != '')
        {
            return $url;
        }
        else
        {
            //Use default image
            return "images/course_default.png";
        }
    }
}


Average of ratings: -