更新PHP和查询，以显示数据库PHP Mysqli的餐厅

Question

将旧代码更新为php 7.3.5，并且在类别列表中循环循环。结果应显示可用的每个类别，但仅显示1个类别。没有错误。

样本数据：

州是：南卡罗来纳州，乔治亚州，伊利诺伊州

类别是：商业界发展，教育，基础设施，地方政府，公共建筑，公共安全，居民国家和联邦政府

当前输出为：

<div style="display:inline;"> <a href="/projects.php?state=SC&amp;category_id=21&amp;category_name=Residential&amp;disp_date=2019-03-18"><img src="/gov_images/Residential.jpg" alt="news category Residential" border="0"></a> </div>

这是在PHP 5 ...

上效果很好的旧代码

<?php
		$sql = 'SELECT * FROM state_to_category WHERE state_id="' . $state . '"';
		// echo "<br>&nbsp;<br>Line #: " .  __LINE__ . " - Select SQL: *$sql*<br>n";
		$sql_query = mysql_query($sql, $sql_link) or die("Could not select record: " . mysql_error() );
		// $row_ct = mysql_num_rows($sql_query);
		$state_id_list = "";
		while ($row = mysql_fetch_array($sql_query))
			{
			$state_id_list[]  = $row['cat_id'];
			//	echo "<br> state: " . $row['state_id'] . "  cat: " . $row['cat_id']; 
			}
		$sql = 'SELECT * 
				FROM news_category 
				WHERE type="base" 
				ORDER BY category';
	
		// echo "<br>&nbsp;<br>Line #: " .  __LINE__ . " - Select SQL: *$sql*<br>n";
		$sql_query = mysql_query($sql, $sql_link) or die("Could not select record: " . mysql_error() );
		// $row_ct = mysql_num_rows($sql_query);
		$category_name = "";
		while ($row = mysql_fetch_array($sql_query))
			{
			if ($category_id == $row['id'])
				{
				$category_name = $row['category'];
				}
			$state_found = "N";
			if (is_array($state_id_list))
				{
				foreach($state_id_list as $value)
					{
					if ($value == $row['id'])
						{
						$state_found = "Y";
						}
					}
				}
			if ($state_found == "Y")
				{
				?>
				<div style="display:inline;">
				<a href="<? echo $_SERVER['PHP_SELF'];?>?state=<? echo $state;?>&category_id=<? echo $row['id'];?>&category_name=<? echo $row['category'];?>&disp_date=<? echo $disp_date;?>"><img src="<? echo $row['icon_small'];?>" alt="news category <? echo $row['category'];?>"  border="0" /></a>
				</div>
				<?php
				}
			}
?>	

这是我正在努力更新以在7.3.5 ...

上运行的代码

<?php
		$sql = 'SELECT * FROM state_to_category WHERE state_id="' . $state . '"';
		
		$sql_query = mysqli_query($sql_link, $sql) or die("Could not select record: " . mysqli_error() );
		
		$state_id_list = "";
		while ($row = mysqli_fetch_array($sql_query))
			{	
			$state_id_list = array();
			$state_id_list[]  = $row['cat_id'];
			
			}
		$sql = 'SELECT * 
				FROM news_category 
				WHERE type="base" 
				ORDER BY category';
	
		$sql_query = mysqli_query($sql_link, $sql) or die("Could not select record: " . mysqli_error() );
		
		$category_name = "";
		while ($row = mysqli_fetch_array($sql_query))
			{
			if ($category_id == $row['id'])
				{
				$category_name = $row['category'];
				}
			$state_found = "N";
			if (is_array($state_id_list))
				{
				foreach($state_id_list as $value)
					{
					if ($value == $row['id'])
						{
						$state_found = "Y";
						}
					}
				}
			if ($state_found == "Y")
				{
				?>
				<div style="display:inline;">
				<a href="<? echo $_SERVER['PHP_SELF'];?>?state=<? echo $state;?>&category_id=<? echo $row['id'];?>&category_name=<? echo $row['category'];?>&disp_date=<? echo $disp_date;?>"><img src="<? echo $row['icon_small'];?>" alt="news category <? echo $row['category'];?>"  border="0" /></a>
				</div>
				<?php
				}
			}
?>	

任何帮助都非常感谢！

Answer 1

我在SQL注入同时遇到了同样的问题，因此我更新了所有查询和循环，并且工作得很好

使用PDO构件：

$mysql_pdo = new PDO("mysql:host=localhost;dbname=DATABASE","user", "passwd");
 $query_categories = 'SELECT * 
                FROM news_category 
                WHERE type=:type 
                ORDER BY category';
    $Q_categories = $mysql_pdo->prepare($query_categories);
    $Q_categories->bindParam(':type',"base");
    $Q_categories->execute();
    while($row = $Q_categories->fetch())
    {
    if ($category_id == $row['id']) **<--maybe you lost $category_id on the update?**
                {
                $category_name = $row['category'];
                }
            $state_found = "N";
            if (is_array($state_id_list))
                {
                foreach($state_id_list as $value)
                    {
                    if ($value == $row['id'])
                        {
                        $state_found = "Y";
                        }
                    }
                }
            if ($state_found == "Y")
                {
                ?>
                <div style="display:inline;">
                <a href="<? echo $_SERVER['PHP_SELF'];?>?state=<? echo $state;?>&category_id=<? echo $row['id'];?>&category_name=<? echo $row['category'];?>&disp_date=<? echo $disp_date;?>"><img src="<? echo $row['icon_small'];?>" alt="news category <? echo $row['category'];?>"  border="0" /></a>
                </div>
                <?php
                }
    }

通过这种方式，您准备了代码以进行进一步更新，并将其确定为SQL INSCLI_QUERY被删除，这也许就是为什么您会得到不稳定的行为