PHP connect_to_database函数代码示例

function isRepeated($course, $username)
{
    list($dbc, $error) = connect_to_database();
    $repeated = false;
    $course_safe = mysqli_real_escape_string($dbc, $course);
    //protect ourselves
    $username_safe = mysqli_real_escape_string($dbc, $username);
    //protect ourselves
    //$now = new date();
    //echo $course_safe;
    //echo $username_safe;
    //echo $course;
    //echo $username;
    $results = mysqli_query($dbc, "select course_id from Enrollment where username= '{$username_safe}'");
    $allEnr = array();
    if ($results) {
        while ($enrollments = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
            $allEnr[] = $enrollments;
            //echo $enrollments;
        }
        foreach ($allEnr as $enr) {
            //echo $enr;
            //echo $enr['course_id'];
            //echo $course_safe;
            if ($enr['course_id'] == $course_safe) {
                $repeated = true;
            }
        }
        //echo $repeated;
        //if($repeated)
        //	p("You are already enrolled in this course");
    }
    return $repeated;
}

function users_checkAdmin($username)
{
    list($dbc, $error) = connect_to_database();
    $admin = false;
    if ($dbc) {
        $username_safe = mysqli_real_escape_string($dbc, $username);
        $query = "SELECT * from users where username='{$username_safe}' AND Type='professor'";
        $result = mysqli_query($dbc, $query);
        if ($result) {
            //aha we found you!
            while ($user = mysqli_fetch_array($result, MYSQLI_BOTH)) {
                $admin = true;
                /*
                				$_SESSION['user'] = $user;
                				
                					if ($user["Type"]=="professor"){
                				$admin = true;
                					}
                			
                					else{
                				//not admin
                					}
                */
            }
        } else {
            //noooo
        }
    }
    return $admin;
}

function query_registered_users()
{
    // Initialize the return code.
    $b_rc = false;
    // Initialize array to store query results in.
    $registered_users = array();
    // Open connection to the database.
    list($b_rc, $db_handle) = connect_to_database();
    // If return code is true proceed with data query.
    if ($b_rc) {
        // Set MySQL table to use.
        $sql_table = "data_submission";
        // Create MySQL query to request all registered user entries in descending
        // order by timestamp.
        $sql_query = "SELECT * FROM {$sql_table} ORDER BY timestamp DESC";
        // Perform database query.
        $result = mysql_query($sql_query, $db_handle);
        // Validate return code from query.
        check_result($result);
        // Fetch each registered user's information from the query results
        // and add it to an array that can be returned to the caller.
        while ($row = mysql_fetch_row($result)) {
            //echo "timestamp = $row[8]<br>";
            // Add user's information to array.
            $registered_users[] = $row;
        }
        // Free the query results.
        mysql_free_result($result);
    }
    // Return array consisting of status boolean and query data.
    return array($b_rc, $registered_users);
}

/**
 * @param $login
 * @param string $field
 * @return bool|mysqli_result|string
 */
function user_s($login, $field = "*")
{
    $result = '';
    list($dbc, $error) = connect_to_database();
    $sql = 'CALL `stu_user_s`("' . $login . '");';
    if ($dbc) {
        $queryResult = mysqli_query($dbc, $sql);
        while ($record = mysqli_fetch_array($queryResult, MYSQLI_ASSOC)) {
            switch (strtoupper($field)) {
                case "USERID":
                    $result = $record['UserId'];
                    break;
                case "NAME":
                    $result = $record['Name'];
                    break;
                case "LOGIN":
                    $result = $record['Login'];
                    break;
                case "EMAIL":
                    $result = $record['Email'];
                    break;
                case "LOCKED":
                    $result = $record['Locked'];
                    break;
                default:
                    $result = $record['UserId'] . ", " . $record['Name'] . ", " . $record['Login'] . ", " . $record['Email'] . ", " . $record['Locked'];
            }
        }
    }
    return $result;
}

/**
 * Wrapper function for the
 * stu_site_s stored procedure.
 *
 * @param string $field
 * @return string
 */
function site_select($field = "*")
{
    $site = array();
    $result = '';
    $i = 0;
    list($dbc, $error) = connect_to_database();
    $sql = 'CALL `stu_site_s`();';
    if ($dbc) {
        $resultSet = mysqli_query($dbc, $sql);
        while ($record = mysqli_fetch_array($resultSet, MYSQLI_ASSOC)) {
            switch (strtoupper($field)) {
                case "TITLE":
                    $result = $record['Title'];
                    break;
                case "SUBTITLE":
                    $result = $record['SubTitle'];
                    break;
                case "SALT":
                    $result = $record['Salt'];
                    break;
                default:
                    $result = $record['Title'] . ", " . $record['SubTitle'] . ", " . $record['Salt'];
                    break;
            }
        }
    }
    return $result;
}

function getDescription($course)
{
    $details = array();
    list($dbc, $error) = connect_to_database();
    if ($dbc) {
        $query = "SELECT * FROM Tbl_Courses WHERE Course_id = {$course};";
        $details = mysqli_query($dbc, $query);
    }
    //ya tengo los detalles. y ahora
    return $details;
}

