如何用一条查询语句从多张表获取所有变量并按指定格式返回？

Absolutely! You can merge these three separate database calls into a single SQL statement using JOIN operations—since all three tables share the common ix variable. This approach cuts down on database round-trips (boosting performance) and cleans up your code significantly.

Step 1: The Combined SQL Query #

We'll use LEFT JOIN here to ensure we get results even if one of the tables doesn't have a matching ix record (unlike INNER JOIN, which would drop the entire row if any table lacks a match). Here's the query:

SELECT 
    i1.idz,
    i2.idxc,
    i3.idsd
FROM 
    i1
LEFT JOIN 
    i2 ON i1.ix = i2.ix
LEFT JOIN 
    i3 ON i1.ix = i3.ix
WHERE 
    i1.ix = ?
LIMIT 1

Notice the ? placeholder—this is for parameterized queries, which avoids SQL injection risks (a critical improvement over your original string-concatenated code).

Step 2: Updated PHP Code #

Replace your multiple query/loop blocks with this streamlined version:

// Execute the single parameterized query
$result = BDR::selectBySQL(
    "g",
    "SELECT i1.idz, i2.idxc, i3.idsd FROM i1 LEFT JOIN i2 ON i1.ix = i2.ix LEFT JOIN i3 ON i1.ix = i3.ix WHERE i1.ix = ? LIMIT 1",
    [$this->ixx] // Pass the variable as a parameter to avoid injection
);

// Initialize default values to prevent undefined index errors
$idz = null;
$idxc = null;
$idsd = null;

// Extract values if a result exists
if (!empty($result)) {
    $row = $result[0];
    $idz = $row['idz'] ?? null;
    $idxc = $row['idxc'] ?? null;
    $idsd = $row['idsd'] ?? null;
}

// Return the formatted array (fixed the typo in your original return where 'id2' was duplicated)
return [
    'id1' => $idz,
    'id2' => $idxc,
    'id3' => $idsd
];

Key Notes: #

• LEFT JOIN vs INNER JOIN: Use LEFT JOIN to retain data from i1 even if i2 or i3 have no matching ix entries (those missing fields will return NULL). If you only want results where all three tables have matching ix records, switch to INNER JOIN.
• SQL Injection Protection: Parameterized queries (using ? and passing variables separately) eliminate the risk of malicious SQL injection—always prefer this over string concatenation.
• Error Handling: The ?? null operator ensures we don't get undefined index warnings if a table has no matching data.

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