0

I've been searching for a way to count the amount of numbers in one row column spaced by ",". (But nothing found, I use MySQL and what to count with the use of PHP) This is going to be done for whole the database to find the total amount of numbers (by 100'reds). What I do is storing multiple numbers like the following.

100, 200, 300, 400

What I want is to get the amount of numbers in this column to be 4 and add this to the next row column number. Which could be

200

and therefore equal to 1 + 4 = 5.

To show it the database way see here See database sample if it helps

enter image description here

The total number should then endup beeing 6.

I want to hear if it is possible in someway to do this?

Thanks in advance :)

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Fredy
  • 15
  • 5
  • 2
    Please provide some code snippets, what did you tried? Take a look at php functions exlode & count. – T K Aug 20 '18 at 09:00
  • You might be able to approach this in a different way and count the commas for example https://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-separated-list-mysql . If there aren't any and the column is not null then the value = 1. – P.Salmon Aug 20 '18 at 09:00
  • 2
    `What I do is storing multiple numbers like the following. 100, 200, 300, 400` That I am afraid was your first mistake. See Normalisation in the Relational Databse design for beginners tutorial – RiggsFolly Aug 20 '18 at 09:01
  • Okay, I'll will look at normalisation in the relational database, RiggsFolly. But how would you then store multiple soap orders? If we say a customer wants to order multiple soaps, by soap number, why would it then make sense to have multiple columns called soap for each number (I think it just takes more databasespace?), I'm new to MySQL so I just wonder? :) – Fredy Aug 20 '18 at 09:07
  • But on the plus side, you can access your data with normal SQL :) And could have added up these value with a simple SQL – RiggsFolly Aug 20 '18 at 09:09
  • Yea, that's worth to consider changing the database style then :) But can I make the database generate soap columns depending on the amount stored in each row? :) My concern is, if I make soap1, soap2, soap3 what if i then store 4 soaps? :) – Fredy Aug 20 '18 at 09:26
  • Instead of adding more columns, what you should do, is to create another table which stores the purchased soaps. The table would contain the key to your original table and the purchased soap number. This is normalisation in the relational database. – slaakso Aug 20 '18 at 09:29

2 Answers2

0

To count the amount of numbers in this column you could use following:

$count_of_numbers = sizeof(explode(",",$row['yourColumn']));

Now add em up and you are done ;-)

Dieter Kräutl
  • 657
  • 4
  • 8
0

You can write code as:-

<?php
    $rows = array( '303, 117, 210, 211', '117', '444');
    $count = 0; 
    foreach( $rows as $row ) {
        $count += count(explode(',', $row));
    }   
    echo "total : " . $count;
?>
Sanjeev Chauhan
  • 72
  • 5