Php下拉菜单通过MySQL显示数据 - Php dropdown menu displaying data via MySQL

我已经使用脚本安装了MySQL数据库，如http://www.databasejournal.com/scripts/practice-sql.html:

然后我有我的PHP代码从Youtube视频学习如何填充下拉与数据从SQL，但它仍然不能工作，没有结果时点击"显示详细信息"提交按钮。我仍然是PHP的新手，不能自己解决它。谢谢!!

//PHP代码

<?php 
require'require.php';
$usersQuery="
    SELECT DISTINCT
        c.cno,
        c.cname,
        o.eno,
        o.shipped
    FROM customers c
    RIGHT JOIN orders o
    ON c.cno=o.cno
    group by (c.cname)
    ";

    $users=$db->query($usersQuery);
    if(isset($_GET['user'])){
        $userQuery="
            {$usersQuery}
            WHERE c.cno=:cno";
        $user= $db->prepare($userQuery);
        $user->execute(['cno'=>$_GET['user']]);
        $selectedUser=$user->fetch(PDO::FETCH_ASSOC);
    }
?>
<!DOCTYPE html> 
<html lang="en">
<head>
<title>Dropbox</title>
<script language=JavaScript>
</script>
</head>
<body>
<h3>My Dropdown</h3>
<form action="dropbox.php" method="get">
    <select name="user">
        <option value="">Choose one</option>
    <?php  foreach($users->fetchAll() as $user):?>
        <option value="<?php echo $user['cno'];?>" <?php echo isset($selectedUser)&& $selectedUser['cno']==$user['cno']? "selected":""?> > <?php echo $user['cname'];?> </option>
    <?php endforeach ?>
</select>
<input type="submit" value="Show details" >
</form>
<?php if(isset($selectedUser)):?>
    <pre><?php echo($selectedUser['cno']);?></pre>
    <?php endif; ?>
</body>
</html>

执行语句有问题。你忘记了:，你必须把数组传递给它。应该是

 $user->execute(array(":cno" =>$_GET['user']));
 $selectedUser=$user->fetch(PDO::FETCH_ASSOC);

读执行

这是一段有效的代码。

我试图注释所有我从你的代码修改

但首先让我们看看你所做的查询:

SELECT DISTINCT c.cno, c.cname, o.eno, o.shipped
FROM customers c
RIGHT JOIN orders o
ON c.cno=o.cno
 group by (c.cname)

DISTINCT and GROUP BY @strawberry表示不喜欢出现在同一个查询中。在这个查询中，GROUP BY子句"将合并"由cname查询的结果。

原始:

但是让我们假设我们输入了两个具有相同名称的客户端(这是可能的，因为PRIMARY KEY是cdo)，并且这两个客户端都订购了一些东西。如果在非主键的列上使用GROUP by，就会错过一个。
GROUP BY的最佳方式是在主键上。
顺便说一句，你的变量名可能很棘手(比如$users &$ user)

Original queries variables :
$usersQuery =  "SELECT c.cno, c.cname
            FROM customers c, orders o
            WHERE c.cno = o.cno
            GROUP BY c.cno";
AND
$userQuery = "SELECT *
                  FROM customers
                  WHERE cno = :cno";

编辑:见Strawberry的评论(下面)

我在如何构建查询上犯了错误，所以我以这种方式进行了更改。一个更好的进程(对于思维来说)正在运行这个查询:

"SELECT DISTINCT c.cno, c.cname, c.street, c.zip, c.phone
            FROM orders o
            LEFT JOIN customers c
            ON o.cno = c.cno"
Then add a WHERE clause when one user is returned by the form :

<?php 
// My include of connecting to my DB - same as ur require.php i suppose 
include("./inc.connect.php");

// As said before 
$usersQuery =  "SELECT DISTINCT c.cno, c.cname, c.street, c.zip, c.phone
                FROM orders o
                LEFT JOIN customers c
                ON o.cno = c.cno";
$users = $db->query($usersQuery);
if(isset($_GET['user']))
{
    // This query will return all informations about the user u selected
    // $userQuery="{$usersQuery} WHERE c.cno=:cno"
    // as @saty said u missed ':' but ur string query 
    //  You included 2 clause WHERE 
    // (from usersQuery and the concatenation) 
    $userQuery = "{usersQuery} WHERE cno = :cno";
    $user = $db->prepare($userQuery);
    $user->execute(array(":cno" => $_GET['user']));
    $selectedUser = $user->fetch(PDO::FETCH_ASSOC);
    // Display the array (<pre> tag make it readable)
    print "<pre>";
    print_r($selectedUser);
    print "</pre>";
}
?>
<!DOCTYPE html> 
<html lang="en">
    <head>
        <title>Dropbox</title>
        <script>
        </script>
    </head>
    <body>
        <h3>My Dropdown</h3>
        <!-- Nothing important : just changed action value coz 
             of my name's page -->
        <form action="index.php" method="get">
            <select name="user">
                <!-- Added "Default" value for first option -->
                <option value="Default">Choose one</option>
                <?php  
                // used echo only to display html tags - 
                // make it cleaner to read
                    foreach($users->fetchAll() as $user)
                    {
                    // Can't make the display Better - SRY
                        echo "<option value='"" . $user['cno'] . "'"";
                        echo (isset($selectedUser) && 
                             ($selectedUser['cno'] == $user['cno'])) 
                             ? "selected" :"";
                        echo ">" . $user['cname'] . "</option>";
                    }
                ?> 
            </select>
            <input type="submit" value="Show details">
        </form>
        <?php 
            if(isset($selectedUser))
                echo "<pre>" . ($selectedUser['cno']) . "</pre>";
        ?>
    </body>
</html>

希望有帮助。