Listing unique entires from a field

frazer · Oct 1, 2001

Hi Guys,

Don't know if this can be easily done or not. I've currently a database where one of the fields contains a range of keywords for searching. Does anyone know of anyway I can search through the entire database and display a list of all the keywords entered into this field - but each one only once?

Thanks in advance,

Frazer

NeffTribe · Oct 1, 2001

In the select statement to your database, do a

SELECT DISTINCT...

-David

Broccoli2 · Oct 2, 2001

Frazer,

I'm guessing you've got something like the following:

Code:

+----------------+
| keyword        |
+----------------+
| cat            |
| dog            |
| cat rabbit     |
| dog cat rabbit |
| dog            |
+----------------+

I don't know of any easy way to do it in SQL (without doing multiple selects). I think your best bet would be to do a

Code:

SELECT DISTINCT

and then process the results in PHP, maybe using something like:

Code:

$keywords=Array();
$sql = &quot;SELECT DISTINCT keyword FROM mytable&quot;;
if (!$r=mysql_query($sql)) {error_report($sql);} else {
  while ($row=mysql_fetch_assoc($r)) {
    $row = explode($row[keyword]);
    while (list(,$keyword)=each($row)) $keywords[]=$keyword;
  }
  mysql_free_result($r);
}

This would leave you with an array

Code:

$keywords

of unique values.

This isn't exactly pretty though, anyone got a better idea?

-Rob

frazer · Oct 2, 2001

Thanks Guys, seems to be heading in the correct direction.

RobEyre: you're spot on about the format of the data in the field.

I seem to be going through a total duh! phase just now (must be the Scottish weather). My test page looks like this:

<html>
<head>
<title>Keyword Test</title>
<body bgcolor="#FFFFFF" text="#000000">

<?php

$db = mysql_connect("myhost", "myusername", "mypassword&quot

;
mysql_select_db("mydatabase",$db);

$keywords=Array();
$sql = "SELECT DISTINCT column FROM table";
if (!$r=mysql_query($sql)) {error_report($sql);} else {
while ($row=mysql_fetch_assoc($r)) {
$row = explode($row[column]);
while (list(,$keyword)=each($row)) $keywords[]=$keyword;
}
mysql_free_result($r);
}

?>
</body>
</html>

Where column is where my keywords are held and table is the table in the database.

I'm getting an error stating:

"Wrong parameter count for explode() on line 15
Variable passed to each() is not an array or object on line 16"

This is repeated over and over again.

I guess I'm doing something wrong but am too brain dead just now to realise what it is. Any help greatly appreciated.

Thanks again,

Frazer

Broccoli2 · Oct 3, 2001

Frazer

Sorry, my mistake.

Code:

explode

requires a separator - if I assume correctly that your keywords are separated by spaces, the line should be

Code:

$row = explode(&quot; &quot;,$row[column]);

I note that your HTML page doesn't display the result. Maybe you need something like this:

Code:

echo &quot;<TABLE><TR><TH>Keywords</TH></TR>\n&quot;;
while (list(,$keyword)=each($keywords)) echo &quot;<TR><TD>$keyword</TD></TR>\n&quot;;
echo &quot;</TABLE>\n\n&quot;;

Hope this helps
-Rob

Broccoli2 · Oct 3, 2001

Oops, just spotted another typo too -
Should be:

Code:

while (list(,$keyword)=each($row)) $keywords[$keyword]=$keyword;

Let me know if it works
-Rob

frazer · Oct 3, 2001

Thanks Rob,

Works a treat - you're a star!

Thanks for the help,

Frazer

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Listing unique entires from a field

frazer

Technical User

NeffTribe

Programmer

Broccoli2

MIS

frazer

Technical User

Broccoli2

MIS

Broccoli2

MIS

frazer

Technical User

Similar threads

Part and Inventory Search

Sponsor