Array stores only one value instead of multiple

Question

I could not figure out how to solve this:

Database:

+-------+-------------+------+
| id    | ArticleNr   | Size | 
+-------+-------------+------+
| 1     | AN111       | L    | 
| 2     | AN111       | XL   |
| 3     | AN222       | M    |
+-------+-------------+------+

sql = "SELECT * FROM table GROUP BY article_number"
$result = $site_db->query($sql);
my_array = array();

while ($row = $site_db->fetch_array($result)) {
    $article_number = $row['article_number'];
    $size = $row['size'];
    $my_array[${'article_number'}] .= $row['size'];
}

As results I got this:

AN111: L
AN222: M

However I want something like this:

AN111: L - XL
AN222: M

Any help will be appreciated

Thanks u_mulder. I have to use group by otherwise I will display all the double. Any suggestion? — Shamss Zaidan
– Shamss Zaidan, Commented Nov 14, 2016 at 14:01

Gordon Linoff · Accepted Answer · 2016-11-14 15:00:02Z

3

I am guessing you want something like this:

SELECT article_number, GROUP_CONCAT(DISTINCT size)
FROM table
GROUP BY article_number;

Unless you really, really, really know what you are doing, don't use SELECT * with GROUP BY. It is not allowed in most other databases and is usually a sign of broken code.

edited Nov 14, 2016 at 15:00

answered Nov 14, 2016 at 14:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shamss Zaidan Over a year ago

Thanks Gordon. Didn't work. It is still displaying 1x size. Do I have to add something else?

Gordon Linoff Over a year ago

@SaraZ . . . Run this in phpadmin and see if the query is returning what you want. It may be how you are reading the data.

Shamss Zaidan Over a year ago

It works for me. Thank you very much. There is an ohter problem. Now I get double in Sizes. If the same article has the same sizes.

Shamss Zaidan Over a year ago

I solve it using GROUP_CONCAT(DISTINCT size). Thank you very much for you help:)

Darren Edwards · Accepted Answer · 2016-11-14 14:51:57Z

1

The returned value from the sql will be the first answer that matches the grouped article number if there are multiple entries for the article number, hence the returned L

I would ORDER BY article_number and forget the GROUP BY clause.

This would return all of the answers to the php parser

$sql = "SELECT * FROM table ORDER BY article_number"
$result = $site_db->query($sql);
$my_array = array();

$article_number = '';
$size = '';
while ($row = $site_db->fetch_array($result)) {
    if($row['article_number']!=$article_number) {
    $article_number = $row['article_number'];
    $size = $row['size'];
    } else {
    $article_number = $row['article_number'];
    $size .= ' - '.$row['size'];
    }
    $my_array[$row['article_number']] = $row['size'];
}

edited Nov 14, 2016 at 14:51

answered Nov 14, 2016 at 14:10

Darren Edwards

315 bronze badges

Comments

Strawberry · Accepted Answer · 2016-11-14 14:26:33Z

0

SELECT * 
  FROM my_table 
 ORDER 
    BY articlenr
     , FIELD(size,'XS','S','M','L','XL','XXL');

The rest of this problem can be solved with a simple PHP loop.

answered Nov 14, 2016 at 14:26

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

Array stores only one value instead of multiple

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related