代码之家 › 专栏 › 技术社区 › xkeshav

如何从单个查询中获取数组中的值

mysql php

1

xkeshav · 技术社区 · 15 年前

我有一个

Book ID Array

    Array
    (
        [0] => 61
        [1] => 72
        [2] => 78
        [3] => 100
        [4] => 102
    )

现在从另一张桌子 table_bookPrice 如果给出了提交的价格我希望从表中选择所有价格,其中book id在给定数组(book id数组)中,如果价格未在中提到 表格书价 字段,那么它将是自动的500

确切的问题是什么

所以我得到的阵列是这样的

Book Price Array

    Array
    (
        [0] => 150
        [1] => 100
        [2] => 500 ( not mentioned in table, so it is 500)
        [3] => 300
        [4] => 200
    )

2 回复 | 直到 15 年前

1

Anthony Forloney 15 年前

我在工作,所以不能测试或编译它,但我希望我的逻辑是可以理解的。

不确定这是否有效,但这方面的一些东西

$book_price_array = array(); //contents to be added.

// loop through the array an examine its price by querying your table.
foreach ($book_id_array as $key => $value) {
   $price = mysql_query("SELECT price FROM table_bookPrice 
                                     WHERE book_id = {$value}");
   // there is a price, set the price.
   if ($price > 0 && $price != NULL)  $book_price_array[$key] = $price;

   // there is no price, set the default price
   else  $book_price_array[$key] = 500; 
}

2

0

AJ. 15 年前

以下是我为您的问题构建的测试数据库:

mysql> select  * from table_bookPrice;
+----+-------+--------+
| id | price | bookid |
+----+-------+--------+
|  1 |   150 |     61 |
|  2 |   100 |     72 |
|  3 |   300 |    100 |
|  4 |   200 |    102 |
+----+-------+--------+
4 rows in set (0.00 sec)

下面是PHP代码:

<?php

$books=array(61,72,78,100,102);

// establish an assoc array of bookid => default_price
$prices=array();
foreach($books as $bookid){
    $prices["$bookid"]=500;
}

// build query to select all prices stored in database
$bookids=implode(', ',$books);
mysql_connect('localhost','aj','nothing') or die('unable to connect!');
mysql_select_db('books') or die('unable to select db!');
$stmt="SELECT bp.bookid, bp.price FROM table_bookPrice bp WHERE bp.bookid IN ($bookids)";
$res=mysql_query($stmt);
while( ($rec= mysql_fetch_assoc($res)) ){
    $idstr=(string)$rec['bookid'];
    $prices[$idstr]=$rec['price'];
}

print_r($prices);

?>

此输出:

Array
(
    [61] => 150
    [72] => 100
    [78] => 500
    [100] => 300
    [102] => 200
)