求助：如何将MySQL查询结果转为trader_sma可用的索引数组

Hey there! I get that you're stuck on converting your database price data into the format the trader_sma function needs—let's break this down step by step, since you already have the hard part (pulling the data) done.

First, let's recap what trader_sma expects: a numeric-indexed array (0-based, consecutive) where each element is a float/int of the price value. The issue is that database query results usually come as associative arrays or objects, so we just need to extract and reformat the data properly.

Step 1: Refine your SQL query (and make it safer) #

First, let's tweak your SQL to ensure we're grabbing data in the right order. Since you need the most recent 5 prices, sort by import_nr (assuming this tracks when data was imported, with higher values for newer entries) and limit to 5. Also, never directly inject variables like $symbol or $table into SQL—use prepared statements to avoid SQL injection. Here's how:

If you're using mysqli:

// Assume you have a mysqli connection $conn
$symbol = "BTCUSDT"; // Example symbol
$table = "prices"; // Example table name

// Use prepared statement to avoid SQL injection
$sql = "SELECT `lastPrice` FROM `$table` WHERE `symbol` = ? ORDER BY import_nr DESC LIMIT 5";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $symbol);
$stmt->execute();
$result = $stmt->get_result();

If you're using PDO:

// Assume you have a PDO connection $pdo
$symbol = "BTCUSDT";
$table = "prices";

$sql = "SELECT `lastPrice` FROM `$table` WHERE `symbol` = :symbol ORDER BY import_nr DESC LIMIT 5";
$stmt = $pdo->prepare($sql);
$stmt->execute(['symbol' => $symbol]);

Step 2: Convert query results to a valid index array #

Now we need to extract the lastPrice values into a simple numeric array, and convert them to floats (since database values might be stored as strings):

For mysqli:

$priceArray = [];
while ($row = $result->fetch_assoc()) {
    // Convert string price to float and add to the array
    $priceArray[] = (float)$row['lastPrice'];
}

For PDO:

// Fetch all `lastPrice` values directly as an array
$priceRows = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
// Convert all values to floats
$priceArray = array_map('floatval', $priceRows);

Step 3: Adjust the array order (critical for accurate indicators) #

Right now, your array is ordered from newest to oldest price (since we sorted import_nr DESC). But technical indicators like SMA require data to be in oldest to newest order. Reverse the array to fix this:

$priceArray = array_reverse($priceArray);

Step 4: Use the array with trader_sma #

Now you have a valid array for trader_sma! Let's test it:

// Calculate SMA with period 5 (since we have 5 data points)
$smaResult = trader_sma($priceArray, 5);

// Output the result (trader_sma returns an array; for period 5, it will have 1 value)
print_r($smaResult);

Quick checks to avoid issues: #

• Make sure the trader extension is installed and enabled—verify this by running phpinfo() and looking for the "trader" section.
• Double-check that import_nr correctly orders data (newer entries should have higher values).
• Ensure lastPrice in your database is a numeric type (DECIMAL, FLOAT, etc.) to avoid conversion errors.

That should get you past the array conversion hurdle! Once this works, the same approach applies to EMA, MACD, and other trader functions—they all expect similar numeric index arrays.

内容的提问来源于stack exchange，提问作者J Pavic