CODEWITHSUNDEEP
phpmysql
Dr3am3rz
Dr3am3rz

Reputation: 563

Comparing field data with another table

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

}

Upvotes: 1

Views: 1903

Answers (4)

sel
sel

Reputation: 4957

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

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>\n';

    }
    echo '</select>';

Upvotes: 1

Kanaiya Katarmal
Kanaiya Katarmal

Reputation: 6118

$select=mysql_query(" select * from products_list where id='1' ORDER BY id ASC LIMIT 20 ");
while($row=mysql_fetch_array($select)){
$id = $row["id"]; 
echo $id;
}

Upvotes: 0

Kanaiya Katarmal
Kanaiya Katarmal

Reputation: 6118

use join sql query like eg,

select * from products_list, categories where product_list.id=categories.id ORDER  BY id ASC

 select * from products_list where id=1 ORDER BY id ASC LIMIT 20

Upvotes: 0

Samuel Adam
Samuel Adam

Reputation: 1337

I don't really understand your question, but I'm assuming that it is a simple Read statement.

  1. Simply use PHP echo to display your query result to form.
  2. Same with No. 1
  3. To do comparison, one of many ways is to store your result (category) in a different variables. Lets say $userSelectedCategory and $listCategory (array), and then do a loop to $listCategory array items and comparing it to $userSelectedCategory.

What to do next is totally up to you.

Upvotes: 0

Related Questions