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

Question

My PDO statement returns 3 fields of data and displays the 3 field in a 3 column table:

I would like to adjust the code so the table displayed only has 2 columns.

This first column should display the country's flag instead of the name. The flag will be in the following folder site_url(); ?>/wp-content/gallery/Flags/'Country'.png.

The second column should display BOTH First Name and Last Name.

<?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>" . "
";
            }
    }

//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>";
?> 

Thanks for your help solving my problem.

Answer 1

As I mentioned in my post, I think I personally would be more inclined to do a couple things differently for sake of ease and readability:

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>

---

EDIT: After posting this I see that @YourCommonSense suggests to use concat() which is a better idea when it comes to the SQL statement.