如何在mysql数组php中显示每种类型的结果?

I have a php array to display information from a database. I want to be able to show items like this ex. Space Quest (3) The '(3)' represents the number of times found in the database. Right now it is pulling the info and listing the results as many times as shows in the database like this ex. Space Quest Space Quest Space Quest which link to the individual stories. Here is my code:

我有一个php数组来显示来自数据库的信息。我希望能够显示像这样的项目。Space Quest(3)“(3)”表示在数据库中找到的次数。现在，它正在提取信息并列出结果，就像这个前任务空间探索空间探索空间探索链接到各个故事一样。这是我的代码:

<?php
  $type = $_POST['Type'];

  $result = mysqli_query($cxn, "SELECT * 
                                  FROM Stories 
                                 WHERE Type = '$type'");

if($result < 0) {
  echo "<center><b>No stories are posted for this category yet.</b></center>";
} else {
  while($row = mysqli_fetch_array($result)) {
    $title = $row['Title']; 
    $result2 = mysqli_num_rows($result);
    echo "<FORM ACTION ='browse.php' METHOD ='POST'>
            <INPUT TYPE = 'Submit' name='cool2' id='cool2' VALUE = '$title'>";
    echo "&nbsp;&nbsp;<font color = '#0000ff''>(" . $result2 . ")";
    echo "</form>";
    echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
  }
} ?>

This code shows all the items that come from type, but I want to organize the results to show the items only once but then the number of stories in each category.

这段代码显示了来自类型的所有项，但我希望将结果组织为只显示项一次，然后显示每个类别中的故事数。

4 个解决方案

#1

You should run the following query instead

您应该运行以下查询

"SELECT Title,count(*) as cnt FROM Stories WHERE Type = '$type' group by Title"

then you'll only get distinct titles, and the cnt column will contain the number of times it occurs.

然后您将只获得不同的标题，而cnt列将包含它发生的次数。

#2

The issue is that you're using

问题是你在使用

while($row = mysqli_fetch_array($result))

This is going to iterate over all of the rows in the result. Instead, if you just want the first one, do

这将遍历结果中的所有行。相反，如果你只想要第一个，那就去做

$row = mysqli_num_rows($result); 
$occurrences = mysqli_num_rows($result);
// ...

#3

Well, if I understand correctly, what you want is a grouping function. For example:

如果我理解正确的话，你想要的是一个分组函数。例如:

select id, title, count( * ) from stories where type = 'ANYTHING' group by id, title

It's that?

那就是吗?

#4

This is not answer to your question, but it is useful information to know anyway:

这不是对你的问题的回答，但它是有用的信息，无论如何知道:

Your SQL query is vunerable to SQL Injection attacks. This is because you are directly inserting user input (the POST parameter) into your SQL query string. For string values, you should use a string escape function, which ensures the string is inserted safely into the query:

您的SQL查询对SQL注入攻击是不可接受的。这是因为您直接将用户输入(POST参数)插入到SQL查询字符串中。对于字符串值，应该使用字符串转义函数，该函数确保将字符串安全地插入查询:

$query = "SELECT * FROM Stories WHERE Type = '" . mysqli_real_escape_string($type) . "'";

It is also a good idea to escape any values that you output to your HTML code. This helps prevent Cross-Site Scripting attacks. Use the htmlspecialchars() function to do this:

将输出到HTML代码的任何值转义也是一个好主意。这有助于防止跨站点脚本攻击。使用htmlspecialchars()函数完成以下操作:

echo "<INPUT TYPE = 'Submit' name='cool2' id='cool2' VALUE = '" . htmlspecialchars($title) . "'>";

#1