PHP DataBase::execute方法代码示例

		public function insertNews() {
			$db = new DataBase();
			//$db->setClassName(get_called_class());
			$news = [];
			foreach ($this->data['news'] as $key => $value) {
				$news[] = '(' . $key . ', ' . $value . ')';
			}
			$sql = 'INSERT INTO ' . $this->table . '(Owner, Post) VALUES ' . implode(', ', array_values($news)) . ' ON DUPLICATE KEY UPDATE Owner = Owner';
			return $db->execute($sql);
		}

 static function getPatternByProgram($program)
 {
     $pattern = new Pattern();
     $db = new DataBase("SchedulerDatabase");
     $getProgramPattern = "SELECT * FROM Patterns \n\t\t\t\t\t\t\t\t\tWHERE ProgramID = '{$program}'\n\t\t\t\t\t\t\t\t\tORDER BY YearRequired, TermRequired, SubjectID;";
     $rows = $db->execute($getProgramPattern);
     while ($row = $rows->fetch_object()) {
         $newItem = new PatternItem($row->ProgramID, $row->CourseType, $row->YearRequired, $row->TermRequired, $row->SubjectID, $row->CourseNumber);
         $pattern->addItem($newItem);
     }
     return $pattern;
 }

 public function updateRow()
 {
     $cols = [];
     $data = [];
     $db = new DataBase();
     foreach ($this->data as $k => $v) {
         $data[':' . $k] = $v;
         if ('id' == $k) {
             continue;
         }
         $cols[] = $k . '=:' . $k;
     }
     $sql = ' UPDATE ' . static::$table . ' SET ' . implode(', ', $cols) . ' WHERE id=:id';
     return $db->execute($sql, $data);
 }

 function getLabs()
 {
     $db = new DataBase("SchedulerDatabase");
     $labs = new SectionList();
     $subID = $this->subjectID;
     $CN = $this->courseNum;
     $term = $this->term;
     $sectionLetter = trim($this->sectionCode);
     $sqlquery = "SELECT * FROM Section\n\t\t\t\t\t\t\tWHERE SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t  AND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t  AND Term LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t  AND (SectionCode LIKE '%" . $sectionLetter . "_%' OR SectionCode LIKE '%L_%')\n\t\t\t\t\t\t\t\t  AND (NumberOfStudents < Capacity);";
     $rows = $db->execute($sqlquery);
     while ($row = $rows->fetch_object()) {
         $newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
         $labs->addItem($newItem);
     }
     return $labs;
 }

 public function dropDb()
 {
     $db = new DataBase($this->_dbname);
     $sql = "DROP DATABASE IF EXISTS `" . $this->_dbname . "`;";
     if ($db->setQuery($sql)) {
         $output = $db->execute();
         $db->freeResults();
         if ($output == null) {
             return false;
         } else {
             return true;
         }
     } else {
         return false;
     }
 }

 function batchUpdate($maxRows = 500)
 {
     $db = new DataBase();
     $db->connect();
     $query = new Query($this->__table__, "update");
     $query->batchMode = TRUE;
     $firstElement = $this->__elements__[0];
     $query->keys = array_keys($firstElement->__buildVars__());
     $e = 0;
     $query->extendedtype = "IGNORE";
     $query->values = array();
     foreach ($this->__elements__ as $elmnt) {
         //$elmnt->__buildVars__();
         $executed = false;
         $query->values[] = $elmnt;
         //echo join(",",$elmnt->__class_vars__)."<br/>";
         if ($e > 0 && $e % $maxRows == 0) {
             echo $query->build();
             //	$res = $db->execute($query);
             $query->values = array();
             $executed = true;
         }
         $e++;
     }
     //echo $query->build();
     if (!$executed) {
         $res = $db->execute($query);
     }
     //echo $elmnt->save();
 }

         } else {
             header("content-type: text/plain");
             echo "success-onsched=";
             echo $filename;
         }
         exit;
     }
     exit;
 case "GetPattern":
     //returns pattern of program specified in XML format
     $program = $_POST['program'];
     header("content-type: text/xml");
     $pat = new Pattern();
     //Select all courses within the pattern table whose program is equal to the one specified by user
     $getProgramPattern = "SELECT * FROM Patterns \n\t\t\t\t\t\t\t\t\tWHERE ProgramID = '{$program}';";
     $rows = $db->execute($getProgramPattern);
     while ($row = $rows->fetch_object()) {
         //Add all courses found in query to list
         $newItem = new PatternItem($row->ProgramID, $row->CourseType, $row->YearRequired, $row->TermRequired, $row->SubjectID, $row->CourseNumber);
         $pat->addItem($newItem);
     }
     //Return XML of pattern to client-side
     echo $pat->exportXML();
     exit;
 case "GetPrograms":
     //Retrieves all academic programs in database and returns them to client-side as XML string
     $getPrograms = "SELECT * FROM AcademicPrograms;";
     $rows = $db->execute($getPrograms);
     $returnval = "<programs>";
     while ($row = $rows->fetch_object()) {
         $returnval .= "<program><ProgramID>" . $row->ProgramID . "</ProgramID>\n\t\t\t\t\t\t\t   <ProgramCode>" . $row->ProgramCode . "</ProgramCode></program>";

$coursesObject = simplexml_load_string($courseList);
//load xml string of courses into XML object
$Sections = new SectionList();
//List of sections that have already been updated, will be used in case one section fails to update, will revert
$SectionsUpdated = new SectionList();
//Create section list out of courses return by client
foreach ($coursesObject->children() as $course) {
    $Sections->addItem(new Section($course->children()[0]->__toString(), $course->children()[1]->__toString(), $course->children()[2]->__toString(), $course->children()[3]->__toString(), $course->children()[4]->__toString(), $course->children()[5]->__toString(), $course->children()[6]->__toString(), $course->children()[7]->__toString(), $course->children()[8]->__toString(), $course->children()[9]->__toString(), $course->children()[10]->__toString(), $course->children()[11]->__toString()));
}
//Query to lock table during transaction
$lockSectionTable = "LOCK TABLE Section WRITE;";
//Query to unlock table once completed transaction
$unlockSectionTable = "UNLOCK TABLES;";
$revert = false;
//boolean to flag if revert required
$db->execute($lockSectionTable);
//lock DB
for ($i = 0; $i < count($Sections->SectionItems) && $revert == false; $i = $i + 1) {
    $subID = $Sections->itemAt($i)->subjectID;
    $CN = $Sections->itemAt($i)->courseNum;
    $SC = $Sections->itemAt($i)->sectionCode;
    $year = $Sections->itemAt($i)->year;
    $term = $Sections->itemAt($i)->term;
    //Query to retrieve given course, used to check that course is not already full
    $getSection = "SELECT * FROM Section \n\t\t\t\t\t\tWHERE SubjectID LIKE '%{$subID}%' AND \n\t\t\t\t\t\tCourseNumber LIKE '%{$CN}%' AND \n\t\t\t\t\t\tSectionCode = '{$SC}' AND\n\t\t\t\t\t\tYear = {$year} AND\n\t\t\t\t\t\tTerm LIKE '%{$term}%';";
    $row = $db->execute($getSection);
    //get section to be updated
    if ($row->num_rows != 1) {
        $revert = true;
    } else {
        $sec = $row->fetch_object();

//Create Database
require_once "classes/database.php";
//Connect to database file
$db = new DataBase("");
//Connect to server
if ($db->execute($createDB)) {
    //Create database in non exist
    //echo "Successfully Created Database<br/>";
} else {
    echo "Error Creating Database: " . $db->getError() . "<br/>";
    exit;
}
$db = new DataBase("{$dbName}");
//Connect to database created
/*-- Create tables --*/
if ($db->execute($CreateSectionTable)) {
    //echo "Successfully Created Section Table<br/>";
} else {
    echo "Error Creating Section Table: " . $db->getError() . "<br/>";
    exit;
}
if ($db->execute($CreateProgramsTable)) {
    //echo "Successfully Created Academic Programs Table<br/>";
} else {
    echo "Error Creating Academic Programs Table: " . $db->getError() . "<br/>";
    exit;
}
if ($db->execute($CreatePatternsTable)) {
    //echo "Successfully Created Pattern Table<br/>";
} else {
    echo "Error Creating Pattern Table: " . $db->getError() . "<br/>";

 function calculateCourses()
 {
     $db = new DataBase("SchedulerDatabase");
     //Arrays to keep track of courses not taken year 1-4
     $coursesNotTaken1 = $this->coursesNotTaken(1);
     $coursesNotTaken2 = $this->coursesNotTaken(2);
     $coursesNotTaken3 = $this->coursesNotTaken(3);
     $coursesNotTaken4 = $this->coursesNotTaken(4);
     $yearsCompleted = 0;
     for ($i = 1; $i < 5; $i++) {
         if ($this->completedYear($i)) {
             $yearsCompleted++;
         } else {
             $i = 5;
             //if does not complete previous year, break out of loop because impossible to complete years after
         }
     }
     if (count($coursesNotTaken1) > 0) {
         //If the student hasn't completed year 1
         for ($i = 0; $i < count($coursesNotTaken1); $i++) {
             //For every course not yet taken in that year
             if ($this->numCourses == 5) {
                 //if reached max amount of courses to be taken exit funtion
                 return;
             }
             //If you have the prerequistes and the course if for the proper term then add it to list
             if ($this->hasPrereq($yearsCompleted, $coursesNotTaken1[$i]->subjectID, $coursesNotTaken1[$i]->courseNumber)) {
                 $this->numCourses++;
                 //increment number of courses added
                 $subID = $coursesNotTaken1[$i]->subjectID;
                 $CN = $coursesNotTaken1[$i]->courseNumber;
                 $term = $this->term;
                 $getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
                 $rows = $db->execute($getSections);
                 while ($row = $rows->fetch_object()) {
                     $newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
                     $this->courses->addItem($newItem);
                 }
                 //end while
             }
             //end if
         }
         //end for
     }
     //end if
     if ($this->numCourses == 5) {
         //if reached max amount of courses to be taken exit funtion
         return;
     }
     if (count($coursesNotTaken2) > 0) {
         //If the student hasn't completed year 2
         for ($i = 0; $i < count($coursesNotTaken2); $i++) {
             //For every course not yet taken in that year
             if ($this->numCourses == 5) {
                 //if reached max amount of courses to be taken exit funtion
                 return;
             }
             //If you have the prerequistes and the course if for the proper term then add it to list
             if ($this->hasPrereq($yearsCompleted, $coursesNotTaken2[$i]->subjectID, $coursesNotTaken2[$i]->courseNumber)) {
                 $this->numCourses++;
                 //increment number of courses added
                 $subID = $coursesNotTaken2[$i]->subjectID;
                 $CN = $coursesNotTaken2[$i]->courseNumber;
                 $term = $this->term;
                 $getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
                 $rows = $db->execute($getSections);
                 while ($row = $rows->fetch_object()) {
                     $newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
                     $this->courses->addItem($newItem);
                 }
                 //end while
             }
             //end if
         }
         //end for
     }
     //end if
     if ($this->numCourses == 5) {
         //if reached max amount of courses to be taken exit funtion
         return;
     }
     if (count($coursesNotTaken3) > 0) {
         //If the student hasn't completed year 3
         for ($i = 0; $i < count($coursesNotTaken3); $i++) {
             //For every course not yet taken in that year
             if ($this->numCourses == 5) {
                 //if reached max amount of courses to be taken exit funtion
                 return;
             }
             //If you have the prerequistes and the course if for the proper term then add it to list
             if ($this->hasPrereq($yearsCompleted, $coursesNotTaken3[$i]->subjectID, $coursesNotTaken3[$i]->courseNumber)) {
                 $this->numCourses++;
                 //increment number of courses added
                 $subID = $coursesNotTaken3[$i]->subjectID;
                 $CN = $coursesNotTaken3[$i]->courseNumber;
                 $term = $this->term;
                 $getSections = "SELECT * FROM Section WHERE\n\t\t\t\t\t\t\t\t\t\tTerm LIKE '%{$term}%'\n\t\t\t\t\t\t\t\t\t\tAND SubjectID LIKE '%{$subID}%'\n\t\t\t\t\t\t\t\t\t\tAND CourseNumber LIKE '%{$CN}%'\n\t\t\t\t\t\t\t\t\t\tAND ScheduleCode LIKE '%LEC%'\n\t\t\t\t\t\t\t\t\t\tAND NumberOfStudents < Capacity;";
                 $rows = $db->execute($getSections);
                 while ($row = $rows->fetch_object()) {
                     $newItem = new Section($row->SubjectID, $row->CourseNumber, $row->Year, $row->Term, $row->Title, $row->Credits, $row->ScheduleCode, $row->SectionCode, $row->Time, $row->Days, $row->Capacity, $row->NumberOfStudents);
//.........这里部分代码省略.........