如何基于PHP将数据库查询得到的教师名称附加到Laravel查询返回的数组对应元素中

Hey there! Let's break down how to convert your Laravel query logic to pure PHP, while fixing a few issues in the original code and optimizing performance.

First, Let's Cover the Core Problem #

Your goal is to take the $data array of news items, use each item's teacher_id to fetch the corresponding teacher name, and attach that name back to the news item.

Step 1: Set Up a PDO Database Connection #

Pure PHP typically uses PDO for secure, reliable database interactions. Start by establishing a connection (adjust the credentials to match your database):

$dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
$dbUser = 'your_db_username';
$dbPass = 'your_db_password';

try {
    $pdo = new PDO($dsn, $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Failed to connect to database: " . $e->getMessage());
}

Step 2: Fetch Initial News Data #

First, get all your news records just like you did in Laravel, but using PDO:

// Fetch news ordered by created_at descending
$stmt = $pdo->query('SELECT * FROM news ORDER BY created_at DESC');
$data = $stmt->fetchAll(PDO::FETCH_ASSOC); // This gives you the array structure you shared

Step 3: Optimized Approach (Avoid N+1 Queries) #

Your original Laravel code uses a loop to run a separate query for each teacher—this is an N+1 query problem (1 query for news, N queries for teachers), which is slow with large datasets. Instead, we'll fetch all needed teachers in one single query:

1. Collect all unique teacher_ids from the news data:

   // Extract all teacher_ids from the news array
   $teacherIds = array_column($data, 'teacher_id');
   // Remove duplicates to reduce the number of rows we need to fetch
   $uniqueTeacherIds = array_unique($teacherIds);
   

2. Batch-fetch the teachers using the unique IDs:

   // Create placeholders for the IN clause (one ? per ID)
   $placeholders = implode(',', array_fill(0, count($uniqueTeacherIds), '?'));
   $stmt = $pdo->prepare("SELECT id, name FROM teachers WHERE id IN ($placeholders)");
   $stmt->execute($uniqueTeacherIds);
   // Fetch as a key-value array (teacher ID => teacher name) for easy lookup
   $teacherMap = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
   

3. Attach the teacher name to each news item:

   // Loop through each news item and add the teacher name
   foreach ($data as &$newsItem) {
       // Use the teacherMap to get the name, default to "Unknown" if no match exists
       $newsItem['teacher_name'] = $teacherMap[$newsItem['teacher_id']] ?? 'Unknown';
   }
   unset($newsItem); // Unset the reference to prevent accidental array modifications later
   

Alternative (Not Recommended): Per-Item Queries #

If you absolutely need to run a query for each news item (only do this for small datasets!), here's how:

foreach ($data as &$newsItem) {
    $stmt = $pdo->prepare('SELECT name FROM teachers WHERE id = ?');
    $stmt->execute([$newsItem['teacher_id']]);
    $teacher = $stmt->fetch(PDO::FETCH_ASSOC);
    $newsItem['teacher_name'] = $teacher ? $teacher['name'] : 'Unknown';
}
unset($newsItem);

Key Fixes from Your Original Laravel Code #

• Your original code had a variable typo: $rows vs $row in the loop.
• In pure PHP, $data is an array of associative arrays (not objects), so you use $newsItem['teacher_id'] instead of $row->teacher_id.
• You can't attach a property to the entire $data array—you need to modify each individual item in the array.

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