Question about Array/Other functions to compare data.

[Travis1128]

Hello. I want to start off by thanking you for reading my question.

 

The question I have today involves two mysql tables

 

Product_Categories

ID

Cat_ID

Product_ID

1

100

201

2

101

201

3

102

201

 

and..

 

Categories

 

ID

Name

100

Demo 1

101

Demo 2

102

Demo 3

103

Demo 4

 

I want to gather through MySQL the two tables and compare the data based on the product_id, and output it like this according to the example above.

 

Demo 1 SELECTED

Demo 2 SELECTED

Demo 3 SELECTED

Demo 4 NOT SELECTED

 

If this is too confusing and need me to explain better please let me know. I am completely stumped at this point and have tried to come up with a logical/theoretical explanation on how to achieve the said above.

[Psycho]

SELECT C.Name`, IF(C.ID = PC.Cat_ID,1, 0) AS selected
FROM Categories AS C
LEFT JOIN Product_Categories AS PC
  ON C.ID = PC.Cat_ID

 

EDIT: Hold on, I don't think that's right. Let me do some checking.

 

EDIT #2: Nope, it works.

[Travis1128]

Thank you for your swift reply mjdamato.

 

The code you sent me gives me

 

"Unknown table 'C' in field list" Error when queried.

[fenway]

Unmatched backticks, to start.

[Travis1128]

Alright I fixed the issue with the back tick's, however the display output it only displaying the numbers regardless.

 

Let me show you in an example how it's ultimately going to be outputted as. The part I need help on is the output and gathering because I may be using the code provided wrong.

 

Product Categories

<select name='p_categories' multiple='multiple'>
<option value='100' selected>Demo 1</option>
<option value='101' selected>Demo 2</option>
<option value='102' selected>Demo 2</option>
<option value='103'>Demo 3</option>
<option value='104'>Demo 4</option>
</select>

 

Now as you can see in the select multiple, the categories associated with the product_id are selected and output a "selected" string to the html form.

[Psycho]

Where is the code you are using to generate the select list? The query I provided would return two fields: 1) the name from the category table and 2) a dynamic field "selected" that will be a 1 or 0. You need to use that second field to determine whether the record is selected or not (using the correct HTML format). Also, that query will need to be changed to also get the category id.

 

Sample code

<?php

$query = "SELECT C.ID, C.Name, IF(C.ID = PC.Cat_ID,1, 0) AS selected
          FROM Categories AS C
          LEFT JOIN Product_Categories AS PC
            ON C.ID = PC.Cat_ID
          ORDER BY C.Name";
$result = mysql_query($query);

//Create options list
$catOptions = '';
while($option = mysql_fetch_assoc($result))
{
    $selected = ($option['selected']==1) ? ' selected="selected"' : '';
    $catOptions .= "<option value=\"{$option['ID']}\">{$option['Name']}</option>\n";
}
?>

<select name="p_categories">
<?php echo $catOptions; ?>
</select>

[Travis1128]

The code provided works to an extent. But it deviates from what is necessary. Lets look at what is needed,

 

I will show you in an IF & Statement

 

IF ?p_id is EQUAL too category_id THEN

  $option = "selected='selected'";

END IF

 

Basically the code you provided is printing double output of each category and none of the options are selected even when using the $selected option and converting to read the ?p_id option.

 

So in the output part it would look like this,

 

<select name='p_categories' multiple='multiple'>

 

<option value='100'

  IF ?p_id (100) EQUALS category_id (100) THEN

    $option = "selected='selected'";

  END IF

>Demo Category 1</option>

<option value='101'

  IF ?p_id (101) EQUALS category_id (101) THEN

    $option = "selected='selected'";

  END IF

>Demo Category 2</option>

<option value='103'

  IF ?p_id (103) EQUALS category_id (103) THEN

    $option = "selected='selected'";

  END IF

>Demo Category 3</option>

<option value='104'

  IF ?p_id (104) EQUALS category_id (104) THEN

    $option = "selected='selected'";

  END IF

>Demo Category 4</option>

</select>

 

The above is basically how it should be outputting not just for those categories but ANY category listed within the `categories` table. So we're comparing the data retrieved from `products` and finding if the data is like or equal too the categories within the final comparison function. If it does find a match from the products_categories listed then it will output the "selected='selected'" string for each option that is related to the category selected.

 

Example SQL,

 

$sql = "SELECT * FROM product_categories WHERE product_id = '". $pid ."'";

 

That is not the exact SQL, but an example of what it's retrieving from the product_cateogires.

[Travis1128]

Let me know if this is too complicated. I am trying to figure out how to simplify this as much as possible. I want to help you in anyway I can.

 

- Travis

[Psycho]

Too complicated? No, I don't think so. Trying to put a bunch of IF statements for each and every option is unnecessary. I already provided that logic in the code I posted previously using the ternary operator.

 

I asked YOU to display the code you were using and provided some "sample" code. I didn't say it would work out of the box because I have no idea what you are doing in the code since you have provided absolutely no code.

 

Based upon the details you have provided the logic I have provided will work if implemented correctly. I explained what the results of the query I provided should return. Did you test the query and check the results to see if they are valid for waht you need, or did you simply copy/paste because you are too lazy to think for yourself?

 

The only way that the options in the select list would be duplicated is if there are duplicates returned from the query (which shouldn't occur if they are unique records in the Categories table) OR if you left in hard coded values in that list.

 

Why don't you start by running the query and pasting the results here. BY the way, what is "p_id"? I presume the product id, but you did not refer to that anywhere previously. based upon your first post you wanted a select list of every category and if the category was referenced in the Product_Categories table, then you wanted it selected.

[Travis1128]

I have been messing with your code, but with my experimentation I have not found a way to make it work the way I want.

 

?p_id is the $_GET['p_id'];

 

i.e., Http://www.YourDomain.com/products.php?p_id=10

 

Here is the code I have been messing around with it is your's, however I am trying to get it to display correctly the information I need.

 

$query = "SELECT C.ID, C.Name, IF(C.ID = PC.Cat_ID,1, 0) AS selected
          FROM Categories AS C
          LEFT JOIN Product_Categories AS PC
            ON C.ID = '". $_GET['p_id'] ."'
          ORDER BY C.Name";
$result = mysql_query($query);

//Create options list
$catOptions = '';
while($option = mysql_fetch_assoc($result))
{
    $selected = ($option['selected']==1) ? ' selected="selected"' : '';
    #$catOptions .= "<option value=\"{$option['ID']}\">{$option['Name']}</option>\n";
#$catOptions .= "<option value='". $option['ID'] ."'>". $option['Name'] ."</option>";
$catOptions .= "<option>". $option['selected'] ."</option>";
}

?>

<select name="p_categories" multiple='multiple'>
<?php echo $catOptions; ?>
</select>

 

It should get the ?p_id, find the product in product_categories, every row. List which row it's in (does not matter) how many times. Then you gather the category table rows and display them. The rows of which the product is listed from product_categories those rows in the select box will be selected or highlighted.

 

I am sorry if I am causing an inconvenience and I want you to know that I have been experimenting all night with the code.

 

- Travis

[Psycho]

Had you mentioned that "p_id" was being passed and was needed in the query you would have saved us both a lot of time. That explains why you are getting multiple entries - the foreign keys in the "Product_Categories" table have duplicates. You should just need to modify the query. I assume there is only one entry for each category in the "Product_Categories" table associated with any particular "p_id".

 

<?php
$p_id = int_val($_GET['p_id']);

$query = "SELECT C.ID, C.Name, IF(C.ID = PC.Cat_ID, 1, 0) AS selected
          FROM Categories AS C
          LEFT JOIN Product_Categories AS PC
            ON C.ID = PC.Cat_ID AND PC.Product_ID = $p_id
          ORDER BY C.Name";
$result = mysql_query($query);

//Create options list
$catOptions = '';
while($option = mysql_fetch_assoc($result))
{
    $selected = ($option['selected']==1) ? ' selected="selected"' : '';
    $catOptions .= "<option value=\"{$option['ID']}\">{$option['Name']}</option>\n";
}
?>

<select name="p_categories">
<?php echo $catOptions; ?>
</select>

 

If this doesn't work, then start with the query. Test the results to see if they make sense. If not, then we need to adjust the query. If they do make sense then there may be a problem in the PHP code that parses the results

[Travis1128]

You're a Beast. Thank you for your help mjdamato. This code you provided works perfectly and is what I need. So far it is doing exactly what I want it to, and that is selecting and highlighting the associated categories.

 

Code used: Provided by mjdamato

$p_id = $_GET['p_id'];
$query = "SELECT C.ID, C.Name, IF(C.ID = PC.Cat_ID, 1, 0) AS selected
          FROM Categories AS C
          LEFT JOIN Product_Categories AS PC
            ON C.ID = PC.Cat_ID AND PC.Product_ID = $p_id
          ORDER BY C.Name";
$result = mysql_query($query) or die(mysql_error());

//Create options list
$catOptions = '';
while($option = mysql_fetch_assoc($result))
{
    $selected = ($option['selected']==1) ? ' selected="selected"' : '';
    $catOptions .= "<option value=\"{$option['ID']}\" $selected>{$option['Name']}</option>\n";
}
?>

<select name="p_categories" multiple='multiple'>
<?php echo $catOptions; ?>
</select>

[Psycho]

So far it is doing exactly what I want it to...

 

"So far"? No confidence huh?

 

You're welcome. I'm marking this as solved. Please do so in the future with the button at the bottom when your posts are solved.