PHP queryDB函数代码示例

function login($username, $mdp)
{
    session_start();
    $link = connectDB();
    // Requête qui va chercher dans la BDD la ligne qui correspond
    // à la combinaison utilisateur/mot de passe
    $query = 'SELECT id_Utilisateur
                  FROM Utilisateur
                  WHERE nom_Utilisateur = "' . mysqli_real_escape_string($link, $username) . '" AND
                        MDP_Utilisateur = "' . mysqli_real_escape_string($link, HashPassword($mdp)) . '"';
    $row = queryDB($query);
    // Si une seule combinaison utilisateur/mdp ressort de la requête,
    // on le connecte
    if (count($row) == 1) {
        // Requête pour inserer l'id de l'utilisateur dans la table de connexion
        $query = "INSERT INTO Connexion(User_Connexion)\n                      VALUES (" . $row['id_Utilisateur'] . ")";
        queryDB($query);
        // On met en variables de session
        // Que l'utilisateur est connecté
        $_SESSION['isloged'] = true;
        // Son pseudo
        $_SESSION['user'] = $username;
        // Son id
        $_SESSION['id_user'] = $row['id_Utilisateur'];
        return true;
    } else {
        // Login Not Ok
        $_SESSION['isloged'] = false;
        return false;
    }
}

/**
 * Extending the test dates to make them accessible to Calendar Module
 * @param     :    Course id, Member id
 * @return    :    array (test start and end dates) in format that can be used by fullcalendar
 * @author    :    Anurup Raveendran, Herat Gandhi
 */
function tests_extend_date($member_id, $course_id)
{
    $tests = array();
    // get course title
    $sql = "SELECT title  FROM %scourses  WHERE course_id = %d";
    $row = queryDB($sql, array(TABLE_PREFIX, $course_id), TRUE);
    $course_title = $row['title'];
    $sql = "SELECT title,test_id,start_date,end_date FROM %stests WHERE course_id = %d";
    $rows_tests = queryDB($sql, array(TABLE_PREFIX, $course_id));
    if (count($rows_tests) > 0) {
        $index = 0;
        foreach ($rows_tests as $row) {
            if (strpos($row['start_date'] . '', '0000-00-00') === false) {
                $unix_ts = strtotime($row['start_date']);
                $time = date('h:i A', $unix_ts);
                $tests[$index] = array("id" => rand(20000, 25000) . '', "title" => _AT('calendar_test_start') . $row['title'], "start" => $row['start_date'], "end" => $row['start_date'], "allDay" => false, "color" => 'lime', "textColor" => 'black', "editable" => false);
                $unix_ts = strtotime($row['end_date']);
                $time = date('h:i A', $unix_ts);
                $index++;
            }
            if (strpos($row['end_date'] . '', '0000-00-00') === false) {
                $tests[$index] = array("id" => rand(20000, 25000) . '', "title" => _AT('calendar_test_end') . $row['title'], "start" => $row['end_date'], "end" => $row['end_date'], "allDay" => false, "color" => 'purple', "textColor" => 'white', "editable" => false);
                $index++;
            }
        }
    }
    return $tests;
}

/**
 * given an owner_type and owner_id
 * returns false if user cannot read or write to this workspace
 * returns WORKSPACE_AUTH_READ if the user can read
 * returns WORKSPACE_AUTH_WRITE if the user can write
 */
function ad_authenticate($owner_id)
{
    if (authenticate(AT_PRIV_ASSIGNMENTS, AT_PRIV_RETURN)) {
        // instructors have read only access to assignments
        return true;
    } else {
        // students have read access to their own assignments
        $sql = "SELECT COUNT(*) cnt FROM %sfiles\n\t\t         WHERE owner_id = %d\n                   AND owner_type= %d\n                   AND member_id = %d";
        $row = queryDB($sql, array(TABLE_PREFIX, $owner_id, WORKSPACE_ASSIGNMENT, $_SESSION['member_id']), TRUE);
        if ($row['cnt'] > 0) {
            return true;
        }
        // enrolled students can submit the assignments that assign to him/her
        if ($_SESSION['member_id'] && $_SESSION['enroll']) {
            // assignments that are assigned to all students
            $sql = "SELECT count(*) cnt FROM %sassignments \n                     WHERE assignment_id = %d\n                       AND assign_to=0 \n                       AND course_id=%d";
            $row = queryDB($sql, array(TABLE_PREFIX, $owner_id, $_SESSION['course_id']), TRUE);
            if ($row['cnt'] > 0) {
                return true;
            }
            // assignments that are assigned to a group,
            // and this group has "file storage" tool available
            // and the student is in this group
            $groups_list = implode(',', $_SESSION['groups']);
            // the groups that the student belongs to
            $sql = "SELECT count(*) cnt\n\t\t              FROM %sgroups_types gt, %sgroups g, %sassignments a\n\t\t             WHERE g.group_id in (%s)\n\t\t               AND g.group_id in (SELECT group_id FROM %sfile_storage_groups)\n\t\t               AND g.type_id = gt.type_id\n\t\t               AND gt.course_id = %d\n\t\t               AND gt.type_id = a.assign_to\n\t\t               AND a.assignment_id = %d";
            $row = queryDB($sql, array(TABLE_PREFIX, TABLE_PREFIX, TABLE_PREFIX, $groups_list, TABLE_PREFIX, $_SESSION['course_id'], $owner_id), TRUE);
            if ($row['cnt'] > 0) {
                return true;
            }
        }
    }
    return false;
}

