php - 在 PDO 选择查询中组合两列/字段

Question

我的 PDO 语句返回 3 个数据字段，并在 3 列表中显示 3 个字段:

我想调整代码，使显示的表格只有 2 列。

第一列应显示该国家/地区的国旗而不是名称。该标志将位于以下文件夹 site_url(); ?>/wp-content/gallery/Flags/'Country'.png.

第二列应显示两者名字和姓氏。

<?php
//Table
echo "<table style='border: solid 1px orange;'>";
echo "<tr><th>Country</th><th>First Name</th><th>Last Name</th></tr>";
class TableRows extends RecursiveIteratorIterator
    {
        function __construct($it)
            {
                parent::__construct($it, self::LEAVES_ONLY);
            }

        function current()
            {
                return "<td style='width:150px;border:1px solid orange;'>".parent::current()."</td>";
            }

        function beginChildren()
            {
                echo "<tr>";
            }

        function endChildren()
            {
                echo "</tr>" . "\n";
            }
    }

//Connection Info
//Connection Started, Data pulled
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $firstname = $_GET['fname'];
    $stmt = $conn->prepare('SELECT Country, First_Name, Last_Name FROM     tblPlayers WHERE First_Name  = :fname');
    $stmt->bindValue(':fname', $firstname, PDO::PARAM_INT);
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
        echo $v;
    }
}
//Error Check
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Take Text entry and fetch the SQL Row
//Kill Connection 
$conn = null;
echo "</table>";
?> 

感谢您帮助解决我的问题。

Answer 1

正如我在帖子中提到的，为了方便和可读性，我个人更倾向于以不同的方式做一些事情:

I would separate out the connection and querying from the view.

/functions/myfunctions.php

function connect($host = 'myhost',$database = 'mydatabase',$password = 'mypassword',$username = 'myusername')
    {
            $conn   =   new PDO("mysql:host={$host};dbname={$database}", $username, $password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $conn;
    }

function fetch($conn, $sql, $bind = false)
    {
        if(!empty($bind)) {
            $query  =   $conn->prepare($sql);
            $query->execute($bind);
        }
        else {
            $query  =   $conn->query($sql);
        }

        while($result = $query->fetch(PDO::FETCH_ASSOC)) {
            $row[]  =   $result;
        }

        return (!empty($row))? $row : 0;
    }

2) I would include the above then instead of extending iterators, just use a basic loop

/whatever.php

<?php
// Include functions
include_once(__DIR__.'/functions/myfunctions.php');

// Set defaults if the $_GET is no good (user manipulated)
$query      =   0;
$firstname  =   (is_numeric($_GET['fname']))? $_GET['fname'] : false;
$con        =   connect();

if($firstname) {
    $query      =   fetch($con,"SELECT `Country`, `First_Name`, `Last_Name` FROM `tblPlayers` WHERE `First_Name` = :fname",array(":fname"=>$firstname));
}
?>

<!-- CREATE A STYLE SHEET INSTEAD OF INLINE -->
<style>
table.mytable,
table.mytable td    {
    border: 1px solid orange;
}
table.mytable td    {
    width: 150px;
}
</style>

<table class="mytable">
    <tr>
        <th>Country</th>
        <th>Name</th>
    </tr>
<?php   if($query != 0) {
            foreach($query as $person) {
?>  <tr>
        <td><img src="/images/flags/<?php echo $person['Country']; ?>.jpg" /></td>
        <td><?php echo $person['First_Name']; ?> <?php echo $person['Last_Name']; ?></td>
    </tr>
<?php       }
        }
    else {
?>  <tr>
        <td colspan="2">No name selected.</td>
    </tr>
<?php
    }
?></table>

<小时/>

编辑:发布此内容后，我发现@YourCommonSense建议使用concat()，这是一个更好的主意SQL 语句。