如何用最短的代码从MySQL数据库调用多行？ [关闭]

EDIT:

It looks like people here don't see the goal I'm trying to reach to.

Ok, I didn't know about IN, but it still doesn't help me.

If this is my table:

Name | Price | In Stock

Apple | 5 | Yes

Tomato | 7 | No

I want to understand what is the best practice to easily create variables such as $apple_price , $apple_stock and $tomato_price, $tomato_stock, without using too many code blocks, or name repeating throughout the code.

I also don't want to simply print them out. I will want to later use the values for calculations, etc.

I want to display several prices of several items, all from a certain database table.

Since each item has its own row, and in the code form that I am familiar with, there is a necessary reference to one particular row, I'm no sure how to write the code in an efficient, shorter way, than the following one:

$mysqli = new mysqli("server","user","pass","database-name");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s
", mysqli_connect_error());
    exit();
    }

$item1 = "apple";
$item2 = "tomato";

$products = $mysqli->query("select * from price_list where Name = '$item1'");
    if($products){
        $product = mysqli_fetch_assoc($products);
        $price1 = $product['Price'];
}

$products = $mysqli->query("select * from price_list where Name = '$item2'");
    if($products){
        $product = mysqli_fetch_assoc($products);
        $price2 = $product['Price'];
}
?>

<?php echo $price1; ?>
<?php echo $price2; ?>

you want this for a few items...

select * from price_list where Name = '$item1' OR Name = '$item2'

Or if you have a really big list...

select * from price_list where Name IN ('$item1','$item2', '$item3', '$item4', ...)

And finally if these items are stored someplace else...

select * from price_list where Name In (select Name from your_other_place)

In response to your other question in the comments... To loop over multiple rows.

$query = "select * from price_list where Name IN ('$item1','$item2', '$item3', '$item4', ...)";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        print $row['price'];
    }

    /* free result set */
    $result->free();
}

Reference: PHP Manual

use can use the 'in' identifier instead of '=' and use the query like the following

select * from price_list where Name in ('apple', 'tomtato')

The following is the updated full code.

$mysqli = new mysqli("server","user","pass","database-name");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s
", mysqli_connect_error());
    exit();
}

$items = array("apple", "tomtato");
$items_str = "(";

// Build the in statement parameters
foreach($items as $item) {
    $items_str .= "'" . $item . "', " 
}
rtrim($items_str, ',');
$items_str .= ")";

$prices = array();
$products = $mysqli->query("select * from price_list where Name in $items_str");
if($products){
    $product = mysqli_fetch_assoc($products);
    $prices[$product['title']]['price'] = $product['Price'];
}

// Print each item title and price
foreach($prices as $key => $price) {
   echo "the price of the following item : " . $key . " is " . $price['price'];
}

What you are trying to achieve I think is the generation of dynamic variables. The code below has two approaches - the use of a stdClass object was my initial idea as it allows you to easily reference the values later when needed. The dynamic variables are more closely aligned with your original approach as you can access an unlimited amount of them ( if they are defined ) as you did in the question ~ namely echo $price1; etc

create table `price_list` (
    `id` int(10) unsigned not null auto_increment,
    `name` varchar(50) not null default '0',
    `description` varchar(50) not null default '0',
    `price` smallint(6) unsigned not null default '0',
    `instock` tinyint(3) unsigned not null default '0',
    primary key (`id`)
)engine=innodb auto_increment=7;

insert into `price_list` (`id`, `name`, `description`, `price`, `instock`) values
    (1, 'apple', 'red and rosey - give it to the teacher', 32, 1),
    (2, 'orange', 'sharp and tangy', 15, 1),
    (3, 'banana', 'yellow and curvy', 6, 1),
    (4, 'kiwi', 'green and slimy', 99, 1),
    (5, 'pomegranate', 'awful', 55, 0),
    (6, 'tomato', 'its a fruit', 2, 1);





$dbhost =   'xxx';
$dbuser =   'xxx'; 
$dbpwd  =   'xxx'; 
$dbname =   'xxx';

$item1 = "apple";
$item2 = "tomato";
$item3 = "pomegranate";
$item4 = "banana";
$item5 = 'Kiwi';

$items=array( $item1, $item2, $item3, $item4, $item5 );
$sql='select * from price_list where `Name` in ("'.implode('","',$items).'");';

$conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );                
$res = $conn->query( $sql );

if( $res ){

    $i=0;/* counter for dynamic variables */
    $prices=new stdClass;

    while( $rs=$res->fetch_object() ){
        $i++;

        /* Populate an object with details for the product */
        $prices->{ strtolower( $rs->name ) }=(object)array( 'price'=>$rs->price, 'description'=>$rs->description, 'instock'=>$rs->instock );

        /* Or, if you simply want a dynamic variable to use later try this approach */
        ${'price'.$i}=$rs->price;

        /* It is the line above that will be of most interest */

        /* Or, with a name */
        ${ strtolower( $rs->name ) .'_price' }=$rs->price;
    }
}   

/* much later - access the data as desired */
echo 'Apple:'.$prices->apple->description . '<br />';
echo 'Tomato:'.$prices->tomato->instock . '<br />';
echo 'Kiwi:'.$prices->kiwi->price . '<br />';

/* using dynamic/variable variables */      
echo 'price 2:'.$price2 . '<br />';