PHP getDB函数代码示例

 public function inscription(\Slim\Slim $app)
 {
     $allPostVars = $app->request->post();
     $username = $allPostVars['username'];
     $mail = $allPostVars['mail'];
     $mdp = $allPostVars['mdp'];
     try {
         $db = getDB();
         $verif = $db->prepare("SELECT username \n\t\t\t\tFROM users\n\t\t\t\tWHERE username = :username");
         $verif->bindParam(':username', $username, PDO::PARAM_INT);
         $verif->execute();
         $usernamed = $verif->fetch(PDO::FETCH_OBJ);
         $verif->closeCursor();
         if ($usernamed) {
             $answer = "Ce nom d'utilisateur est déjà pris, merci de retenter avec un nouveau.";
         } else {
             $sth = $db->prepare("INSERT INTO users \n\t\t\t\t(username, mail, mdp)\n\t\t\t\tVALUES (:username, :mail, :mdp)");
             $sth->bindParam(':username', $username, PDO::PARAM_INT);
             $sth->bindParam(':mail', $mail, PDO::PARAM_INT);
             $sth->bindParam(':mdp', $mdp, PDO::PARAM_INT);
             $sth->execute();
             $answer = array("status" => "success", "code" => 1);
         }
         $app->response->setStatus(200);
         $app->response()->headers->set('Content-Type', 'application/json');
         echo json_encode($answer);
         $db = null;
     } catch (PDOException $e) {
         $app->response()->setStatus(404);
         echo '{"error":{"text":' . $e->getMessage() . '}}';
     }
 }

function makeArticleEasy($article)
{
    log_info($article);
    $conn = getDB();
    $words = explode(" ", $article);
    $outp = "";
    $array = array();
    foreach ($words as $word) {
        $word = str_replace('"', "", $word);
        $word = str_replace("'", "", $word);
        $word = str_replace(" ", "", $word);
        $word = str_replace(".", "", $word);
        $word = str_replace(",", "", $word);
        $word = str_replace(":", "", $word);
        $word = str_replace("\\", "", $word);
        if (empty($word) || strlen($word) < 3) {
            continue;
        }
        $meaning = '';
        $gender = '';
        $partOfSpeech = '';
        //check if the meaning exist in DB
        $result = mysqli_query($conn, "SELECT * FROM words WHERE word = '" . $word . "'");
        if (mysqli_num_rows($result) == 0) {
            // row not found, get from online dicts
            $link = "http://de-en.dict.cc/?s=" . utf8_encode($word);
            $meaning = getMeaningFromOnlinDict($link);
            if (!empty($meaning)) {
                insertWord($conn, $word, $meaning, null, null, 0);
            } else {
                continue;
            }
        } else {
            //take db value
            $row = mysqli_fetch_row($result);
            //var_dump($row);
            $meaning = $row[2];
            $partOfSpeech = $row[3];
            $gender = $row[4];
        }
        if (empty($meaning)) {
            continue;
        }
        //now return as json object
        //     if ($outp != "") {$outp .= ",";}
        $a = new Article();
        $a->word = $word;
        $a->meaning = $meaning;
        array_push($array, $a);
        // $outp .= '{"word":"'  . $word . '",';
        //$outp .= '"meaning":"'  . $meaning . '",';
        //    $outp .= '"meaning":"'  . $meaning. '"}';
        //   $outp .= '"partOfSpeech":"'  . $partOfSpeech . '",';
        //    $outp .= '"gender":"'  . $gender  . '"}';
    }
    //forloop
    //$outp ='{"words":['.$outp.']}';
    echo json_encode($array);
    $conn->close();
}

 public function testDataType()
 {
     if (skipTest()) {
         $this->markTestSkipped();
         return;
     }
     if (getDB()->exec("CREATE TABLE copy_from_test(field1 TEXT NOT NULL, field2 INTEGER, field3 DATE)") === false) {
         $this->markTestSkipped("Create table for copy_from_test failed");
         return;
     }
     $data = [];
     for ($i = 0; $i < 100; $i++) {
         if ($i != 50) {
             $data[] = "text_{$i}\t{$i}";
         } else {
             $data[] = "text_{$i}\t\\N";
         }
     }
     if (!file_put_contents(__TESTS_TEMP_DIR__ . '/copy_from_test.csv', implode("\n", $data))) {
         $this->markTestSkipped("Unable to save the file needed for copy from test");
         return;
     }
     $this->assertTrue(getDB()->copyFromFile("copy_from_test", __TESTS_TEMP_DIR__ . '/copy_from_test.csv', null, null, ['field1', 'field2']));
     $r = getDB()->query("SELECT count(field1) AS f1, count(field2) AS f2 FROM copy_from_test")->fetch(PDO::FETCH_ASSOC);
     $this->assertEquals($r['f1'], 100);
     $this->assertEquals($r['f2'], 99);
 }

 public function __set($name, $value)
 {
     if (getUser()->data->id != $this->data->owner && getUser()->data->access_level != 0) {
         return;
     }
     switch ($name) {
         case 'name':
         case 'mime':
             getDB()->query('setFile', array('id' => $this->fid, 'field' => $name, 'value' => $value));
             $this->data->{$name} = $value;
             break;
         case 'list':
             getDB()->query('setFile', array('id' => $this->fid, 'field' => $name, 'value' => $value));
             $this->data = getDB()->query('refreshData', array('pid' => $this->pid));
             $this->data = $this->data->dataObj;
             break;
         case 'file':
             if (!is_file(SYS_TMP . $file)) {
                 $this->throwError('$value isn`t a file', $value);
             }
             getRAR()->execute('moveFile', array('source' => SYS_TMP . $value, 'destination' => SYS_SHARE_PROJECTS . $this->fid));
             break;
         default:
             return;
     }
 }

