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

Question

After reading and finding online, I still do not have any idea how to solve my problem. And there's no way that I can do like this:

$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";

}
}

Am I right?

Currently I have 2 tables in database products_list and categories.

Table products_list consist of many fields: ID, code, price, category, status, etc...

Table categories only have: ID, category (where this table only contains all the available set of category options)

I have an update/edit form which I use the select statement and get all the results from Table products_list that the user previously saved all their data.

Under the category part on the form itself is a multiple selection listbox.

My problem now is:

1. Display all the result from Table products_list onto the update/edit form
2. Display all the category options from Table categories onto the update/edit form (listbox)
3. Comparing the category that is saved by the user in Table products_list and select the options that is display on the listbox (which is no.2 question)

I really need a lot of help on this as I have never done this before and don't know what's going on reading those articles or threads online. Hope you guys understand my question. Thanks in advance.

Currently updated codings but still stuck:

$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>
';
    }

}

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>
';
}

For category saved in concatenated string format. eg: black,white,red

 // 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>
';

    }
    echo '</select>';