function subject_getDetail($id)
{
    list($dbc, $error) = connect_to_database();
    if ($dbc) {
        $query = "SELECT subjects.Id, Code, Name, Building, Class, Professor, Semester FROM SUBJECTS ";
        $subject = NULL;
        $result = mysqli_query($dbc, $query);
        if ($result) {
            $subject = mysqli_fetch_array($result);
        }
        return $subject;
    }
}

function enrollments_getAll($username)
{
    list($dbc, $error) = connect_to_database();
    $username_safe = mysqli_real_escape_string($dbc, $username);
    //protect ourselves
    $results = mysqli_query($dbc, "select * from enrollments join subjects on enrollments.subject = subjects.id where username='{$username_safe}'");
    $allEnrollments = array();
    if ($results) {
        while ($enrollment = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
            $allEnrollments[] = $enrollment;
        }
    }
    return $allEnrollments;
}

function users_getAll()
{
    $users = array();
    list($dbc, $error) = connect_to_database();
    if ($dbc) {
        $query = "SELECT * FROM Tbl_Users;";
        $result = mysqli_query($dbc, $query);
        if ($result) {
            while ($user = mysqli_fetch_array($result)) {
                $users[] = $user;
            }
        }
    }
    return $users;
}

function students_getAll()
{
    $students = array();
    list($dbc, $error) = connect_to_database();
    if ($dbc) {
        $query = "SELECT users.Id, username, password, FirstName, LastName, Email, Type FROM USERS ";
        $result = mysqli_query($dbc, $query);
        if ($result) {
            while ($student = mysqli_fetch_array($result)) {
                $students[] = $student;
            }
        }
    }
    return $students;
}

/**
 * Test whether a given database
 * exists.
 *
 * @param $databaseName
 * @return bool
 */
function databaseExists($databaseName)
{
    $sql = $result = false;
    list($dbc, $error) = connect_to_database();
    $sql = 'SELECT CASE WHEN COUNT(*) LIKE \'1\' THEN TRUE ELSE FALSE END AS \'EXISTS\'' . 'FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = \'' . $databaseName . '\';';
    if ($dbc) {
        $queryResult = mysqli_query($dbc, $sql);
        while ($record = mysqli_fetch_array($queryResult, MYSQLI_ASSOC)) {
            if ($record['EXISTS']) {
                $result = true;
            }
        }
    }
    return $result;
}

function subjects_getAll()
{
    $subjects = array();
    list($dbc, $error) = connect_to_database();
    if ($dbc) {
        $query = "SELECT subjects.Id, Code, Name, Building, Class, Professor, Semester FROM SUBJECTS ";
        $result = mysqli_query($dbc, $query);
        if ($result) {
            while ($subject = mysqli_fetch_array($result)) {
                $subjects[] = $subject;
            }
        }
    }
    return $subjects;
}

function users_checkAdmin($username)
{
    list($dbc, $error) = connect_to_database();
    $admin = false;
    if ($dbc) {
        $username_safe = mysqli_real_escape_string($dbc, $username);
        //$password_safe = mysqli_real_escape_string($dbc,sha1($password + SALT));
        echo $username_safe;
        //echo $password_safe;
        $query = "SELECT * from Tbl_Users where username='{$username_safe}' AND role='Professor';";
        $result = mysqli_query($dbc, $query);
        ///$allrols = array();
        if ($result) {
            while ($rols = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
                $admin = true;
            }
        }
    }
    return $admin;
}

function getCourseDetails($courseId)
{
    $result = array();
    list($dbc, $error) = connect_to_database();
    $sql = 'CALL `stu_course_detail_s`("' . $courseId . '");';
    if ($dbc) {
        array_push($result, '<table border="1">');
        array_push($result, '<tr><th>CourseNumber</th><th>Description</th><th>Credits</th><th>Cost</th><th>Specialization</th><th></th></tr>');
        $resultSet = mysqli_query($dbc, $sql);
        while ($record = mysqli_fetch_array($resultSet, MYSQLI_ASSOC)) {
            array_push($result, '<tr><td>' . $record['CourseNumber'] . '</td>');
            array_push($result, '<td>' . $record['CourseDescription'] . '</td>');
            array_push($result, '<td>' . $record['Credits'] . '</td>');
            array_push($result, '<td>' . $record['Cost'] . '</td>');
            array_push($result, '<td>' . $record['Specialization'] . '</td>');
            array_push($result, '<td><a href="index.php?enroll=' . $record['CourseId'] . '">Enroll</a></td></tr>');
        }
        array_push($result, '</table>');
    }
    return $result;
}

/**
* Check if a user exists in the database, adds user to session if exists
*/
function users_checkExists($username, $password)
{
    list($dbc, $error) = connect_to_database();
    $success = false;
    if ($dbc) {
        $username_safe = mysqli_real_escape_string($dbc, $username);
        $password_safe = mysqli_real_escape_string($dbc, sha1($password . SALT));
        //$password_safe = mysqli_real_escape_string($dbc,sha1($password));
        $query = "SELECT * from users where username='{$username_safe}' AND password='{$password_safe}'";
        $result = mysqli_query($dbc, $query);
        if ($result) {
            //aha we found you!
            while ($user = mysqli_fetch_array($result, MYSQLI_BOTH)) {
                $_SESSION['user'] = $user;
                $success = true;
            }
        } else {
            //bad, wrong username or password
        }
    }
    return $success;
}