时间戳

首页 > 解决方案 > 有没有基于回显值的PHP查询解决方案

php - 有没有基于回显值的PHP查询解决方案

问题描述

我试图根据我从“项目”表中获得的 project_title 计算“用户”表中的用户名列（不同），在“项目”表上，我的用户名列与用户表上的用户名相同

项目名称已成功显示，但我没有成功根据项目名称计算用户名数量，

下面是我使用的代码

<tbody>
<?php 
    $no = 1;
    $data = mysqli_query($connect,"select * from project");
    while($d=mysqli_fetch_array($data)){
    ?>
        <tr style="text-align:center;">
            <td><?php echo $no++; ?></td>
            <td style="width:125px;" name="title"><?php echo $d['project_title']; ?></td>

                <?php
                $topice = $_GET['title'];
                foreach($connect->query("SELECT COUNT(DISTINCT (username)) FROM user where topic='$topice'") as $row) {
                    echo "<td>" . $row['COUNT(DISTINCT (username))'] . "</td>";
                }
                ?>

        </tr>
        <?php 
    }
?>
</tbody>

期望获得 WinBali = 3 和 WinITB = 2 的用户名计数

基于 MySQL 数据

标签： phpmysql

解决方案

这将使您深入了解如何创建表和运行您的应用程序

create table nancy_project(project_id int primary key auto_increment,project_title varchar(30),project_name varchar(30),username varchar(30));


create table nancy_user(user_id int primary key auto_increment,username varchar(30),project_id varchar(30),counter int(11));

您现在可以插入

insert into nancy_project(project_title,project_name,username) values('Chemistry Project','This is for Chemistry lecturers','WinBali');
insert into nancy_project(project_title,project_name,username) values('Physics Project','This is for Physics stsudents','WinITB');

请记住project_id从 1 开始自动递增，因此不包含在插入语句中

WinBali 对项目 id 1 进行的计数 = 3 

insert into nancy_user(username,project_id,counter) values('WinBali',1,1);
insert into nancy_user(username,project_id,counter) values('WinBali',1,1);
insert into nancy_user(username,project_id,counter) values('WinBali',1,1);

WinITB = 2 在项目 id 2 上进行的计数

insert into nancy_user(username,project_id,counter) values('WinITB',2,1);
insert into nancy_user(username,project_id,counter) values('WinITB',2,1);

注意：除非您确保项目标题是唯一的，否则您将按project_id而不是project_title进行查询。

这是代码

     <?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "dbname goes here"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);

// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}


               //$username ='WinBali';
               //$username ='WinITB';
                $query = "SELECT * FROM nancy_project";
                $result = mysqli_query($con,$query);
                while($row = mysqli_fetch_array($result)){
                    $project_id = $row['project_id'];
                    $project_title = $row['project_title'];
                    $project_name = $row['project_name'];
                        $username = $row['username'];

                    // Count the counter
// you can try different queries commented
$query1 = "SELECT COUNT(*) AS cntStatus FROM nancy_user where username='$username' and project_id='$project_id'";
                    //$query1 = "SELECT COUNT(*) AS cntStatus FROM nancy_user where username='$username'";
                    $result1 = mysqli_query($con,$query1);
                    $row1 = mysqli_fetch_array($result1);
                    $total_count = $row1['cntStatus'];


            ?>
                    <div class="post">
 <h1 style="color:green"><?php echo $username; ?></h1>
                        <h1><?php echo $project_title; ?></h1>
                        <div class="post-text">
                            <?php echo $project_name; ?>
                        </div>
                        <div class="post-action">

                            (<span><?php echo $total_count; ?></span>)<br>


                        </div>
                    </div>
            <?php
                }
            ?>

        </div>
    </body>
</html>

这是结果的屏幕截图

截屏

当请求来自表单输入时，请记住执行清理和验证，以避免sql 注入、csrf、xss、html 注入等漏洞。

推荐阅读