多个SQL表与PHP生成站点地图 - multiple SQL tables with PHP to generate sitemap

multiple SQL tables with PHP to generate sitemap

本文关键字：站点地图 PHP SQL 表与多个 | 更新日期: 2023-09-27

我有一个用PHP生成的站点地图，实际上它只调用表零售商，如下所示：

$query = "select * from retailers WHERE status='active' ORDER BY added DESC";

并构造为：

while ($row = mysql_fetch_array($result))
{  
    $i_url = SITE_URL.'loja/'.$row['slug_title'];
    $year = substr($row['added'],0,4);
    $month  = substr($row['added'],5,2);
    $day  = substr($row['added'],8,2);
    $i_date = ''.$year.'-'.$month.'-'.$day.'';
    // you can assign whatever changefreq and priority you like
    // changefreg - optional
    // priority - optional
    echo  
    '
    <url>
    <loc>'.$i_url.'</loc>
    <lastmod>'.$i_date.'</lastmod>
    <changefreq>daily</changefreq>
    <priority>0.8</priority>
    </url>
    ';
}

问题是，只有零售商页面它的到来，我需要再得到一些 3 个表，但我无法想到在其中调用和构建更多东西的方法，也许是 PHP 条件？

感谢大家抽出宝贵时间！

我建议你创建一个函数来处理你需要的查询或子查询

喜欢主代码

while ($row = mysql_fetch_array($result))
{  
    $i_url = SITE_URL.'loja/'.$row['slug_title'];
    $year = substr($row['added'],0,4);
    $month  = substr($row['added'],5,2);
    $day  = substr($row['added'],8,2);
    $i_date = ''.$year.'-'.$month.'-'.$day.'';
    $data = subquery('what i need here', 'another param');
    echo  
    '
    <url>
    <loc>'.$i_url.'</loc>
    <lastmod>'.$i_date.'</lastmod>
    <changefreq>daily</changefreq>
    <priority>0.8</priority>
    </url>
    ';
}
function subquery($firstparam, $secondparam)
{
  $myquery = "SELECT * FROM ".$firstparam;
  //more code
  $result = 'my query result';
  return $result;
}

有了这个，您可以基于

主查询调用子查询，您可以创建更多功能或仅创建一个不同类型的功能，使您能够在一个函数中执行不同的查询。

由于表都具有我们需要的相同字段(slug_name 和 added (，我们可以使用相同的过程遍历每个表，然后输出到 sitemap.xml 文件。

    // Our Echo Buffer
    $buffer = array();
    // Table Names
    $tables = array( 'retailers', 'table2', 'table3', 'table4' );
    // Using MySQLi, cause it's Improved.
    $conn = new MySqli( 'localhost', 'user', 'pass', 'database' );
    // Iterate over $tables
    foreach( $tables as $table )
    {
        // Build Query
        $query = "SELECT `slug_name`, `added` FROM $table" .
                 " WHERE status='active' ORDER BY added DESC";
        // Get Result
        $result = $conn->mysqli_query( $query );
        // Iterate over Result
        while( $row = $result->fetch_assoc() )
        {
            // Chop up the Date
            $date = substr($row['added'],0,4) . '-' .
                    substr($row['added'],5,2) . '-' .
                    substr($row['added'],8,2);
            // Add page details to $buffer
            $buffer[] = '<url>' .
                        '<loc>' . SITE_URL . 'loja/' . $row['slug_title'] . '</loc>' .
                        '<lastmod>' . $date . '</lastmod>' .
                        '<changefreq>daily</changefreq>' .
                        '<priority>0.8</priority>' .
                        '</url>';
        }
        // Free MySQLi Result
        $result->close();
    }
    // Output the Buffer to view. Make sure it looks good.
    echo implode( "'r'n", $buffer );
    // Remove the echo above and uncomment below if it looks good.
    // if( ( $xml = fopen( 'sitemap.xml', "w" ) ) !== FALSE )
    // {
    //     fwrite( $xml, implode( "'r'n", $buffer ) );
    // }