Beispiel #1
0
 /**
  * Main query generator
  * 
  * @param string $sql String of SQL code to execute
  * @param array $paramArray Array of parameters to bind, bind types as 
  * first element of the array. Can send array of arrays, in which case
  * it will be treated as a transaction.
  * @param string $db Optional, send if a different database is to be used
  * for just this query.
  * @return boolean|\DBResult Returns false if query fails, otherwise result
  * object
  */
 public static function query($sql, $paramArray = NULL, $db = NULL, $auto_clean = NULL, $index = NULL)
 {
     // For function flexibility can send dbname as 2nd parameter
     $log = false;
     if ($log) {
         Timer::start();
     }
     if ($paramArray && !is_array($paramArray)) {
         $db = $paramArray;
         $paramArray = NULL;
     }
     // Get database handler
     $dbh = self::getDBh($db);
     // If query fails to initialise then print error message
     if (!$dbh || !($stmt = $dbh->prepare($sql))) {
         echo "<p>DB failure on database " . self::getDB() . " with query {$sql}";
         if ($paramArray) {
             "and parameters - ";
             print_r($paramArray);
         }
         echo "</p>";
         echo "<p>With error message - ";
         echo $dbh->error;
         echo "</p>";
         echo "<pre>";
         var_dump(debug_backtrace());
         var_dump($db);
         echo "</pre>";
         exit;
     }
     $affRows = 0;
     // Bind parameters if supplied
     if ($paramArray) {
         // Allows an array of parameters to be applied to the same prepared
         // statement
         // Turns off autocommit for 10x speed!
         if (is_array($paramArray[0])) {
             self::begin();
             foreach ($paramArray as $pA) {
                 self::bindParam($stmt, $pA);
                 $affRows += $stmt->affected_rows;
             }
             self::commit();
             // Single set of parameters only
         } else {
             self::bindParam($stmt, $paramArray);
             $affRows += $stmt->affected_rows;
         }
         // No parameters, just execute the sql
     } else {
         $stmt->execute();
         $affRows += $stmt->affected_rows;
     }
     $meta = $stmt->result_metadata();
     $result = new DBResult();
     // If no metadata then INSERT, UPDATE etc. so return id data
     if (!$meta) {
         $result->affected_rows = $affRows;
         // Insert ID of last inserted if multiple done
         $result->insert_id = $stmt->insert_id;
         // If metadata then retrieve and store results
     } else {
         $stmt->store_result();
         // If no results from the SELECT return false
         $result->num_rows = $stmt->num_rows;
         $params = array();
         $row = array();
         $result->rows = array();
         // Get field names from meta
         while ($field = $meta->fetch_field()) {
             $params[] =& $row[$field->name];
         }
         $meta->close();
         call_user_func_array(array($stmt, 'bind_result'), $params);
         // Create result object as $result->rows[]->fieldnames
         while ($stmt->fetch()) {
             $rowobj = new StdClass();
             foreach ($row as $key => $value) {
                 if ($auto_clean) {
                     $value = HTML::clean($value);
                 }
                 $rowobj->{$key} = $value;
             }
             if ($index) {
                 $result->rows[$rowobj->{$index}] = $rowobj;
             } else {
                 $result->rows[] = $rowobj;
             }
         }
         $stmt->free_result();
     }
     $stmt->close();
     if ($log) {
         $time = Timer::result();
         if (self::getDB() != 'dbusers') {
             $logsql = "INSERT INTO dbLog ( `sql`, params, `time`, `dbfn` ) VALUES ( ?, ?, ?, 'DB' )";
             $pA = array($sql, serialize($paramArray), $time);
             DBP::query($logsql, $pA, 'ecrf');
         }
         Timer::stop();
     }
     return $result;
 }
