加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > PHP教程 > 正文

php – 如何从mysql中获取数据以表现

发布时间：2020-12-13 16:10:54 所属栏目：PHP教程 来源：网络整理
导读：所以我想填写并显示一个 HTML列表,即2列宽.但是,无论我尝试什么,我的数据总是紧密相连. 问题是,我实际上想把整个数据放到一个表中,在那里它按起始字母排序. 这是我的代码： ?php include_once 'Database/dbh.php'; ?!DOCTYPE htmlhtml body ?php $fetch = "S
所以我想填写并显示一个 HTML列表,即2列宽.但是,无论我尝试什么,我的数据总是紧密相连.

问题是,我实际上想把整个数据放到一个表中,在那里它按起始字母排序.

这是我的代码：

<?php include_once 'Database/dbh.php'; ?>

<!DOCTYPE html>
<html>
    <body>
        <?php
        $fetch = "SELECT DISTINCT Kategori,LEFT(Kategori,1) AS Letter FROM kategorier ORDER BY Kategori;";
        $result = mysqli_query($conn,$fetch);
        $resultCheck = mysqli_num_rows($result);

        if ($resultCheck > 0) {
            while ($row = mysqli_fetch_assoc($result)) {
                if (!isset($lastLetter) || $lastLetter != $row['Letter']) {
                    $lastLetter = $row['Letter'];
                    echo "<h2>",$row['Letter'],"</h2>";
                }
                echo "<table><tr><td>" . $row['Kategori'] . "</td></tr></table>";
            }
        }
        ?>      
    </body>
</html>

这是现在的情况：

Here is a picture of how it is right now

这是我希望它看起来像：

Here is how I want it to look like

解决方法

我很抱歉让你失望,但解决方案比你想象的要复杂一点.正如我所见,@ Yanber已经建议过了.所以,如果他写了答案,那么首先考虑他的答案是公平的.

建议

关于分离问题：

现在让我首先说,对于未来,你应该熟悉Separation of Concerns原则.简单来说,以您的代码为例：始终将涉及访问数据库的代码(用于获取数据,更新等)与显示数据的代码(例如页面的HTML部分)分开.

这意味着,如果您需要从数据库中获取数据,请在网页顶部执行此操作,并将其保存在数组中.然后只需在网页的HTML部分内使用这些数组,而不是像mysqli_query或mysqli_fetch_assoc等一些与数据库相关的函数.为清楚起见,请参阅我提供的代码的HTML部分(“index.php”).

这种方法的一大优点是,您可以将整个PHP代码从页面顶部移动到php函数或类方法中.然后,数组将只包含调用这些函数/方法产生的数据.

所有上述陈述的要点是什么？随心所欲地使用php代码和网页顶部的数据,并将结果保存在php数组中.最后,数组应该具有这样的结构,即网页的HTML部分的工作将非常简单：只读取和显示数组元素.

所以不要将HTML代码与db相关的代码混合在一起.如果你这样做,那么代码太难维护了.

关于从PHP打印客户端代码：

您应该记住的另一个重要约定是,不使用PHP代码打印任何客户端代码.例如.不要使用诸如echo“< table>< tr>< td>”之类的语句….在这个echo情况下,只需将要呈现的内容保存到变量中,并将其显示在HTML部分中.根据需要的网页.

关于预备陈述：

如果需要使用参数执行sql语句,则使用prepared statements(在这种情况下使用mysqli :: query).他们将保护你的代码免受最终的sql injections.完成后,在这个答案的最后,我发布了一个使用预处理语句而不是mysqli :: query的index.php的例子.

问题解决方案：

脚步：

关于我准备的解决方案,它涉及四个步骤：

>从数据库中获取数据并将其保存到数组($data)中.
>首先,创建第二个数组($formattedData).然后迭代$data并以这种方式将其项目保存到$formattedData中,以便可以非常轻松地在选定的HTML结构中显示(div为字母,table为类别).
>迭代$formattedData并在每个字母的最后一个类别行中为每个缺少的类别附加一个null作为类别名称的项目.对不起,我在这里写了一句话,但是,如果您将阅读我的代码中的评论,您肯定会更清楚地理解我的意思.
>通过迭代$formattedData并读取其值来显示页面的HTML部分中的数据.

