php - MySQL:从查询中获取 "array"或 "object"

Question

我正在为我的 php 项目编写一个小型票务系统，我想获取为特定消息 ID 提供的所有附件。

到目前为止，这是我的查询:

SELECT a.id, 
       a.from, a.to, a.date, 
       a.title,
       a.content,
       GROUP_CONCAT(tma.filename, tma.filepath) 
FROM bestia_messages AS a 
LEFT JOIN bestia_messages_attachments as tma 
       ON a.id = tma.messageid 
GROUP BY a.id
ORDER BY a.date DESC

我的返回看起来像这样:

object(stdClass)#375 (8) { 
    ["id"]       => string(3) "725" 
    ["ticketid"] => string(11) "123456 
    ["from"]     => string(16) "test@example.com" 
    ["to"]=> string(27) "test2@example.com" 
    ["date"]=> string(19) "2015-08-03 14:47:27" 
    ["title"]=> string(17) "Ticket mit Anhang" 
    ["content"]=> string(775) "Ticket mit Anhang…"
    ["GROUP_CONCAT(tma.filename, tma.filepath)"] => string(212) "Test.pngasdaadasasgsdafasdasdasgasfasf,Bildschirmfoto 2015-07-14 um 17.10.43.png52d0833774bc94698e4101c021deebecf5724a4c65d1efc477948180c072c61778b6c0174040cdef6d94d8669251f0a6646c5caf6e1966b9796625979973b2a4.png" }

到目前为止，还不错

`Test.png` is attached to file path `asdaadasasgsdafasdasdasgasfasf`
 and `Bildschirmfoto 2015-07-14 um 17.10.43.png` is attached to file path `52d0833774bc94698e4101c021deebecf5724a4c65d1efc477948180c072c61778b6c0174040cdef6d94d8669251f0a6646c5caf6e1966b9796625979973b2a4.png`.

现在我想用它创建一个数组，这样我就可以像这样在 PHP 中工作:

foreach($mesage->attachment as $attachment)
{
    echo 'Filename: '.$attachment->filename;
    echo 'Filepath: '.$attachment->filepath;
}

我该如何解决这个问题？

Answer 1

如果您使用 PHP MySQL 函数，它们将为您返回一个数组。

这是 W3schools 的一个例子(尽管我讨厌从他们那里推荐任何东西)。

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Run SQL query
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

// output data of each row
while($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"];
    echo "Name: " . $row["firstname"] . $row["lastname"];
}
$conn->close();

参见:http://www.w3schools.com/php/php_mysql_select.asp

如果您避免使用 GROUP BY 和 GROUP_CONCAT() 函数，您的结果将已经是数组形式。

如果您绝对必须使用GROUP_CONCAT，那么您可以使用...分割结果

// Convert a long string (separated by commas) into an array
$attachments = explode(',', $attachments);