Beispiel #2
0
 /**
  * @Route("/wotr/ajax/units/{id}")
  * @ParamConverter("post", class="WotRBundle:Game", options={"repository_method" = "getCurrentGame"})
  * @param Game $game
  * @param Request $request
  * @return JsonResponse|Response
  */
 public function ajaxUnitsAction(Game $game, Request $request)
 {
     if ($request->isXMLHttpRequest()) {
         $units = $game->getUnits();
         $outputArr = array();
         /** @var Unit $u */
         foreach ($units as $u) {
             if ($u->getLocation()) {
                 $outputArr[$u->getId()] = array('unitName' => $u->getName(), 'loc' => $u->getLocation()->getId(), 'sideId' => $u->getSide()->getId());
             }
         }
         $response = new JsonResponse();
         $response->setData(array('data' => $outputArr));
         return $response;
     }
     return new Response('This is not ajax!', 400);
     $unitTable = 'units';
     $request = new Request();
     switch ($request->getProperty('request')) {
         case 'units':
             $sql = "SELECT {$unitTable}.id, loc, unit.name as unitName, unitType.name as type, " . "nation.side_id as sideId, nation.name as nation FROM {$unitTable} " . "LEFT JOIN unit ON unit_id = unit.id " . "LEFT JOIN nation ON nation_id = nation.id " . "LEFT JOIN unitType on unitType_id = unitType.id " . "WHERE loc != 0 " . "ORDER BY {$unitTable}.loc";
             $unitsQ = DBP::query($sql);
             $outputArr = array();
             foreach ($unitsQ->rows as $unit) {
                 $outputArr[$unit->id] = array('unitName' => $unit->unitName, 'loc' => $unit->loc, 'type' => $unit->type, 'nation' => $unit->nation, 'sideId' => $unit->sideId);
             }
             echo json_encode($outputArr);
             break;
         case 'recruitable':
             $sql = "SELECT {$unitTable}.id, IF( ISNULL(unit.name),CONCAT(nation.name, ' ', unitType.name, ' (', COUNT({$unitTable}.id), ' remaining)'),unit.name) AS unitName " . "FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "LEFT JOIN nation ON nation.id = nation_id " . "LEFT JOIN unitType ON unitType.id = unitType_id " . "WHERE loc = 0 AND ( side_id = 2 || casualty != 1 ) AND unit.unitType_id <= 4 " . "GROUP BY CONCAT(nation.name, ' ', unitType.name) " . "UNION ALL " . "SELECT {$unitTable}.id, unit.name AS unitName FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "LEFT JOIN nation ON nation.id = nation_id " . "LEFT JOIN unitType ON unitType.id = unitType_id " . "WHERE loc = 0 AND casualty != 1 AND unit.unitType_id >= 5 " . "ORDER BY id";
             $units = DBP::query($sql, NULL, NULL, 'keyPair');
             echo json_encode($units->rows);
             break;
         case 'recruit':
             $unitID = $request->getProperty('unit');
             $regionID = $request->getProperty('region');
             if ($regionID && $unitID) {
                 $sql = "UPDATE {$unitTable} SET loc = ? WHERE id = ?";
                 $pA = array($regionID, $unitID);
                 DBP::query($sql, $pA);
                 echo "Unit recruited.";
             } else {
                 echo "Please select a unit to recruit.";
             }
             break;
         case 'move':
             $unitIDs = $request->getProperty('selectedUnits');
             $dest = $request->getProperty('dest');
             if (is_array($unitIDs)) {
                 $qMarks = str_repeat('?,', count($unitIDs) - 1) . '?';
                 $sql = "UPDATE {$unitTable} SET loc = ? WHERE id IN ({$qMarks})";
                 array_unshift($unitIDs, $dest);
                 DBP::query($sql, $unitIDs);
                 echo "Units moved";
             } else {
                 echo "Please select units to move";
             }
             break;
         case 'remove':
             $unitIDs = $request->getProperty('selectedUnits');
             $casualty = $request->getProperty('casualty');
             if (is_array($unitIDs)) {
                 $qMarks = str_repeat('?,', count($unitIDs) - 1) . '?';
                 $sql = "UPDATE {$unitTable} SET loc = 0, casualty = ? WHERE id IN ({$qMarks})";
                 array_unshift($unitIDs, $casualty);
                 DBP::query($sql, $unitIDs);
                 echo "Units removed";
             } else {
                 echo "Please select units to remove";
             }
             break;
         case 'reduce':
             $unitIDs = $request->getProperty('selectedUnits');
             $regionID = $request->getProperty('region');
             $return = '';
             foreach ($unitIDs as $unitID) {
                 $sql = "SELECT nation_id FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "WHERE {$unitTable}.id = ?";
                 $result = DBP::query($sql, array($unitID));
                 $nation = $result->nation_id;
                 $sql = "SELECT {$unitTable}.id, casualty FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "WHERE nation_id = ? AND loc = 0 AND unitType_id = 1 " . "ORDER BY casualty DESC";
                 $regulars = DBP::query($sql, array($nation));
                 if ($regulars->getRows()) {
                     $sql = "UPDATE {$unitTable} SET loc = 0, casualty = 1 WHERE id = ?";
                     $pA = array($unitID);
                     DBP::query($sql, $pA);
                     $sql = "UPDATE {$unitTable} SET loc = ?, casualty = 0 WHERE id = ?";
                     $pA = array($regionID, $regulars->id);
                     DBP::query($sql, $pA);
                     $return = 'Elite reduced';
                 } else {
                     $return = 'No regular to reduce elite';
                 }
             }
             echo $return;
     }
 }