如何在while循环中从mysqli\u fetch\u array()中检索当前和下一个元素?

plupiseo  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(241)

我有一个表,可以有多个相同的记录。所以我想做一些任务,如果有多个记录是相同的。在这种情况下我怎么能检查呢。

<div>
<table class="table">

<thead class="table table-bordered" style="background-color:#EBF5FB;">
  <tr>
    <th>Name</th>
    <th>Locations</th>
    <th>Type</th>
    <th>Number</th>
    <th>Users</th>
    <th>Edited</th>
    <th>ID</th>
  </tr>
</thead>

<div class="pre-scrollable">
<tbody id="myTable">

 <?php
        include_once 'Connection.php';
        $check_test=mysqli_query($GLOBALS['db'],"select * from test ORDER bY id DESC");

        while ($row = mysqli_fetch_array($check_test)) {
            echo '<td>'.$row['name'].'</td>';
            echo '<td>'.$row['region'].'</td>';
            echo '<td>'.$row['number'].'</td>';
            echo '<td>'.$row['rollno'].'</td>';
            echo '<td>'.$row['created'].'</td>';
            echo '<td></td>';
            echo '</tr>';
        }   
    ?>
</tbody>
</div>

</table>
</div><br/>

表行示例:

'genarate id', 'us-west-4', 'Test', '1', '15', '2018-06-06 23:43:02','93'

'genarate id', 'us-east-3', 'Test', '1', '15', '2018-06-06 23:43:02', '93'

如果行是这样的,我需要像这样打印一次记录

'genarate id', 'us-east-3 , us-west-4', 'Test', '1', '15', '2018-06-06 23:43:02',  '93'
mlnl4t2r

mlnl4t2r1#

不是在php中,而是在sql中,您可以更改查询以执行以下操作:

SELECT name, email, address, rollno, count(*) as nb
FROM test
GROUP BY name, email, address, rollno
ORDER BY id DESC

然后在php中,只需显示数字:

while ($row = mysqli_fetch_array($check_test)) {
            echo $row['name'];
            echo $row['email'];
            echo $row['address'];
            echo $row['rollno'];
            echo $row['nb'];
 }

由于您的评论,您正在寻找的是组\u concat,以下是查询:

SELECT GROUP_CONCAT(name) as names, email, address, rollno
FROM test
GROUP BY email, address, rollno
ORDER BY id DESC

因为你没有写你想要连接的字段,我选择了name,但是如果你也改变了组,你可以为任何一列写。要了解有关函数组concat的更多信息,请参阅手册:group concat

h6my8fg2

h6my8fg22#

You can get the distinct records by using SELECT DISTINCT statement.

<?php
    include_once 'Connection.php';
    $check_test=mysqli_query($GLOBALS['db'],"select DISTINCT name, email, address, rollno from test ORDER bY id DESC");

    while ($row = mysqli_fetch_array($check_test)) {
        echo $row['name'];
        echo $row['email'];
        echo $row['address'];
        echo $row['rollno'];
    }   
?>

或者,如果您想知道哪些记录是重复的,那么-您可以使用此sql查询

SELECT 
  first_name, COUNT(first_name),
  last_name,  COUNT(last_name),
  email,      COUNT(email)
FROM
  contacts
GROUP BY 
  first_name , 
  last_name , 
  email
HAVING  COUNT(first_name) > 1
AND COUNT(last_name) > 1
AND COUNT(email) > 1;

相关问题