function createUser()
{
    $sql = "INSERT INTO users(nombre, email, telefono, foto_perfil, rol, password, poblacion, pais) VALUES(:nombre, :email, :telefono, :foto_perfil, :rol, :password, :poblacion, :pais)";
    $request = \Slim\Slim::getInstance()->request();
    $user = $request->params();
    $x1 = $user["nombre"];
    $x2 = $user["email"];
    $x3 = $user["telefono"];
    $x4 = $user["foto_perfil"];
    $x5 = $user["rol"];
    $x6 = $user["pass"];
    $x7 = $user["poblacion"];
    $x8 = $user["pais"];
    /*try {*/
    $dbh = getDB();
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':nombre', $x1);
    $stmt->bindParam(':email', $x2);
    $stmt->bindParam(':telefono', $x3);
    $stmt->bindParam(':foto_perfil', $x4);
    $stmt->bindParam(':rol', $x5);
    $stmt->bindParam(':password', $x6);
    $stmt->bindParam(':poblacion', $x7);
    $stmt->bindParam(':pais', $x8);
    $stmt->execute();
    /*$dbh=null;*/
    /*echo json_encode($user);
    		} catch (PDOException $e) {
    			echo "Error";
    		}*/
}

function login()
{
    $sql = "SELECT user_id, username, name, password FROM users WHERE username = ?";
    try {
        $body = getBody();
        $db = getDB();
        $stmt = $db->prepare($sql);
        $stmt->execute(array($body->username));
        $users = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        if (sizeof($users) > 0) {
            $user = reset($users);
            $salt = substr($user->password, 0, 10);
            $password = substr($user->password, 10);
            if ($password == hash('sha256', $salt . $body->password)) {
                $_SESSION["user"]["isLoggedIn"] = true;
                $_SESSION["user"]["id"] = $user->user_id;
                $_SESSION["user"]["username"] = $user->username;
                $_SESSION["user"]["name"] = $user->name;
                echo '{"success": true, "user": ' . json_encode($_SESSION["user"]) . '}';
            } else {
                echo '{"success": false, "message": "Wrong password."}';
            }
        } else {
            echo '{"success": false, "message": "Username not found."}';
        }
    } catch (PDOException $e) {
        //error_log($e->getMessage(), 3, '/var/tmp/phperror.log'); //Write error log
        echo '{"error":{"message":' . $e->getMessage() . '}}';
    }
}

