Page 1 of 1

Searching and displaying distinct words

Posted: Thu Mar 09, 2006 6:57 am
by hame22
Hi,

I have records for products which include a field for the products keywords.

What I want to do is display a list of all disticnt keywords held within this field.

Any ideas as to how i can do this?

thanks in advance

Posted: Thu Mar 09, 2006 7:02 am
by Chris Corbyn

Code: Select all

select distinct field_name from table_name

Posted: Thu Mar 09, 2006 7:04 am
by hame22
yes but in my keyword field I have values such as keyword1, keyword 2, keyword 3

i need to break these down first and then select distinct values from the individual keywords.

Can this be done?

Posted: Thu Mar 09, 2006 7:09 am
by Chris Corbyn
hame22 wrote:yes but in my keyword field I have values such as keyword1, keyword 2, keyword 3

i need to break these down first and then select distinct values from the individual keywords.

Can this be done?
Ah I see... comma separated.

I think this is out of mysql's hands now so you'll need to use php:

Code: Select all

$query = "select field_name from table_name";
$result = mysql_query($query);
$out = array();
while ($row = mysql_fetch_assoc($result))
{
    $parts = explode(',', $row['field_name']);
    $out = array_merge($out, $parts);
}
$out = array_unique($out);

print_r($out);
If there's a way to do it with mysql it'll be a bit loopy :P

EDIT | Passed array_unique output back to $out (I thought it was done by-reference)

Posted: Thu Mar 09, 2006 7:14 am
by hame22
that looks cool,

what i then wanted to do is for each keyword, make it into a hyperlink so would be displaying:

<a href>Keyword</a>

Can your script be adapted to do this?

Posted: Thu Mar 09, 2006 7:21 am
by Chris Corbyn
hame22 wrote:that looks cool,

what i then wanted to do is for each keyword, make it into a hyperlink so would be displaying:

<a href>Keyword</a>

Can your script be adapted to do this?
Just use a foreach on the resultant array:

Code: Select all

$query = "select field_name from table_name";
$result = mysql_query($query);
$out = array();
while ($row = mysql_fetch_assoc($result))
{
    $parts = explode(',', $row['field_name']);
    $out = array_merge($out, $parts);
}
$out = array_unique($out);

foreach ($out as $keyword)
{
    echo "<a href="#">$keyword</a><br />\n";
}