php教程
js文档Node.jsVue.jsReactjQueryES2015(es6)angularjscss3动画vbs最新电影flutter微信小程序C教程java教程C++教程C#教程php教程sql教程mysql教程android教程ios教程python教程正则表达式

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

发布时间：2022-04-30 发布网站：脚本宝典
脚本宝典收集整理的这篇文章主要介绍了php – 如何从mysql中获取数据以表现，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
所以我想填写并显示一个 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>

这是现在的情况：

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

这是我希望它看起来像：

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

解决方法

我很抱歉让你失望,但解决方案比你想象的要复杂一点.正如我所见,@ 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');

结果

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

附加内容：

这是如何使用预准备语句而不是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_@R_812_1403@ 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
 * ---------------------------------------------------------------------------------------------
 */

// ...

脚本宝典总结

以上是脚本宝典为你收集整理的php – 如何从mysql中获取数据以表现全部内容，希望文章能够帮你解决php – 如何从mysql中获取数据以表现所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：
猜你在找的php教程相关文章
其他相关热搜词更多
微信小程序PHPVueVue.jsPythonPython3SQLJSJavaScriptCSSCSS3Web前端C++C语言HTMLJavaTypeScriptes6鸿蒙OS
全站导航更多
html5HTML/XhtmlCSSXML/XSLTDreamweaver教程Frontpage教程心得技巧JavaScriptASP.NETPHP编程正则表达式AJAX相关ASP编程JSP编程编程10000问CSS/HTMLFlexvbsDOS/BAThtahtcpythonperl游戏相关VBA远程脚本ColdFusionMsSqlMysqlmariadboracleDB2mssql2008mssql2005SQLitePostgreSQLMongoDB星外虚拟主机华众虚拟主机Linuxwin服务器FTP服务器DNS服务器Tomcatnginxzabbix云和虚拟化bios系统安装系统系统进程Windows系列LINUXRedHat/CentosUbuntu/DebianFedoraSolaris红旗Linux建站经验微信营销网站优化网站策划网络赚钱网络创业站长故事alexa域名photoshop教程摄影教程Fireworks教程CorelDraw教程Illustrator教程Painter教程Freehand教程IndesignSketch笔记本主板内存CPU存储显卡显示器光存储鼠标键盘平板电脑安全教程杀毒防毒安全设置病毒查杀脚本攻防入侵防御工具使用业界动态Exploit漏洞分析
最新php教程教程
热门php教程教程