/**
 * @param string $requiredPgSqlVersion
 * @return bool
 */
function skipTest($requiredPgSqlVersion = '0.0')
{
    if (is_null(getDB())) {
        return true;
    }
    return defined('__PGSQL_VERSION__') && __PGSQL_VERSION__ < $requiredPgSqlVersion;
}

 public function testQuery()
 {
     if (skipTest()) {
         $this->markTestSkipped();
         return;
     }
     if (getDB()->exec("CREATE TABLE test_query(int_field INTEGER NOT NULL, ts_field TIMESTAMP)") === false) {
         $this->markTestSkipped("Create table for testing query method of PDO");
     }
     $sampleData = [];
     for ($i = 0; $i < 3; $i++) {
         $sampleData[] = [':int' => $i, ':datetime' => (new \DateTime("2015-09-01 00:00:00"))->add(new DateInterval("P{$i}D"))];
     }
     $s = getDB()->prepare("INSERT INTO test_query(int_field, ts_field) VALUES(:int, :datetime)");
     for ($i = 0; $i < 3; $i++) {
         $s->bindParam(":int", $sampleData[$i][':int']);
         $s->bindParam(":datetime", $sampleData[$i][':datetime'], PDO::PARAM_DATETIME);
         $this->assertEquals(1, $s->execute());
     }
     $s = getDB()->query("SELECT * FROM test_query");
     $s->setColumnTypes(['ts_field' => PDO::PARAM_DATETIME]);
     $idx = 0;
     foreach ($s as $rIdx => $r) {
         $this->assertEquals($sampleData[$idx][':int'], $r['int_field']);
         $this->assertEquals($sampleData[$idx][':datetime'], $r['ts_field']);
         $idx++;
     }
     $this->assertEquals(3, $idx);
 }

