Count number of items in a field?

[djfox]

I can tell php to count how many entries in a table, but I can`t figure out how to tell it to count within a field.

 

Goal: Count total number of items that are in people`s inventories.

Example:

Field name is "bought". bought holds the id numbers of virtual items that the person has bought with their virtual money. So there may be repeats. The guy who made this system for me had it set up that the items bought are stored in the field bought. SO it could appear something like: 6,7,6,19,21,20,23,36,49,59,69,69,69,

 

I want the code to be able to count how many times an id number appears in bought in all of the accounts. Is it possible? If so, how do I do it?

[trq]

So there may be repeats. The guy who made this system for me had it set up that the items bought are stored in the field bought. SO it could appear something like: 6,7,6,19,21,20,23,36,49,59,69,69,69,

 

Thats is a terrible database design. You will need to pull all those numbers out, explode them into an array and go from there. Not a pretty or efficient solution.

[Psycho]

SELECT COUNT(DISTINCT bought) FROM tablename

 

Nevermind, I didn't realize all the data was in a single field. Thorpe is correct.

[djfox]

SELECT COUNT(DISTINCT bought) FROM tablename

 

Is it possible to specify an id number with that?

[trq]

That query will not get near what you want. You will need to pull the numbers out of the db and do your calculations within php.

[revraz]

SELECT COUNT(DISTINCT bought) FROM tablename WHERE id = 1

 

SELECT COUNT(DISTINCT bought) FROM tablename

 

Is it possible to specify an id number with that?

[djfox]

SELECT COUNT(DISTINCT bought) FROM tablename WHERE id = 1

 

SELECT COUNT(DISTINCT bought) FROM tablename

 

Is it possible to specify an id number with that?

 

I do that but then the numbers are all 0 when the count is called on (shouldn`t be 0).

 

That query will not get near what you want. You will need to pull the numbers out of the db and do your calculations within php.

 

I`m not sure of what you mean.

[Ken2k7]

SELECT COUNT(DISTINCT bought) FROM tablename WHERE id = 1

That's not possible. The id will most likely never equal to 1. Just do what thorpe says.

 

Edit: djfox, if you're asking for help and someone (especially a mod) gives you the answer, don't just pass it up. Most likely, it would be the best solution. Obviously I'm not guaranteeing it 100%, but their answers are top-notch.

[djfox]

Just do what thorpe says.

 

I don`t understand what he means by his statement.

[Ken2k7]

He's telling you to find the person's inventory using a query, then grab the bought column's data out. Explode the string by commas so you get an array. Then you find out the repetitions of IDs then. Like thorpe says, it's not efficient at all.

[revraz]

It was an example, and an answer to his question.

 

SELECT COUNT(DISTINCT bought) FROM tablename WHERE id = 1

That's not possible. The id will most likely never equal to 1. Just do what thorpe says.

 

[The Little Guy]

How about something like this:

 

<?php
$exp = explode(","$row['fieldName']);
print_r(array_count_values($exp));
?>

[djfox]

Like thorpe says, it's not efficient at all.

 

I`ve heard conflicting statements about that, back when I was trying to work out favourites system for my site. I wanted to have each favourite made by it`s own entry in a table and people in another forum (don`t ask me the name, I haven`t been there in a few months) said that was inefficient and that I should add them all into one field in the person`s account information table. But here you guys say the opposite. *headscratch*

[revraz]

That's because people have different views, and you'll always have more than one way to do something.  You have to decide what's best for you, not someone else.  If you don't follow what they suggest, then they just won't help you any more, but someone else with a different idea might.

 

Its how we learn.

[Ken2k7]

How about something like this:

 

<?php
$exp = explode(","$row['fieldName']);
print_r(array_count_values($exp));
?>

<?php
$exp = explode(",",$row['fieldName']);
print_r(count($exp));
?>

Forgot a comma there. And count($exp) is sufficient enough. It would be easier to query the results and apply mysql_num_rows() to find the answer rather than to split a string and then figure out what number you want to search in the array to get the answer on how many times the number is repeated.

[Lukela]

Truthfully, I think you should redesign the database so later on in the future you dont have to worry about it.

 

Have a table for items, users, and shopping_cart.

 

Than give them id's so items_id, users_id.

 

In the shopping_cart table you will have AT least these columns which will be users_id and items_id. These will be the items they have in the cart. Simple as that.

[The Little Guy]

count will count all the values,

 

array_count_values will count all the DISTINCT values

 

<?php
$array = array(1, "hello", 1, "world", "hello");
print_r(array_count_values($array));
?>

 

Returns:

Array
(
    [1] => 2
    [hello] => 2
    [world] => 1
)

[roopurt18]

Your current database design is what we call unnormalized; look up database normalization and our comments will make more sense.

 

What thorpe is saying is something like this:

  $column = '1,3,5,7,19';
  $column = explode(',', $column);
  echo count($column); // should print 5

Just replace the first line with one that grabs the value from the database.

 

You can count the number of items within this field using pure SQL though:

SELECT
  LENGTH(`column`) - LENGTH(REPLACE(`column`, ',', '')) AS `count`
FROM `the_table`
WHERE
  ...

 

A database redesign will be more efficient in the long run though.

[djfox]

Oh no, I totally know what you guys mean by the other way of setting this up. And I agree totally that it`s easier to work with in the long run. The reason I`m trying to stick with how the guy had it was because the entire doll system he made for me depends on the items listed in bought. I`m not skilled enough to redo it myself nor can I afford to have him (or even another person) redo it.

[roopurt18]

You did see this, right?

SELECT
  LENGTH(`column`) - LENGTH(REPLACE(`column`, ',', '')) AS `count`
FROM `the_table`
WHERE
  ...

[djfox]

You did see this, right?

SELECT
  LENGTH(`column`) - LENGTH(REPLACE(`column`, ',', '')) AS `count`
FROM `the_table`
WHERE
  ...

 

I must be doing something wrong since nothing shows up:

$resb = mysql_query("SELECT
  LENGTH(`bought`) - LENGTH(REPLACE(`bought`, ',', '')) AS `count`
FROM `userdata`
WHERE bought='$c[0]'");
$try = mysql_fetch_row($resb);
mysql_free_result($resb);

[Ken2k7]

I think it should be WHERE count='$c[0]'

[djfox]

It`s gone bonkers saying it`s not a valid argument.

 

Ah never mind it. It was just something for a statistical curiosity anyhow. No big deal. Thanks for trying, guys.

[trq]

The method roopart posted will not count distinct values, it basically counts the length of the string, then removes the commas. The difference should give you the number of items in the string.

[Ken2k7]

What are the columns in your database? Does it contain the user's name or anything that you can use to get the particular user's inventory?