duanna3634 2015-05-23 08:56
浏览 308
已采纳

Php下拉菜单通过MySQL显示数据

I've installed the MySQL database using the script as in http://www.databasejournal.com/scripts/practice-sql.html :

Then I have my PHP code learnt from Youtube video about how to populate dropdown with data from SQL, but It still can't work that nothing turns out when clicking the "Show details" submit button. I am still new to PHP and can't sort it out myself. Thank you !!

// PHP Code

<?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>
  • 写回答

2条回答 默认 最新

  • drc15469 2015-05-23 10:19
    关注

    This is a code that works.

    I tried to commented all the modifications i made from ur code

    But first let's look into the query u made :

    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 said doesn't like to be in same query. IN this query the GROUP BY clause "will merge" the result of the query BY cname.

    ORIGINAL :

    But let's suppose we entered 2 clients with the same name (it's possible coz PRIMARY KEY is cdo) and both of these clients ordered something. U'll miss one by using GROUP BY on a column that isn't a PRIMARY KEY.

    BEST way to GROUP BY was on PRIMARY KEY.

    Btw Ur variables names can be tricky (like $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";

    EDIT : see Strawberry's comment (below)

    GROUP BY VS DISTINCT

    I was mistaking about how to build query so i made changes in this way. A better process (for the mind) is running with this query :

    "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>

    Hope that helped.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题？

悬赏问题

  • ¥15 想问一下树莓派接上显示屏后出现如图所示画面，是什么问题导致的
  • ¥100 嵌入式系统基于PIC16F882和热敏电阻的数字温度计
  • ¥15 cmd cl 0x000007b
  • ¥20 BAPI_PR_CHANGE how to add account assignment information for service line
  • ¥500 火焰左右视图、视差（基于双目相机）
  • ¥100 set_link_state
  • ¥15 虚幻5 UE美术毛发渲染
  • ¥15 CVRP 图论 物流运输优化
  • ¥15 Tableau online 嵌入ppt失败
  • ¥100 支付宝网页转账系统不识别账号