php - 将字段数据与另一个表进行比较

Question

在网上阅读和查找后，我仍然不知道如何解决我的问题。我无法这样做:

$select=mysql_query("select * from products_list categories where id='3'");
while($row=mysql_fetch_array($select)){

echo "whatever fields that I want to display onto the form before category field";

$select=mysql_query("select * from categories ORDER BY id ASC");
while($row=mysql_fetch_array($select)){

echo "compare with the result in categories table and select the option if it's true";

}
}

我说得对吗？

目前我在数据库 products_list 和 categories 中有 2 个表。

表 products_list 由许多字段组成:ID、代码、价格、类别、状态等...

表格类别只有:ID、类别(此表仅包含所有可用的类别选项集)

我有一个更新/编辑表单，我使用 select 语句并从表 products_list 中获取用户之前保存所有数据的所有结果。

在表单本身的类别部分下是一个多选列表框。

我现在的问题是:

1. 将表 products_list 中的所有结果显示到更新/编辑表单中
2. 将表格类别中的所有类别选项显示到更新/编辑表单(列表框)
3. 比较用户在 Table products_list 中保存的类别并选择显示在列表框中的选项(这是第 2 题)

在这方面我真的需要很多帮助，因为我以前从未做过这件事，也不知道在线阅读这些文章或话题会发生什么。希望你们明白我的问题。提前致谢。

当前已更新代码但仍然卡住:

$result2= mysql_query("SELECT category FROM categories ORDER BY id ");
$all_cat = array();
while($row2 = mysql_fetch_assoc($result2)) { 
    $all_cat[] = $row2['category'];
}

//dropdownlist query
$query="SELECT * from products_list where id='100'";
$result = mysql_query ($query);

//populate the dropdownlist

while($row = mysql_fetch_assoc($result)) { 

  $sel_cats = explode(",", $row['category']);
  //determine if the category is selected
  foreach ($sel_cats as $sel_cat){

  $selected = '';

  if ($sel_cat == $all_cat ){
           $selected =' selected="selected" ';
           break;
        }

  }

    foreach($all_cat as $i => $value)
    {
     echo  '<option value="' . $value. '"' . $selected . '>'.$value.'</option>\n';
    }

}

Answer 1

// get all product, assuming the saved category is cat column in product_list table
$result2= mysql_query("SELECT * from products_list where id='3'");
$row2 = mysql_fetch_assoc($result2);
$sel_cat =$row2['cat'];

//dropdownlist query
$query="SELECT cat,id FROM categories ORDER BY id ";
$result = mysql_query ($query);


//populate the dropdownlist
while($row = mysql_fetch_assoc($result)) { 
      //determine if the category is selected
      $selected = ($row['id '] == $sel_cat ) ? ' selected="selected" ' : NULL;
      echo  '<option value="' . $row['id']. '"' . $selected . '>'.$row['cat'].'</option>\n';
}

对于以连接字符串格式保存的类别。例如:黑色、白色、红色

 // get all product, assuming the saved category is cat column in product_list table
    $result2= mysql_query("SELECT * from products_list where id='3'");
    $row2 = mysql_fetch_assoc($result2);
    $sel_cats = explode(',', $row2['cat']);

    //dropdownlist query
    $query="SELECT cat,id FROM categories ORDER BY id ";
    $result = mysql_query ($query);


    //populate the dropdownlist
    echo '<select name="cat" size="5" multiple="multiple">';
    while($row = mysql_fetch_assoc($result)) { 
          //loop thru the sel_cat
           foreach ($sel_cats as $sel_cat){
          //determine if the category is selected
           $selected = '';
           if ($row['id '] == $sel_cat ){
               $selected =' selected="selected" ';
               break;
           }
          }
          echo  '<option value="' . $row['id']. '"' . $selected . '>'.$row['cat'].'</option>\n';

    }
    echo '</select>';