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

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条)

报告相同问题?