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

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>

图片转代码服务由CSDN问答提供 功能建议

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

然后我从Youtube视频中学习了我的PHP代码,该视频介绍了如何使用SQL中的数据填充下拉列表,但单击“显示详细信息”提交按钮时,仍然无法正常工作。 我还是PHP的新手,无法自己解决。 谢谢!!

// PHP代码

 &lt;?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-&gt; query($ usersQuery); 
 
 if(isset($ _ GET ['user'  ])){
 $ userQuery =“
 {$ usersQuery} 
 WHERE c.cno =:cno”; 
 
 $ user = $ db-&gt; prepare($ userQuery); 
 $ user-  &gt;执行(['cno'=&gt; $ _ GET ['user']]); 
 $ selectedUser = $ user-&gt; fetch(PDO :: FETCH_ASSOC); 
 
} 
 
?&gt;  ; 
&lt;!DOCTYPE html&gt;  
&lt; html lang =“en”&gt; 
&lt; head&gt; 
 
&lt; title&gt; Dropbox&lt; / title&gt; 
&lt; script language = JavaScript&gt; 
 
&lt; / script&gt; 
 
&lt; /  head&gt; 
&lt; body&gt; 
&lt; h3&gt;我的下拉列表&lt; / h3&gt; 
 
&lt; form action =“dropbox.php”method =“get”&gt; 
&lt; select name =“user”&gt;  
&lt; option value =“”&gt;选择一个&lt; / option&gt; 
&lt;?php foreach($ users-&gt; fetchAll()为$ user):?&gt; 
&lt; option value =“&lt;  ;?php echo $ user ['cno'];?&gt;“  &lt;?php echo isset($ selectedUser)&amp;&amp;  $ selectedUser [ 'CNO'] == $用户[ 'CNO']?  “选择的”: “” &GT;?  &GT;  &lt;?php echo $ user ['cname'];?&gt;  &lt; / option&gt; 
 
&lt;?php endforeach?&gt; 
 
&lt; / select&gt; 
 
&lt; input type =“submit”value =“显示详细信息”&gt; 
&lt; / form&gt;  
&lt;?php if(isset($ selectedUser)):?&gt; 
&lt; pre&gt;&lt;?php echo($ selectedUser ['cno']);?&gt;&lt; / pre&gt; 
&lt;  ?php endif;  ?&gt; 
 
&lt; / body&gt; 
 
&lt; / html&gt; 
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

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.

    已采纳该答案
    打赏 评论
  • duanji9378 2015-05-23 09:01

    There is problem is you execute statement. you forget : in it and you have to pass array into it.It would be

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

    Read execute

    打赏 评论

相关推荐 更多相似问题