当然,您可以根据需要优化PHP代码和/或将其分发为两个三个函数.然后你可以调用它们并将它们返回的值分配给$data和$formattedData变量.

注意：

如果您使用我的连接代码,请不要忘记用您的数据库替换我的数据库凭据.

的index.php

<?php
require 'Database/dbh.php';

$sql = 'SELECT
            DISTINCT Kategori,1) AS Letter
        FROM kategorier
        ORDER BY Kategori';

$result = mysqli_query($conn,$sql);

/*
 * Fetch all data at once,into an array like this:
 *
 *  Array
 *  (
 *      [0] => Array
 *          (
 *              [Kategori] => Artiskok
 *              [Letter] => A
 *          )
 *
 *      [1] => Array
 *          (
 *              [Kategori] => Asiatisk
 *              [Letter] => A
 *          )
 *
 *      [2] => Array
 *          (
 *              [Kategori] => Burger
 *              [Letter] => B
 *          )
 *
 *      [...] => [...]
 *
 *  )
 */
$data = mysqli_fetch_all($result,MYSQLI_ASSOC);

/*
 * Free the memory associated with the result. You should
 * always free your result when it is not needed anymore.
 *
 * @link http://php.net/manual/en/mysqli-result.free.php
 */
mysqli_free_result($result);

/*
 * Close the previously opened database connection. Not really needed because
 * the PHP engine closes the connection anyway when the PHP script is finished.
 *
 * @link http://php.net/manual/en/mysqli.close.php
 */
mysqli_close($conn);

/*
 * Iterate through the fetched data and save it into a new array,with a structure suited for the
 * required HTML display. To each letter,a list of category rows is assigned. The new array will
 * look like this,when the maximal number of categories per category row is 2:
 *
 *  Array
 *  (
 *      [A] => Array
 *          (
 *              [0] => Array
 *                  (
 *                      [0] => Aoiuoiiiu
 *                      [1] => Aqewroiuoiiu
 *                  )
 *
 *              [1] => Array
 *                  (
 *                      [0] => Artiskok
 *                      [1] => Asiatisk
 *                  )
 *
 *              [2] => Array
 *                  (
 *                      [0] => Azkajhsdfjkh
 *                  )
 *
 *          )
 *
 *      [B] => Array
 *          (
 *              [0] => Array
 *                  (
 *                      [0] => Bhaskdfhjkh
 *                      [1] => Biuzutt
 *                  )
 *
 *              [1] => Array
 *                  (
 *                      [0] => Burger
 *                  )
 *
 *          )
 *
 *      [...] => [...]
 *
 *  )
 */
$formattedData = [];

// The maximal number of categories per each category row.
$maximalNumberOfCategoriesPerCategoryRow = 2;

// The number of categories per current category row.
$numberOfCategoriesPerCurrentCategoryRow = 0;

// The index of a category row in the list of all category rows assigned to a letter.
$indexOfCurrentCategoryRow = 0;

foreach ($data as $item) {
    $letter = $item['Letter'];
    $category = $item['Kategori'];

    if (!array_key_exists($letter,$formattedData)) {
        /*
         * Assign an item with the current letter as key and an array as value.
         * The array holds all category rows for the current letter.
         */
        $formattedData[$letter] = [];

        // Reset.
        $indexOfCurrentCategoryRow = 0;

        // Reset.
        $numberOfCategoriesPerCurrentCategoryRow = 0;
    }

    // Append the current category to the current category row for the current letter.
    $formattedData[$letter][$indexOfCurrentCategoryRow][] = $category;

    // Increment.
    $numberOfCategoriesPerCurrentCategoryRow++;

    /*
     * If the maximal number of categories per category row is reached...
     *
     * @see "Modulo" operator at https://secure.php.net/manual/en/language.operators.arithmetic.php
     */
    if (
            $numberOfCategoriesPerCurrentCategoryRow %
            $maximalNumberOfCategoriesPerCategoryRow === 0
    ) {
        // Reset.
        $numberOfCategoriesPerCurrentCategoryRow = 0;

        // Increment.
        $indexOfCurrentCategoryRow++;
    }
}

