[SOLVED] Pulling distinct from a delimiter...

[jaxdevil]

Ok, this one is tricky. I am trying to pull distinct records but the field I am pulling from has everything broken down as follows and using a '|' as the delimiter between the values...

 

main cat|subcat|subsubcat|subsubsubcat|etc

 

I want to display all of the distinct 'main cat', so I want the query to look for just what is before the first | delimiter. Any ideas? Here is the code I have now..

 

<SELECT NAME="location">
<OPTION VALUE="NONE">-----Select Category-----
<?php
$sql = "SELECT DISTINCT `cats` FROM categories" or die ( "Query failed due to: ".mysql_error());
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
?>
<OPTION VALUE="<?=$row['cats']?>"><?=$row['cats']?>
<?php
}
?>
</SELECT>

[deadonarrival]

Can you just clarify for a second.

 

The information you want is stored in one field, with multiple values seperated by |'s

 

If that's the case

$cats = explode("|",$row['cats']);

$main_cat = $cats[0];
$sub_cat = $cats[1];
$subsubcat = $cats[2];

....etc

 

You don't have to actually run the $main_cat = $cats[0]; - just use the $cats[value] you need, remembering that arrays start at id 0.

 

You can use sizeof($cats) to find how many subcategories are nested, and use a for loop to walk through them, if required.

 

main cat|subcat|subsubcat|subsubsubcat|etc

 

[jaxdevil]

DAMN YOU ARE GOOD! That was quick. I thought this one would take a while. I am pretty kindergarten knowledge on some things, like this one. Well here is what I have now...

 

<SELECT NAME="location">
<OPTION VALUE="NONE">-----Select Category-----
<?php
$sql = "SELECT DISTINCT `cats` FROM categories" or die ( "Query failed due to: ".mysql_error());
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
$cats = explode("|",$row['cats']);
$main_cat = $cats[0];
$sub_cat = $cats[1];
$subsubcat = $cats[2];
$size = sizeof($cats);
?>
<OPTION VALUE="<?=$main_cat?>"><?=$main_cat?>
<?php
}
?>
</SELECT>

 

 

Which does pull up just the main categories, but what I am trying to do is just display distinctive categories. Right now I have say 300 entries of one category, 100 of another, etc. I want it to just display 1 entry for each distinctive category, not display 100 entries for sports, or 300 entries for crafts. Any ideas? I appreciate the help

 

 

[deadonarrival]

That's a little more tricky, since you can't just use mysql's nice distinct keyword.

 

One possibility (although it's a little unwieldy) is to do:

$seen = array();

while($row = mysql_fetch_array($query)) {
    $cats = explode("|",$row['cats']);
    $main_cat = $cats[0];
    if(!in_array($main_cat,$seen) && !in_array($main_cat,$multiple))
    {
        array_push($seen,$main_cat);
    }
    else
    {
        array_push($multiple,$main_cat);
        unset($seen[$main_cat]);
    }
}
$size = sizeof($seen);
for($i=0;$i<=$size,$i++)
{
    print "<OPTION VALUE="$seen[$i]">$seen[$i]</OPTION>";
}

 

I've not tested this, but the theory is there.

 

Basic walk through of code

for each row
    get the category from the set of cat|sub|subsub
    if it has not been seen before, and there has not been more than one (ie it's so far unique)
        put it in our list of "seen" categories
    else (it's been seen before)
        add it to our list of "multiple" categories
        remove it from our list of "seen" categories
    endif
end for

walk through seen list and print them.

 

NOTE: You may have to change the if statement slightly. If you have a problem I'll take another look.

[jaxdevil]

That worked like gang busters! I had to modify one or two things, there was a comma were a semicolon was supposed to be, simple mistake I am sure you were typing fast, and I just took the html part out of the php code altogether, I always like to do that, just to save any mistakes I make. Here it is in final form, you the man!

 

<SELECT NAME="location">
<OPTION VALUE="NONE">-----Select Category-----
<?php
$sql = "SELECT DISTINCT `cats` FROM categories" or die ( "Query failed due to: ".mysql_error());
$query = mysql_query($sql);
$seen = array();

while($row = mysql_fetch_array($query)) {
    $cats = explode("|",$row['cats']);
    $main_cat = $cats[0];
    if(!in_array($main_cat,$seen) && !in_array($main_cat,$multiple))
    {
        array_push($seen,$main_cat);
    }
    else
    {
        array_push($multiple,$main_cat);
        unset($seen[$main_cat]);
    }
}
$size = sizeof($seen);
for($i=0;$i<=$size;$i++)
{
?>
<OPTION VALUE="<?=$seen[$i]?>"><?=$seen[$i]?></OPTION>
<?
}
?>
</SELECT>
<?=$size?>

[deadonarrival]

I always do that in for loops, I'm so used to using commas in function calls.

 

Anyway, glad it helped