PHP connection::query方法代码示例

function AddProduct($config, $jsonResult)
{
    $cargo_id = $_GET["cargo_id"];
    $kind_id = "";
    $kind = $_GET["kind"];
    $amount = $_GET["amount"];
    $sql1 = "SELECT COUNT(name) as kind_amount , kind_id FROM kind WHERE name = '" . $kind . "';";
    $sql2 = "INSERT INTO kind(name) VALUES('" . $kind . "')";
    $conn = new connection($config);
    $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    try {
        $result1 = $conn->query($sql1);
        foreach ($result1 as $row) {
            if ($row['kind_amount'] === "0") {
                $conn->query($sql2);
                $kind_id = $conn->conn->lastInsertId();
            } else {
                $kind_id = $row['kind_id'];
            }
        }
        $conn->query("INSERT INTO product(cargo_id , amount , kind_id) VALUES(" . $cargo_id . " , " . $amount . " , " . $kind_id . ")");
        $product_id = $conn->conn->lastInsertId();
        $jsonResult->json_data['result_data'] = $product_id;
    } catch (PDOException $e) {
        $jsonResult->json_data['result_code'] = JSON::$resultCodes['mysql_exception'];
    }
}

function getCargoListWithKinds($config, $jsonResult)
{
    $conn = new connection(new Config(null, null));
    $result = array();
    $cargoes = $conn->query("SELECT * FROM cargo");
    foreach ($cargoes as $cargo) {
        $kinds = $conn->query("SELECT DISTINCT kind.name\n                FROM product INNER JOIN kind on product.kind_id = kind.kind_id\n                WHERE cargo_id =" . $cargo['cargo_id']);
        $kinds = $kinds->fetchAll();
        array_push($result, array('name' => $cargo['name'], 'kinds' => $kinds));
    }
    $jsonResult->json_data['result_data'] = $result;
}

    function has_items($id)
    {
        $db = new connection();
        $db->query('SELECT * FROM object WHERE type="page" AND status=1 AND parent_id=' . $id);
        if ($this->has_child($id)) {
            ?>
<ul class="submenu">
<?php 
        }
        while ($result = $db->fetch_array()) {
            ?>
<li><a href='./?page_id=<?php 
            echo $result['id'];
            ?>
'><?php 
            echo $result['menu_title'];
            ?>
</a>
<?php 
            if ($this->has_items($result['id'])) {
                echo $this->has_items($result['id']);
            }
            ?>
</li>
<?php 
        }
        if ($this->has_child($id)) {
            ?>
</ul>
<?php 
        }
    }

function RemoveAuction($config, $jsonResult)
{
    $id = $_GET['auction_id'];
    $conn = new connection($config);
    $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    $conn->query("DELETE FROM allegro where auction_id='" . $id . "';");
}

 function the_cat($by_name = null, $linked = null)
 {
     if ($this->is_object()) {
         if (!empty($this->cat)) {
             // Gibt die ID der Kategorie zurück
             if (null == $by_name) {
                 return $this->cat;
                 // Gibt den Titel der Kategorie zurück
             } else {
                 $db = new connection();
                 $get_the_catname = $db->query('SELECT * FROM object WHERE id=' . $this->cat);
                 $the_catname = $db->fetch_array();
                 if (null !== $linked) {
                     // Kategorie wird als Link ausgegeben
                     return '<a href="./?cat=' . $the_catname['id'] . '" rel="nofollow">' . $the_catname['title'] . '</a> ';
                 } else {
                     // Kategorie wird nicht als Link ausgegeben
                     return $the_catname['title'] . " ";
                 }
             }
         } else {
             return false;
         }
     }
 }

function getProductList($kind_name)
{
    $productsWithPhotos = array();
    $conn = new connection(new Config(null, null));
    $products = $conn->query("SELECT product_id , amount , kind.name as kind\nFROM product INNER JOIN kind on product.kind_id = kind.kind_id\nWHERE kind.name = '" . $kind_name . "';");
    print $conn->error;
    foreach ($products as $product) {
        $tmp_photos = array();
        $photos = $conn->query("SELECT photo.photo_id as id, photo.extension\n                from product inner join photo on product.product_id = photo.product_id\n                where product.product_id = '" . $product['product_id'] . "';");
        foreach ($photos as $photo) {
            array_push($tmp_photos, new Photo($photo['id'], $photo['extension']));
        }
        array_push($productsWithPhotos, new Product($product['product_id'], $product['kind'], $product['amount'], $tmp_photos));
    }
    return $productsWithPhotos;
}

function getCargoListWithKinds($config, $jsonResult)
{
    $kind_name = $_GET['kind_name'];
    $productsWithPhotos = array();
    $conn = new connection($config);
    $products = $conn->query("SELECT product_id , amount , kind.name as kind\nFROM product INNER JOIN kind on product.kind_id = kind.kind_id\nWHERE kind.name = '" . $kind_name . "';");
    print $conn->error;
    foreach ($products as $product) {
        $tmp_photos = array();
        $photos = $conn->query("SELECT photo.photo_id as id, photo.extension\n                from product inner join photo on product.product_id = photo.product_id\n                where product.product_id = '" . $product['product_id'] . "';");
        foreach ($photos as $photo) {
            array_push($tmp_photos, new Photo($photo['id'], $photo['extension']));
        }
        array_push($productsWithPhotos, new Product($product['product_id'], $product['kind'], $product['amount'], $tmp_photos));
    }
    $jsonResult->json_data['result_data'] = $productsWithPhotos;
}