/*
 * Append an item with "null" as category for each missing category in the last
 * category row of each letter. The array holding the formatted data will look
 * like this,when the maximal number of categories per category row is 2:
 *
 *  Array
 *  (
 *      [A] => Array
 *          (
 *              [...] => [...]
 *
 *              [2] => Array
 *                  (
 *                      [0] => Azkajhsdfjkh
 *                      [1] => null
 *                  )
 *
 *          )
 *
 *      [B] => Array
 *          (
 *              [...] => [...]
 *
 *              [1] => Array
 *                  (
 *                      [0] => Burger
 *                      [1] => null
 *                  )
 *
 *          )
 *
 *      [...] => [...]
 *
 *  )
 */
foreach ($formattedData as $letter => $categoryRows) {
    $lastCategoryRow = end($categoryRows);
    $lastCategoryRowKey = key($categoryRows);

    $numberOfCategoriesPerLastCategoryRow = count($lastCategoryRow);

    $numberOfMissingCategoriesInLastCategoryRow = $maximalNumberOfCategoriesPerCategoryRow -
            $numberOfCategoriesPerLastCategoryRow;

    for ($i = 0; $i < $numberOfMissingCategoriesInLastCategoryRow; $i++) {
        // Append an item with "null" as category.
        $formattedData[$letter][$lastCategoryRowKey][] = null;
    }
}

//=====================================================================================
//@todo Just for testing: uncomment the next two lines to display the arrays on screen.
//=====================================================================================
//echo '<pre>' . print_r($data,TRUE) . '</pre>';
//echo '<pre>' . print_r($formattedData,TRUE) . '</pre>';
//=====================================================================================
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo</title>

        <link href="custom.css" type="text/css" rel="stylesheet">
    </head>
    <body>

        <h3>
            Demo: Print a list of categories per category letter,on multiple columns.
        </h3>

        <?php
        if ($formattedData) { /* Data exists */
            foreach ($formattedData as $letter => $categoryRows) {
                ?>
                <div class="categories-container">
                    <div class="letter">
                        <?php echo $letter; ?>
                    </div>
                    <table class="categories">
                        <?php
                        foreach ($categoryRows as $categoryRow) {
                            ?>
                            <tr>
                                <?php
                                foreach ($categoryRow as $category) {
                                    ?>
                                    <td>
                                        <?php echo $category; ?>
                                    </td>
                                    <?php
                                }
                                ?>
                            </tr>
                            <?php
                        }
                        ?>
                    </table>
                </div>
                <?php
            }
        } else { /* No data */
            ?>
            <p class="no-data">
                No data found
            </p>
            <?php
        }
        ?>

    </body>
</html>

custom.css

body {
    margin: 0;
    padding: 20px;
    color: #333;
}

a {
    text-decoration: none;
}

.categories-container {
    margin-bottom: 10px;
}

.letter {
    padding: 10px;
    text-align: left;
    font-weight: 700;
    background-color: #a0c3e5;
}

.categories {
    width: 100%;
    border-spacing: 1px;
    border-collapse: separate;
}

.categories td {
    width: 50%;
    padding: 10px;
    background-color: #f4f4f4;
}

.no-data {
    padding: 10px;
    background-color: #f4f4f4;
}

数据库/ dbh.php

<?php

/*
 * This page contains the code for creating a mysqli connection instance.
 */

// Db configs.
define('HOST','localhost');
define('PORT',3306);
define('DATABASE','tests');
define('USERNAME','root');
define('PASSWORD','root');

// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors',1); /* SET IT TO 0 ON A LIVE SERVER! */

