MySql Set Help

sbroad

I am trying to export sets from VARCHAR fields and find unique items.

Ex. Row of Data

|22,23,24 |

|21,19,22 |

Now what I would like to see happen from mysql is that the data is delivered to me as seperate ROWS like this.

22
23
24
21
19
22

If I can get MySQL to do this, I can group and do whatever I like. Mysql seems to have the functions to create comma delimeted sets, but nothing to seperate them out. Please Help!

cgraz

You'd like it displayed as seperate rows? You could pull the info out, then use str_replace() to replace commas with breaks

<?

$query = mysql_query("SELECT field_name FROM yourtable");

while ($a_row=mysql_fetch_array($query)) {

   $data = $a_row["field_name"];
   $new_data = str_replace(",", "<br>", $data);

   echo $new_data . "<br><br>";

}

?>

Per your example, this would output

22
23
24

21
19
22

Hope this helps!

Cgraz

sbroad

$sql="SELECT file_group FROM fileManager GROUP BY file_group";
if ($result=mysql_query($sql))
while($row=mysql_fetch_array($result))
$data.="$row[file_group],";
$result=explode(",",$data);
$result=array_unique($result);

I am really allowing people to put group names to files and then what I want to do, is qeury for all unique group names that people have given to files. A file could have multiple group names such as "web,internal,manager".

So I want mysql to break up the group names from each row and extract them individually and show only uniques. This way, I can create a group list and people can list files based on an individual group.

It is my belief that mysql has the capacity to BREAK sets up since it can create sets.

I would think something exists like this

SELECT BREAK_UP_SET(file_group) as example GROUP BY example.

And this way mysql would go through every row and break up the sets that exist in the field file_group and then I would have a final result of indivdual set names from each row. Makes Sense, right?

cgraz

I am trying to export sets from VARCHAR fields and find unique items.

To export as a set, wouldn't your set need to be a set field, not varchar?

I'm not sure how to export sets, I think there's a command called EXPORT_SET, but I'm not sure, you can look it up if you'd like.

Just off the top of my head, what you could do is select out of your database, use explode() to seperate values and put in array, then use

array_unique() to return a new array without duplicate values. Then output. Something like

<?

$sql = mysql_query("SELECT file_group FROM fileManager GROUP BY file_group"); 

while($a_row=mysql_fetch_array($sql)) {

  $data = $a_row["file_group"];

  if (!$result) {
       $result = explode(",", $data); 
  } else {
      $result .= explode(",", $data);
  }

// end while
}

$new_result = array_unique($result);

echo $new_result;

?>

This hasn't been tested, just putting ideas out off the top of my head. You should be able to work with that with some modifications (if needed) to get what you want.

Let me know how it goes.

Cgraz

sbroad

I tried the EXPORT_SET, and that does not seem to do what I want, at least not as far as I can tell. I Forgot to mention that in the previous post's. Honestly, I do not understand export_set, but it does not seem to do what I want.

If export_set would even return a string of all unique GROUPS that would be fine and then I would allow PHP break the groups up into array based on the delimeter. I just need to get the groups though.

Some of you prob. think I am being hard headed because I already have written a solution to the prob which works for the most part but is not the most efficient method (see prev post). It would seem mysql should be able to accomplish this since it supports sets through functions.

Clearer Example of What I am Tryingto Do

Example DBASE

| FILENAME | GROUP |

| frank.jpg | web,internal,personal |

| header.gif | web,news,public |

Now, I want MYSQL to export the GROUPS individualy and uniquely so I recieve a result like this

web,
internal
personal
news
public

Notice: Web displayed once. Now a person could pick a group name and display relevent rows which contain the group. I cannot use the SET field because I am allowing people to add groups at will so the field is a VARCHAR.

When someone wants to add a new file they can choose from a list of groups that other people have created or create new ones as well.