function RemoveProduct($config, $jsonResult)
{
    $conn = new connection($config);
    $product_id = $_GET['product_id'];
    $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    try {
        $conn->query("DELETE FROM product WHERE product_id=" . $product_id . ";");
        $result = $conn->query("SELECT photo_id , extension FROM photo WHERE product_id = " . $product_id . ";");
        foreach ($result as $row) {
            $photo_id = $row['$photo_id'];
            unlink('../images/' . $photo_id . '.' . $row['extension']);
            $conn->query("DELETE FROM photo WHERE photo_id = " . $photo_id . ";");
        }
    } catch (PDOException $e) {
        $jsonResult->json_data['result_code'] = JSON::$resultCodes['mysql_exception'];
    }
}

function GetAuctionList($config, $jsonResult)
{
    $conn = new connection($config);
    $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    $result = $conn->query("SELECT auction_id , name , sandbox FROM allegro;");
    foreach ($result as $row) {
        array_push($jsonResult->json_data['result_data'], array('id' => $row['auction_id'], 'name' => $row['name'], 'sandbox' => $row['sandbox']));
    }
}

function GetCargoName($config, $jsonResult)
{
    $cargo_id = $_GET['cargo_id'];
    $conn = new connection($config);
    $result = $conn->query("SELECT name FROM cargo WHERE cargo_id = " . $cargo_id . ";");
    foreach ($result as $row) {
        array_push($jsonResult->json_data["result_data"], $row['name']);
    }
    $jsonResult->json_data["result_code"] = JSON::$resultCodes['ok'];
}

function AddCargo($config, $jsonResult)
{
    $name = $_GET['name'];
    $conn = new connection($config);
    $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    try {
        $conn->query("INSERT INTO cargo(name) VALUES('" . $name . "');");
    } catch (PDOException $e) {
        $jsonResult->json_data['result_code'] = JSON::$resultCodes['mysql_exception'];
    }
}

function GetProductList($config, $jsonResult)
{
    $cargo_id = $_GET['cargo_id'];
    $conn = new connection($config);
    $result = $conn->query("SELECT kind.name as kind_name , amount , product_id\n            from product\n            inner join kind on product.kind_id = kind.kind_id\n            inner join cargo on product.cargo_id = cargo.cargo_id\n            where cargo.cargo_id = '" . $cargo_id . "'\n            ORDER BY kind.name;");
    $kinds = array();
    foreach ($result as $row) {
        $tmp_photos = array();
        $result2 = $conn->query("SELECT photo.photo_id , photo.extension\n                from product inner join photo on product.product_id = photo.product_id\n                where product.product_id = '" . $row['product_id'] . "';");
        foreach ($result2 as $row2) {
            array_push($tmp_photos, new Photo($row2['photo_id'], $row2['extension']));
        }
        array_push($kinds, new Product($row['product_id'], $row['kind_name'], $row['amount'], $tmp_photos));
    }
    $jsonResult->json_data['result_data'] = $kinds;
    if ($result->rowCount() === 0) {
        $jsonResult->json_data["result_code"] = JSON::$resultCodes['no_product'];
    } else {
        $jsonResult->json_data["result_code"] = JSON::$resultCodes['ok'];
    }
}

function AddItems($config, $jsonResult)
{
    $conn = new connection($config);
    $product_id = $_GET['product_id'];
    $amount = $_GET['amount'];
    $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    try {
        $conn->query("UPDATE product SET amount = amount + " . $amount . " WHERE product_id = " . $product_id);
    } catch (PDOException $e) {
        $jsonResult->json_data['result_code'] = JSON::$resultCodes['mysql_exception'];
    }
}

 public function load($iRecordID)
 {
     $oCon = new connection();
     $sSQL = "SELECT RecordID, Title, Artist, Price, GenreID, PhotoPath\n\t\t\t\tFROM tbrecords \n\t\t\t\tWHERE RecordID = " . $iRecordID;
     $oResultSet = $oCon->query($sSQL);
     $aRow = $oCon->fetchArray($oResultSet);
     $this->iRecordID = $aRow['RecordID'];
     $this->sTitle = $aRow['Title'];
     $this->sArtist = $aRow['Artist'];
     $this->fPrice = $aRow['Price'];
     $this->iGenreID = $aRow['GenreID'];
     $this->iPhotoPath = $aRow['PhotoPath'];
     $oCon->close();
 }

function GetCargoList($config, $jsonResult)
{
    $conn = new connection($config);
    $result = $conn->query("SELECT\n                            COUNT(DISTINCT kind_id) as kind_amount ,\n                            COUNT(product_id) as product_amount ,\n                            SUM(amount) as item_amount,\n                            cargo.cargo_id,\n                            cargo.name as cargo_name\n                            FROM product\n                            RIGHT JOIN cargo on product.cargo_id = cargo.cargo_id\n                            GROUP BY cargo.name\n                            ORDER BY cargo.cargo_id;");
    print $conn->error;
    foreach ($result as $row) {
        array_push($jsonResult->json_data["result_data"], array('kind_amount' => $row['kind_amount'], 'product_amount' => $row['product_amount'], 'item_amount' => $row['item_amount'], 'cargo_id' => $row['cargo_id'], 'cargo_name' => $row['cargo_name']));
    }
    if ($result->rowCount() === 0) {
        $jsonResult->json_data['result_code'] = JSON::$resultCodes['no_cargoes'];
    } else {
        $jsonResult->json_data['result_code'] = JSON::$resultCodes['ok'];
    }
}