/*
 * Enable internal report functions. This enables the exception handling,* e.g. mysqli will not throw PHP warnings anymore,but mysqli exceptions
 * (mysqli_sql_exception).
 *
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
 *
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create a new db connection.
$conn = mysqli_connect(HOST,USERNAME,PASSWORD,DATABASE,PORT);

用于测试的数据

CREATE TABLE `kategorier` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,`Kategori` varchar(100) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `kategorier` (`id`,`Kategori`)
VALUES
    (1,'Artiskok'),(2,'Asiatisk'),(3,'Burger'),(4,'Pizza'),(5,(6,(7,(8,'Durum'),(9,(10,(11,'Chinaboks'),(12,'Azkajhsdfjkh'),(13,'Aoiuoiiiu'),(14,'Aqewroiuoiiu'),(15,'Bhaskdfhjkh'),(16,'Biuzutt');

结果

enter image description here

附加内容：

这是如何使用预准备语句而不是mysqli :: query获取数据的示例.请注意,我只在这里提取数据.其余代码与上面index.php页面的omolog部分相同,后者使用mysqli :: query.

的index.php

<?php

require 'Database/dbh.php';

/*
 * Save the values,with which the database data will be filtered,into variables.
 * These values will replace the parameter markers in the sql statement.
 * They can come,for example,from a POST request of a submitted form.
 */
$letterParam1 = 'A';
$letterParam2 = 'C';
$letterParam3 = 'P';

/*
 * The SQL statement to be prepared. Notice the so-called markers,e.g. the "?" signs. They
 * will be replaced later with the corresponding values when using mysqli_stmt::bind_param.
 *
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$sql = 'SELECT
            DISTINCT Kategori,1) AS Letter
        FROM kategorier
        WHERE
            LEFT(Kategori,1) = ?
            OR LEFT(Kategori,1) = ?
        ORDER BY Kategori';

/*
 * Prepare the SQL statement for execution - ONLY ONCE.
 *
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$statement = mysqli_prepare($conn,$sql);

/*
 * Bind variables for the parameter markers (?) in the SQL statement that was passed to prepare().
 * The first argument of bind_param() is a string that contains one or more characters which
 * specify the types for the corresponding bind variables.
 *
 * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
 */
mysqli_stmt_bind_param($statement,'sss',$letterParam1,$letterParam2,$letterParam3
);

/*
 * Execute the prepared SQL statement. When executed any parameter markers
 * which exist will automatically be replaced with the appropriate data.
 *
 * @link http://php.net/manual/en/mysqli-stmt.execute.php
 */
mysqli_stmt_execute($statement);

/*
 * Get the result set from the prepared statement.
 *
 * NOTA BENE:
 *
 * Available only with mysqlnd ("MySQL Native Driver")! If this is not installed,then
 * uncomment "extension=php_mysqli_mysqlnd.dll" in PHP config file (php.ini) and restart
 * web server (I assume Apache) and mysql service. Or use the following functions instead:
 * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
 *
 * @link http://php.net/manual/en/mysqli-stmt.get-result.php
 * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
 */
$result = mysqli_stmt_get_result($statement);

/*
 * Fetch all data at once,MYSQLI_ASSOC);

/*
 * Free the memory associated with the result. You should
 * always free your result when it is not needed anymore.
 *
 * @link http://php.net/manual/en/mysqli-result.free.php
 */
mysqli_free_result($result);

/*
 * Close the prepared statement. It also deallocates the statement handle.
 * If the statement has pending or unread results,it cancels them
 * so that the next query can be executed.
 *
 * @link http://php.net/manual/en/mysqli-stmt.close.php
 */
mysqli_stmt_close($statement);

/*
 * Close the previously opened database connection. Not really needed because
 * the PHP engine closes the connection anyway when the PHP script is finished.
 *
 * @link http://php.net/manual/en/mysqli.close.php
 */
mysqli_close($conn);

/*
 * ---------------------------------------------------------------------------------------------
 * The rest of the page is identical with the omolog part of index.php,which uses mysqli::query
 * ---------------------------------------------------------------------------------------------
 */

// ...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同