function make_cache_file($feed_id)
{
    static $rss;
    if (!isset($rss)) {
        require_once AT_INCLUDE_PATH . '../mods/_standard/rss_feeds/classes/lastRSS.php';
        $rss = new lastRSS();
        $rss->cache_dir = AT_CONTENT_DIR . 'feeds/';
        $rss->num_results = AT_FEED_NUM_RESULTS;
        $rss->description = AT_FEED_SHOW_DESCRIPTION;
    }
    $sql = "SELECT url, feed_id FROM %sfeeds WHERE feed_id=%d";
    $row_feeds = queryDB($sql, array(TABLE_PREFIX, $feed_id), TRUE);
    if (count($row_feeds) > 0) {
        $output = $rss->get($row['url'], $row['feed_id']);
        $cache_file = AT_CONTENT_DIR . 'feeds/' . $feed_id . '_rss.cache';
        if ($f = @fopen($cache_file, 'w')) {
            fwrite($f, $output, strlen($output));
            fclose($f);
        }
        return 0;
    } else {
        $output = $rss->get($_POST['url'], 0);
        return $output;
    }
}

 function export($sql, $course_id)
 {
     global $db;
     $sql = str_replace('?', $course_id, $sql);
     $content = '';
     $result = queryDBresult($sql, array());
     $rows_csv = queryDB($sql, array(), '', '', '', MYSQL_NUM);
     $field_types = $this->detectFieldTypes($result);
     if (!$field_types) {
         return FALSE;
     }
     $num_fields = count($field_types);
     foreach ($rows_csv as $row) {
         for ($i = 0; $i < $num_fields; $i++) {
             if ($types[$i] == 'int' || $types[$i] == 'real') {
                 $content .= $row[$i] . ',';
             } else {
                 $content .= $this->quote($row[$i]) . ',';
             }
         }
         $content = substr($content, 0, -1);
         $content .= "\n";
     }
     at_free_result($result);
     return $content;
 }

/**
 * Extending the assignment dates to make them accessible to Calendar Module
 * @param     :    Course id, Member id
 * @return    :    array (assignment due and cut off dates) in format that can be used by fullcalendar
 * @author    :    Anurup Raveendran, Herat Gandhi
 */
function assignments_extend_date($member_id, $course_id)
{
    //global $db;
    $assignments = array();
    // get course title
    $sql = "SELECT title  FROM %scourses  WHERE course_id = %d";
    $row = queryDB($sql, array(TABLE_PREFIX, $course_id), TRUE);
    $course_title = $row['title'];
    $sql = "SELECT assignment_id,title,date_due,date_cutoff FROM %sassignments WHERE course_id = %d";
    $rows_courses = queryDB($sql, array(TABLE_PREFIX, $course_id));
    $row_count = count($rows_courses);
    if ($row_count > 0) {
        $index = 0;
        foreach ($rows_courses as $row) {
            $assignment_id = $row['assignment_id'];
            $unix_ts = strtotime($row['date_due']);
            $time = date('h:i A', $unix_ts);
            if (strpos($row['date_due'] . '', '0000-00-00') === false) {
                $assignments[$index] = array("id" => rand(5000, 9000) . '', "title" => _AT('calendar_assignment_due') . $row['title'], "start" => $row['date_due'], "end" => $row['date_due'], "allDay" => false, "color" => 'yellow', "textColor" => 'black', "editable" => false);
                $unix_ts = strtotime($row['date_cutoff']);
                $time = date('h:i A', $unix_ts);
                $index++;
            }
            if (strpos($row['date_cutoff'] . '', '0000-00-00') === false) {
                $assignments[$index] = array("id" => rand(5000, 9000) . '', "title" => _AT('calendar_assignment_cut') . $row['title'], "start" => $row['date_cutoff'], "end" => $row['date_cutoff'], "allDay" => false, "color" => 'red', "textColor" => 'white', "editable" => false);
                $index++;
            }
        }
    }
    return $assignments;
}

