Defining various variables from an SQL database ta

问题:

I have a database that looks like this:

Name       | WebsitePrice | WebsiteStock |
-----------------------------------------
football   |   20         | Yes          |
-----------------------------------------
basketball |   10         | No           |
-----------------------------------------
hockey     |   30         | Yes          |
-----------------------------------------

I want to create 2 variables for each item:

1. price of item
2. stock of item.

This is the shortest way I came up with:

$item1 = "football";
$item2 = "basketball";
$item3 = "tennis-ball";

$productList = array();
$products = $mysqli->query("select * from table_products where Name IN ('$item1', '$item2', '$item3')");
if($products){
    while($product = mysqli_fetch_assoc($products)){
        $productList[$product['Name']]['WebsitePrice'] = $product['WebsitePrice'];
        $productList[$product['Name']]['WebsiteStock'] = $product['WebsiteStock'];
    }
}

//first product: 

$price1 = $productList[$item1]['WebsitePrice']; 
$stock1 = $productList[$item1]['WebsiteStock'];

//second product: 

$price2 = $productList[$item2]['WebsitePrice'];
$stock2 = $productList[$item2]['WebsiteStock'];

//third product: 

$price3 = $productList[$item3]['WebsitePrice'];
$stock3 = $productList[$item3]['WebsiteStock'];

It looks pretty messy to me. Is there any better way to do this? especially if we assume that I might have 20 items and not just 3.

for example, is there a way to skip writing the whole ('$item1', '$item2', '$item3') line and make that line refer to number of items that are defined in the first code block?

回答1:

If you want to echo the fields directly from the DB, then give your fields aliases, like:

SELECT Name, WebsitePrice as PriceOfItem, WebsiteStock as StockOfItem from table_products where Name IN ('$item1', '$item2', '$item3')

回答2:

Here's how I changed your code into dynamic one:

<?php

$con = mysqli_connect("localhost","test_user","user1234","test");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

//turn items into an array
$item_array = array('football','basketball','tennis-ball');

//implode items, turn into string
//result for this string is football','basketball','tennis-ball
$item_implode = join("','", $item_array);

//declare an overall array for result
$product_items = array();

$productList = array();
$result = $con->query("select * from table_products where Name IN ('$item_implode')");

if ($result->num_rows > 0) {
    $x = 0;
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $product_items[$x]["Name"] = $row['Name'];
        $product_items[$x]["WebsitePrice"] = $row['WebsitePrice'];
        $product_items[$x]["WebsiteStock"] = $row['WebsiteStock'];
        $x = $x + 1;
    }
} else {
    echo "0 results";
}

//can access data by $product_items[index][attribute]
//prints all
for ($i=0; $i < count($product_items); $i++) { 
    echo $product_items[$i]["Name"] . " - ";
    echo $product_items[$i]["WebsitePrice"] . " - ";
    echo $product_items[$i]["WebsiteStock"] . "<br/>";
}
?>

The result should be:

football - 20 - Yes
basketball - 10 - No

You can add items by adding/appending it to $item_array.