Mysql Results and array manipulation

Question

I am stuck working on a problem, and would appreciate some guidance. I am working with an old system that was coded awfully, and didnt do any validating, or sanitzing of user input, so the database I'm working with is a bit missy.

I have issues with one column called "tags", used for tags for articles. But each row appears like this, and varies between them:

tag tag1 tag2 tag3

OR

tag1, tag2, tag

But I need to combine them and put them into an array that lists them by how many times each one occurs, like this:

tag(2)
tag1(2)
tag2(2)
tag3(1)

Because I'm not the most astute with php, Im not sure if I am going about the problem correctly?

Im new to manipulating arrays on this scale which is why I am stuck at this point. You can view the example below of what I am doing below:

  $sql = "SELECT tags, approved FROM articles WHERE approved = '1' ";
  $result = mysql_query($sql);

 while( $rows = mysql_fetch_array($result) ) { 
  $arr = $rows['tags'];
  $arr = str_replace(", ", " ", $arr); // attempting to clean up the data, so that each word appends with a space.
  $arr = str_replace(" ", " ", $arr);

  // Don't know what to do next, or if this is even the right way to do it.

}

Any help is appreciated.

Thanks, Lea

I think I get stuck because the script outputs an array for each row, and Im not sure how to go about manipulating, and all of my attempts of the past hour have failed — Lea
– Lea, Commented Apr 5, 2011 at 15:39
You can convert a string into an array with the explode function. — FrustratedWithFormsDesigner
– FrustratedWithFormsDesigner, Commented Apr 5, 2011 at 15:40
@FrustratedWithFormsDesigner - True, but that doesn't deal with the way each row of data is formatted. It places it into an array like: array() { [0] => tag, tag1, tag3, tag4 [1] => tag, tag2, tag6, tag7 } — Lea
– Lea, Commented Apr 5, 2011 at 15:45

Aaron W. · Accepted Answer · 2011-04-05 16:11:13Z

2

Maybe this will be helpful. Modified your code to have a $tagArr for each query. If you need an overall array of tags it would be a bit different, but could easily be coded using the following.

while( $rows = mysql_fetch_array($result) ) { 
  $arr = $rows['tags'];
  $arr = str_replace(", ", " ", $arr); // attempting to clean up the data, so that each word appends with a space.

  // Don't know what to do next, or if this is even the right way to do it.

  $tagArr = array();
  $current_tags = explode(" ", $arr);
  foreach($current_tags as $tag) {
    if(!array_key_exists($tag, $tagArr)) {
      $tagArr[$tag] = 1;
    } else {
      $tagArr[$tag] = $tagArr[$tag]++;
    }
  }

  // now $tagArr has the tag name as it's key, and the number of occurrences as it's value
  foreach($tagArr as $tag => $occurrences) {
    echo $tag . '(' . $occurrences . ')<br />';
  }
}

edited Apr 5, 2011 at 16:11

answered Apr 5, 2011 at 15:50

Aaron W.

9,2952 gold badges36 silver badges47 bronze badges

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

3 Comments

simshaun Over a year ago

No need for the str_replace space with space line.

Aaron W. Over a year ago

@Lea Updated the code. I just traversed through the new $tagArr array and echoed the information. Also removed the unneeded str_replace.

Lea Over a year ago

Aaron W. - I see now. Thanks for your help:)

Collectives™ on Stack Overflow

Mysql Results and array manipulation

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related