function forums_news()
{
    require_once AT_INCLUDE_PATH . '../mods/_standard/forums/lib/forums.inc.php';
    global $db, $enrolled_courses, $system_courses;
    $news = array();
    if ($enrolled_courses == '') {
        return $news;
    }
    $sql = 'SELECT E.approved, E.last_cid, C.* FROM ' . TABLE_PREFIX . 'course_enrollment E, ' . TABLE_PREFIX . 'courses C WHERE C.course_id in ' . $enrolled_courses . '  AND E.member_id=' . $_SESSION['member_id'] . ' AND E.course_id=C.course_id ORDER BY C.title';
    $rows_en_courses = queryDB($sql, array());
    if (count($rows_en_courses) > 0) {
        foreach ($rows_en_courses as $row) {
            $all_forums = get_forums($row['course_id']);
            if (is_array($all_forums)) {
                foreach ($all_forums as $forums) {
                    if (is_array($forums)) {
                        foreach ($forums as $forum_obj) {
                            $forum_obj['course_id'] = $row['course_id'];
                            $link_title = $forum_obj['title'];
                            $news[] = array('time' => $forum_obj['last_post'], 'object' => $forum_obj, 'alt' => _AT('forum'), 'thumb' => 'images/pin.png', 'course' => $system_courses[$row['course_id']]['title'], 'link' => '<a href="bounce.php?course=' . $row['course_id'] . SEP . 'pu=' . urlencode('mods/_standard/forums/forum/index.php?fid=' . $forum_obj['forum_id']) . '"' . (strlen($link_title) > SUBLINK_TEXT_LEN ? ' title="' . AT_print($link_title, 'forums.title') . '"' : '') . '>' . AT_print(validate_length($link_title, SUBLINK_TEXT_LEN, VALIDATE_LENGTH_FOR_DISPLAY), 'forums.title') . '</a>');
                        }
                    }
                }
            }
        }
    }
    return $news;
}

function file_storage_news()
{
    global $enrolled_courses, $system_courses;
    $news = array();
    if ($enrolled_courses == '') {
        return $news;
    }
    // As personal files are listed in any enrolled courses of the student,
    // randomly pick one course for bouce.php
    $end_of_first_course = strpos($enrolled_courses, ",") - 1;
    $any_one_enrolled_course = substr($enrolled_courses, 1, $end_of_first_course ? $end_of_first_course : -1);
    $sql = "(SELECT date, file_id, file_name, owner_id course_id, description \n\t           FROM " . TABLE_PREFIX . "files \n\t          WHERE owner_type = " . WORKSPACE_COURSE . " AND owner_id IN " . $enrolled_courses . ")\n\t        UNION\n\t        (SELECT date, file_id, file_name, " . $any_one_enrolled_course . " course_id, description \n\t           FROM " . TABLE_PREFIX . "files\n\t          WHERE owner_type = " . WORKSPACE_PERSONAL . " AND owner_id = " . $_SESSION['member_id'] . ")\n\t        UNION\n\t        (SELECT f.date, f.file_id, f.file_name, gt.course_id, f.description \n\t           FROM " . TABLE_PREFIX . "files f, " . TABLE_PREFIX . "groups g, " . TABLE_PREFIX . "groups_types gt\n\t          WHERE owner_type = " . WORKSPACE_GROUP . " \n\t            AND f.owner_id = g.group_id \n\t            AND g.type_id = gt.type_id \n\t            AND gt.course_id IN " . $enrolled_courses . "\n\t            AND " . $_SESSION['member_id'] . " in \n\t               (select member_id \n\t                from " . TABLE_PREFIX . "groups_members gm \n\t                where gm.group_id = g.group_id))\n\t         ORDER BY date DESC";
    $rows_files = queryDB($sql, array());
    if (count($rows_files) > 0) {
        foreach ($rows_files as $row) {
            if ($row['description'] != "") {
                $filetext = $row['description'];
            } else {
                $filetext = $row['file_name'];
            }
            $sql = "SELECT course_id, home_links, main_links from %scourses WHERE course_id = %d";
            $row2 = queryDB($sql, array(TABLE_PREFIX, $row['course_id']), TRUE);
            // check if course has file storage enabled
            if (strstr($row2['home_links'], 'file_storage') || strstr($row2['main_links'], 'file_storage')) {
                $news[] = array('time' => $row['date'], 'object' => $row, 'course' => $system_courses[$row['course_id']]['title'], 'alt' => _AT('download'), 'thumb' => 'images/application_get.png', 'link' => '<a href="bounce.php?course=' . $row['course_id'] . SEP . 'p=' . urlencode('mods/_standard/file_storage/index.php?download=1' . SEP . 'files[]=' . $row['file_id']) . '"' . (strlen($filetext) > SUBLINK_TEXT_LEN ? ' title="' . AT_print($filetext, 'input.text') . '"' : '') . '>' . AT_print(validate_length($filetext, SUBLINK_TEXT_LEN, VALIDATE_LENGTH_FOR_DISPLAY), 'input.text') . '</a>');
            }
        }
    }
    return $news;
}

