dongyu1125
2016-01-30 03:35
浏览 46
已采纳

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

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

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.

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanpei8853 2016-01-30 06:05
    已采纳

    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.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题