function existingEmail($emailToCheck, $keyToCheck, $dbSheetToCheck)
{
    $db = getDB();
    //get list of sites to compare with site entered
    $stmt = $db->prepare("SELECT * FROM {$dbSheetToCheck}");
    $contentsOfDB = array();
    $newArrayToCheck = array();
    if ($stmt->execute() && $stmt->rowCount() > 0) {
        $contentsOfDB = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    //            //print entire array with keys for testing purposes
    //            $keys = array_keys($contentsOfDB);
    //            for($i = 0; $i < count($contentsOfDB); $i++)
    //            {
    //                echo $keys[$i] . "{<br>";
    //                foreach($contentsOfDB[$keys[$i]] as $key => $value)
    //                {
    //                    echo $key . " : " . $value . "<br>";
    //                }
    //                echo "}<br>";
    //            }
    for ($i = 0; $i < count($contentsOfDB); $i++) {
        $newArrayToCheck[$i] = $contentsOfDB[$i][$keyToCheck];
    }
    if (in_array($emailToCheck, $newArrayToCheck) == false) {
        return false;
    } else {
        return true;
    }
}

 /** Schliesst Registrierungsprozess ab.
  *
  * @param integer $id Registrierungs-ID
  * @param string $hash Registrierungshash
  * @param string $username Gewünschter Benutzername
  * @param string $password Passwort
  * @return mixed true bei Erfolg oder String mit Fehlermeldung, false falls
  *				Datenbankabfrage fehlschlug
  * @author Cédric Neukom
  */
 public function completeRegistration($id, $hash, $username, $password)
 {
     // Falls login durchgeführt werden kann abbrechen
     if (!$this->permitted) {
         return $this->throwError('You\'re already logged in. You can\'t register a new account.');
     }
     $db = getDB();
     // Registrierung validieren
     $id = intval($id);
     $reg = $db->query('getRegistrationByID', array('id' => $id));
     if (!$reg || !$reg->dataLength) {
         return $this->throwError('Your registration could not be found.');
     }
     if ($reg->dataObj->password !== $hash) {
         return $this->throwError('Your registration could not be found.');
     }
     // Benutzername prüfen
     if (strlen($username) < SYS_USERNAME_MINLENGTH || strlen($username) > SYS_USERNAME_MAXLENGTH) {
         return $this->throwError('The length of your username must be between ' . SYS_USERNAME_MINLENGTH . ' and ' . SYS_USERNAME_MAXLENGTH . ' characters.');
     }
     $names = $db->query('getUser', array('user' => $username));
     if (!$names || $names->dataLength) {
         return $this->throwError('The username you chose is already taken.');
     }
     // Passwort prüfen
     if (strlen($password) < SYS_PASSWORD_MINLENGTH || strlen($password) > SYS_PASSWORD_MAXLENGTH) {
         return $this->throwError('The length of you password must be between ' . SYS_PASSWORD_MINLENGTH . ' and ' . SYS_PASSWORD_MAXLENGTH . ' characters.');
     }
     // Registrierung abschliessen
     return $db->query('registerUser', array('pwd' => self::hashPwd($password), 'name' => $username, 'id' => $id));
 }

 function getArticulosByCodgru($arCodgru)
 {
     global $app;
     //        $app = \Slim\Slim::getInstance();
     try {
         $db = getDB();
         $sth = $db->prepare("SELECT * FROM articulos WHERE ArCodgru=:arCodgru ORDER BY ArCodgru");
         $sth->bindParam(':arCodgru', $arCodgru, PDO::PARAM_STR);
         $result = $sth->execute();
         //$articulos = 1;
         $page = $app->request->get('page', 1);
         $pageSize = $app->request->get('size', 1000);
         if ($result) {
             $app->response->setStatus(200);
             $app->response()->headers->set('Content-Type', 'application/json');
             $inicio = $page * $pageSize - $pageSize;
             $tope = $page * $pageSize;
             $i = 0;
             while (($articulo = $sth->fetch(PDO::FETCH_OBJ)) && $i < $tope) {
                 if ($i >= $inicio) {
                     echo json_encode($articulo);
                 }
                 $i++;
             }
             $db = null;
         } else {
             throw new PDOException('No records found.');
         }
     } catch (PDOException $e) {
         $app->response()->setStatus(404);
         echo '{"error":{"text":' . $e->getMessage() . '}}';
     }
 }

 public function testDataType()
 {
     if (skipTest('9.3')) {
         $this->markTestSkipped();
         return;
     }
     if (getDB()->exec("CREATE TABLE json(field JSON NOT NULL)") === false) {
         $this->markTestSkipped("Create table with json data type failed");
     }
     $s = getDB()->prepare("INSERT INTO json VALUES(:json)");
     $this->assertInstanceOf('\\PgBabylon\\PDOStatement', $s, "Asserting pdo::prepare returns a pgbabylon statement");
     $p = ["key_1" => "val_1", "key_2" => 2];
     $s->bindParam(":json", $p, PDO::PARAM_JSON);
     $r = $s->execute();
     $this->assertTrue($r, "Testing json insert using PHP array");
     $s = getDB()->prepare("SELECT field AS json_col FROM json");
     $s->bindColumn("json_col", $val, PDO::PARAM_JSON);
     $r = $s->execute();
     $this->assertTrue($r, "Testing json select");
     $r = $s->fetch(PDO::FETCH_ASSOC);
     $this->assertSame(['json_col' => $p], $r, "Asserting fetch return an array after deserializing the pgsql json");
     $this->assertSame($p, $val, "Asserting fetch sets the previously bound variable as json");
     $s = getDB()->prepare("SELECT field AS json_col FROM json", PDO::AUTO_COLUMN_BINDING);
     $r = $s->execute();
     $this->assertTrue($r, "Testing json select with auto column binding");
     $r = $s->fetch(PDO::FETCH_ASSOC);
     $this->assertSame(['json_col' => $p], $r, "Asserting fetch return an array after deserializing the pgsql json with auto column binding");
     $p = ["key_2" => "val_2", "key_3" => 3];
     $s = getDB()->prepare("INSERT INTO json VALUES(:json)");
     $r = $s->execute([':json' => PgBabylon\DataTypes\JSON($p)]);
     $this->assertTrue($r, "Testing json insert using PHP array directly in execute");
 }

 public function testDataType()
 {
     if (skipTest()) {
         $this->markTestSkipped();
         return;
     }
     if (getDB()->exec("CREATE TABLE copy_to_test(field1 TEXT NOT NULL, field2 INTEGER, field3 DATE)") === false) {
         $this->markTestSkipped("Create table for copy_to_test failed");
         return;
     }
     $data = [];
     $s = getDB()->prepare("INSERT INTO copy_to_test(field1,field2) VALUES (:field1, :field2)");
     for ($i = 0; $i < 100; $i++) {
         if ($i != 50) {
             $data[] = "text_{$i}\t{$i}";
             $s->execute([":field1" => "text_{$i}", ":field2" => $i]);
         } else {
             $data[] = "text_{$i}\t\\N";
             $s->execute([":field1" => "text_{$i}", ":field2" => null]);
         }
     }
     $this->assertTrue(getDB()->copyToFile("copy_from_test", __TESTS_TEMP_DIR__ . '/copy_to_test.csv', null, null, ['field1', 'field2']));
     if (!($f = file_get_contents(__TESTS_TEMP_DIR__ . '/copy_to_test.csv'))) {
         $this->markTestSkipped("Unable to load the file needed for copy to test");
         return;
     }
     $this->assertSame(implode("\n", $data) . "\n", $f);
 }

function deleteArticle($articleId)
{
    $conn = getDB();
    $query = "DELETE FROM Articles WHERE articleId = " . $articleId . " limit 1";
    $result = mysqli_query($conn, $query);
    $conn->close();
}

function getExpenseReviewForm($CategoryParentType, $CategoryParentOrder)
{
    $ResultsToReturn = getDB($CategoryParentType, $CategoryParentOrder);
    ?>
<hr><h4 class="panel-title col-sm-5"><?php 
    echo substr($ResultsToReturn[0]["categoryParentName"], 7);
    ?>
</h4>
            <div class="col-sm-1">Client</div>
            <div class="col-sm-1">Spouse</div>
            <div class="col-sm-1">Total</div>
            <div class="col-sm-1">Actual</div>
            <div class="col-sm-1">Difference</div>
            <div class="panel-body">
                <?php 
    $subtotal = 0;
    foreach ($ResultsToReturn as $row) {
        $total = $row["budgetSelfAmount"] + $row["budgetSpouseAmount"];
        $subtotal += $total;
        $GLOBALS['totalExpense'] += $total;
        if ($total > 0.0) {
            ?>
                        <div class = "row">
                            <div class="col-sm-5"><span><?php 
            echo $row["categoryName"];
            ?>
</span></div>
                            <div class = "col-sm-1"><span>$&nbsp<?php 
            echo number_format($row["budgetSelfAmount"]);
            ?>
</span></div>
                            <div class = "col-sm-1"><span>$&nbsp<?php 
            echo number_format($row["budgetSpouseAmount"]);
            ?>
</span></div>
                            <div class = "col-sm-1"><span>$&nbsp<?php 
            echo number_format($total);
            ?>
</span></div>
                            <div class="col-sm-1">_________</div>
                            <div class="col-sm-1">_________</div>
                        </div>
                        <?php 
        }
    }
    ?>
                <div class="row">
                    <div class="col-sm-7"><h5><u>Total</u></h5></div>
                    <div class="col-sm-1"><u>$&nbsp<?php 
    echo number_format($subtotal);
    ?>
</u></div>
                    <div class="col-sm-2"><u><?php 
    echo number_format($subtotal / $GLOBALS['grossIncome'] * 100);
    ?>
&nbsp% gross income</u></div>
                </div>
            </div>
            <?php 
}