Page 1 of 1

Duplicate entries in foreach() loop

Posted: Wed Jul 09, 2014 10:26 am
by Sindarin
So pretty much I have two arrays from the database, one for all the platforms that exist and one for all the products.

I need to show all platforms in a <select multiple> and mark as selected any platform that existed in the entry.
Field 'os' in table accepts the values space separated like "5 7 9"

This works, but gives me duplicate results:

Code: Select all

foreach($products_platforms as $entry) {	
    $os = explode(' ', $products_entry['os']);
    foreach ($os as $key) {
        echo '<option value="'.$entry['id'].'"'; if ($key == $entry['id']){ echo ' selected="selected"'; }; echo '>'.$entry['title'].'</option>';
    }
}

Re: Duplicate entries in foreach() loop

Posted: Wed Jul 09, 2014 10:30 am
by Celauran
Sindarin wrote:Field 'os' in table accepts the values space separated like "5 7 9"
That's a red flag right there. You're storing multiple values in a single column?

You may want to create a new array for all the os column values. Iterate over your query results, explode out your os column values, check if they already exist in your new array and add them if they don't. Once that's done, you can use the new array to populate your select list.

Re: Duplicate entries in foreach() loop

Posted: Wed Jul 09, 2014 10:39 am
by Sindarin
In table products_platforms, it contains 2 rows "id" and "title", like
1, Windows
2, Mac OSX
3, Linux
ect.

In table products, column "os" accepts ids like if a product is compatible with Windows and Linux, it'd be "1 3"

Is there any better way to do this?

Re: Duplicate entries in foreach() loop

Posted: Wed Jul 09, 2014 10:51 am
by Celauran
Yes, join tables.

Code: Select all

Products
+------------+
| id | title |
+----+-------+
|  1 | Foo   |
|  2 | Bar   |
|  3 | Baz   |
+------------+

OS
+--------------+
| id |  title  |
+----+---------+
|  1 | Windows |
|  2 | OS X    |
|  3 | Linux   |
+--------------+

product_os
+--------------------+
| product_id | os_id |
+------------+-------+
|          1 |     1 |
|          1 |     3 |
|          2 |     2 |
|          3 |     1 |
+--------------------+

Re: Duplicate entries in foreach() loop

Posted: Wed Jul 09, 2014 11:01 am
by Sindarin
I don't really get the table structure above, do I need 3 tables to join? Will this save me an extra query to query once the products and once the product_platforms tables?

I pretty much solved my issue with in_array,

Code: Select all

$os = explode(' ', $products_entry['os']);

foreach($products_platforms as $entry) {
    echo '<option value="'.$entry['id'].'"'; if (in_array($entry['id'], $os)){ echo ' selected="selected"'; }; echo '>'.$entry['title'].'</option>';
}

Re: Duplicate entries in foreach() loop

Posted: Wed Jul 09, 2014 12:03 pm
by Christopher
Sindarin wrote:I don't really get the table structure above, do I need 3 tables to join? Will this save me an extra query to query once the products and once the product_platforms tables?
As Celauran showed, the usual way to implement these kinds of relations is to have an intermediate link table. Like Celauran show, that table is usually named by combining both table names like table1_table2. In the link table are key pairs for the keys for each table. So each record in the link table connects one record in table1 with one record in table2. To fetch results you JOIN the link table with one or both data tables and SELECT WHERE either table1_id or table2_id is equal to a specific value.