displaying data from mysql in a sequence

[Riu 2009]

Please Help!!

i have been trying to get some help on displaying data from mysql in a sequence (like a course). basically i want my student to view and learn the couurse that has been saved in mysql database in learningmaterial table, the table contains images, audio files, online documents like pdf and ppt, video files (from youtube, flickr, slideshare,picase vimeo etc) and local files as well.. i have an order field in the table as well to view all the material in the required order.. now i want to let my student view all the stuff with their descriptions as well (stored in the descrition field) and iam wondering how the student would view all the course material and learn it... i dont want to show the material in the table..

hope i've explained my question clearly enough, any help would be appreciated. Thanks in advance :)

This sounds like a job for a separate progress table:

(prog_id) | user_id | course_part_id | date_completed

With the use of joined tables you can track the user's progress and only make the relevant part of the course available, e.g. all past stuff and the direct next.

[Riu 2009]

yes for tracking progress i would need another table. but @diafol please tell me how would i fetch the material (as urls are saved in the table and internal files' paths as well)
and display the course material in a fine and nice way??? thankyou for ur response :)

... display the course material in a fine and nice way

No idea. But you could have a template page for the course, for example:

<?php 
//I'd place my retrieving and formatting functions inside classes - you don't have to, you could just have a 'functions.php' file

include(class.user.php);
include(class.progressmanager.php);
include(class.resource.php);

$user = new User();

$progress = new ProgressManager($user);
$course_part_id = $progress->getRequest(); 

$resource = new Resource($course_part_id);

$title = $progress->getTitle();
$prev = $progress->getPrev();
$next = $progress->nextPrev();
$name = $user->getName('firstname');
$percent_complete = $progress->getPcComplete();
$main_resource = $resource->getMainResource();
(etc)

//if using Twig templating engine for example, you could then go to it to do this:

<h1>{{ course_part_id }}: {{ title }}</h1>
<p>{{ name }} you have completed {{ percent_complete }}% of the course to date.</p>

<div id="main_resource">
{{ main_resource }}
</div>

I have no idea if that's the sort of thing you need?
THe class methods here are pretty simple affairs, they'd just query to DB for data and return it. Twig's {{ }} just mean 'echo this variable'. Of course you could do it without any templating engine at all, and just use natives:

<h1><?php echo $course_part_id;?>: <?php echo $title;?></h1>
<p><?php echo $name;?> you have completed <?php echo $percent_complete;?>% of the course to date.</p>
(etc)

I assume that you're able to query your database and extract your resources, e.g. YT videos?

[Riu 2009]

@diafol thankyou so much sir, but i think iam not explaining my question clearly.. iam sorry for that...

i want to retrieve the urls that are saved in DB and want to embed them in my webpage
as urls are from different services so im getting confuse how would i use one embed code for displaying data of different sites like youtube,vimeo,dailymotion,flickr, picasa,slideshare etc..

i want to retrieve the url that is saved in DB and display (embed) the material from that url into my web page..

i hope u get my question now.. sorry for bothering you... i know im bad at explaining questions.. :(

You simply store the url with a code column (resource_type) in your resources table such as:

resource_id | resource_url | resource_type | description | label

The resource type is the key to how your resource is displayed. It can be set to an integer field and linked to a resource_types table like this:

resource_type (PK, int) | label (varchar, e.g. 'Youtube Video', 'Vimeo Video') | template (text)

The resource label and resource_type: v useful for producing a dropdown when adding or editing items -> DB.

The template comes into its own when you use JOINS to retrieve the resource:

SELECT r.resource_url, r.description, r.label, t.template FROM resources AS r INNER JOIN resource_types AS t ON r.resource_type = t.resource_type WHERE r.resource_id = $res_id

A template field item could be something like this for YouTube:

<iframe width="{width}" height="{height}" src="http://www.youtube.com/embed/{url}" frameborder="0" allowfullscreen></iframe>

You'd then use this for a base for the url, e.g. 'mUAxHa6arWc'

I'd suggest that the width and height are kept out of the template so that they can be set dynamically.
You can replace the template string with a simple str_replace() - no need for a preg_replace.

Slideshare could be:

<iframe src="http://www.slideshare.net/slideshow/embed_code/{url}" width="{width}" height="{height}" frameborder="0" marginwidth="0" marginheight="0" scrolling="no" style="border:1px solid #CCC;border-width:1px 1px 0;margin-bottom:5px" allowfullscreen> </iframe>

A typical {url} for slideshare would be '14996307'
As you can see the urls mentioned here aren't really urls, but codes.

[Riu 2009]

@diafol thankyou very much, despite of whether my work is done or not,, ur comments are really helpful, thanx alot :)
yes some how iam saving the data like u told

table name:learningmaterial
url=full url of video,image,ppt,pdf etc
source= internal or external (if internal it allows to upload file manually..if external just paste the url and external_id)
description=description of content
external_id=this is the unique code from urls like '14996307'
courses_id=from which course it belongs
type=i have dropdown list for source (utube,vimeo....etc)
sequence=in which sequence the material is shown (ordering the material from 1-20)
duration= file duration if its video or audio
internal_path= a column which saves the path of the directory in which the manual upload files are stored
as i have already made the table, and iam new to php its difficult for me to understand some other table..can i get my work done from this table i have shown below??

so i would need different iframes for different services??
and what should i add to this DB table??

*******************************************************************************
*id*title*url*source*description*external_id*courses_id*type*sequence*duration* 
*  *     *   *      *           *           *          *    *        *        *
*  *     *   *      *           *           *          *    *        *        *
*  *     *   *      *           *           *          *    *        *        *
*  *     *   *      *           *           *          *    *        *        *
*  *     *   *      *           *           *          *    *        *        *
*******************************************************************************

No prob. You have the 'type' info, so you can apply a php string template for iframes or players:

function getEmbedSource($url,$type,$width,$height){
    switch ($type){
       case 'utube':     
          $output = "<iframe width='$width' height='$height' src='http://www.youtube.com/embed/$url' frameborder="0" allowfullscreen></iframe>";  
            break;
       ...     
    }
    return $output;
}

$resource = getEmbedSource($url,$type,$width,$height);

[Riu 2009]

thankyou so much it helped me a lot.. thankyou :)