function file_storage_delete_group($group_id)
{
    $sql = "DELETE FROM %sfile_storage_groups WHERE group_id=%d";
    $result = queryDB($sql, array(TABLE_PREFIX, $group_id));
    require_once AT_INCLUDE_PATH . '../mods/_standard/file_storage/file_storage.inc.php';
    fs_delete_workspace(WORKSPACE_GROUP, $group_id);
}

function reading_list_delete($course)
{
    $sql = "DELETE FROM %sreading_list WHERE course_id=%d";
    queryDB($sql, array(TABLE_PREFIX, $course));
    $sql = "DELETE FROM %sexternal_resources WHERE course_id=%d";
    queryDB($sql, array(TABLE_PREFIX, $course));
}

function blogs_news()
{
    global $db, $enrolled_courses, $system_courses;
    $news = array();
    if ($enrolled_courses == '') {
        return $news;
    }
    $sql = "SELECT G.group_id, G.title, G.modules, T.course_id FROM %sgroups G INNER JOIN %sgroups_types  T USING (type_id) WHERE T.course_id IN %s ORDER BY G.title";
    $rows_enrolled = queryDB($sql, array(TABLE_PREFIX, TABLE_PREFIX, $enrolled_courses));
    if (count($rows_enrolled) > 0) {
        foreach ($rows_enrolled as $row) {
            if (strpos($row['modules'], '_standard/blogs') !== FALSE) {
                // check for group membership before showing news.
                $sql = "SELECT member_id FROM %sgroups_members WHERE member_id=%d AND group_id= %d";
                $row_group_member = queryDB($sql, array(TABLE_PREFIX, $_SESSION['member_id'], $row['group_id']), TRUE);
                // check for course instructor, show blog news if so
                $sql = "SELECT member_id from %scourses WHERE member_id =%d";
                $row_instructor = queryDB($sql, array(TABLE_PREFIX, $_SESSION['member_id']));
                if (count($row_group_member) > 0 || count($row_instructor) > 0) {
                    // retrieve the last posted date/time from this blog
                    $sql = "SELECT MAX(date) AS date FROM %sblog_posts WHERE owner_type=%d AND owner_id=%d";
                    $row2 = queryDB($sql, array(TABLE_PREFIX, BLOGS_GROUP, $row['group_id']), TRUE);
                    $last_updated = ' - ' . _AT('last_updated', AT_date(_AT('forum_date_format'), $row2['date'], AT_DATE_MYSQL_DATETIME));
                    $link_title = $row['title'];
                    $news[] = array('time' => $row2['date'], 'object' => $row, 'alt' => _AT('blogs'), 'course' => $system_courses[$row['course_id']]['title'], 'thumb' => 'images/home-blogs_sm.png', 'link' => '<a href="bounce.php?course=' . $row['course_id'] . SEP . 'p=' . urlencode('mods/_standard/blogs/view.php?ot=' . BLOGS_GROUP . SEP . 'oid=' . $row['group_id']) . '"' . (strlen($link_title) > SUBLINK_TEXT_LEN ? ' title="' . AT_print($link_title, 'blog_posts.title') . '"' : '') . '>' . AT_print(validate_length($link_title, SUBLINK_TEXT_LEN, VALIDATE_LENGTH_FOR_DISPLAY), 'blog_posts.title') . '</a>');
                }
            }
        }
    }
    return $news;
}

function categoryNameList()
{
    // Requête pour récuperer la liste de noms
    $req = 'SELECT intitule_Categorie
  				FROM Categorie';
    // On retourne la liste
    return queryDB($req);
}

function enrolment_delete($course)
{
    global $db;
    $sql = "DELETE FROM %scourse_enrollment WHERE course_id=%d";
    $result = queryDB($sql, array(TABLE_PREFIX, $course));
    $sql = "DELETE FROM %sauto_enroll_courses WHERE course_id=%d";
    $result = queryDB($sql, array(TABLE_PREFIX, $course));
}

function getAtualSeason()
{
    $date = date("Y-m-d");
    $sql = "select id_season from tb_season\n\twhere '{$date}' between dt_start and dt_end";
    $rs = queryDB($sql);
    $row = mysql_fetch_assoc($rs);
    return $row['id_season'];
}

function backups_delete($course)
{
    global $db;
    $path = AT_BACKUP_DIR . $course . '/';
    clr_dir($path);
    $sql = "DELETE FROM %sbackups WHERE course_id=%d";
    $result = queryDB($sql, array(TABLE_PREFIX, $course));
}