Remov duplicates from array

[bigbob]

I hope someone can help I've been trying to sort this for hours.
I am trying to pull info from my database and display everything which I have done successfully, however I have duplicate records in my database due to records in different subcategories.
Anyway I want to display everything but only once and not display the duplicates.
At pres my code is this:

<?php if(isset($_GET['alph'])){
$alph = $_GET['alph'];
$sql = "SELECT * FROM paid WHERE title LIKE '$alph%' ORDER by asc";
$query = mysql_query($sql);
while($data = mysql_fetch_array($query)){
$desc = substr($data['description'],0,100);
echo "<tr><td valign=\"top\">".$data['title']." ".$data['description']."</tr></td>";
}}
?>

Now I have tryed DISTINCT in the sql statement and that didn't work and I have tryed using array_unique() eveywhere but to be honest I'm not exactly sure how to use array_unique() with mysql_fetch_array.
If someone could point me in the right direction it will save this hole in my wall getting any bigger as I feel I can do nothing but bash my head against it.

PLEASE HELP!!

[ShawnCplus]

Well you can't really do array_unique on mysql_fetch_array since it actually acts as an iterator for the MYSQL Resource that is returned by mysql query.
The correct way to fix it would be to fix your database to have unique keys so you don't have duplicates.
The hacky way to fix it would be to choose a field like title and add the title to another array as you iterate through the list. and don't output if it already exists in the array.

$entries = array();
while($data = mysql_fetch_array($query)) {
  $entries[]=$data['title'];
  if(!in_array($data['title'], $entries)) {
    // Your code here
  }
}

[Aamit]

There is way to remove duplicate data or row like...
Step 1: Move the non duplicates (unique tuples) into a temporary table

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Step 2: delete delete the old table

DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;

[bigbob]

Hi thanks for the reply's. Aamit I dont want to delete the duplicates I just don't want to display them when I echo out the results form the table I just want to display the results once.

Thanks ShawnCplus, but when I tryed your surgestgion I didn't get anything to display.

So still trying. Thanks though guys

[Aamit]

then you do like this...example

CREATE TABLE table2 (
id INT NOT NULL UNIQUE AUTO_INCREMENT,
name VARCHAR(20) NOT NULL 
);

INSERT INTO table2(id,name) VALUES
(1,'Things Fall Apart'),
(2,'Things Fall Apart'),
(3,'The Famished Road'),
(4,'Things Fall Apart'),
(5,'The Famished Road'),
(6,'Thirteen cents'),
(7,'Thirteen cents');

CREATE  TABLE 
temp2(id VARCHAR(10), name VARCHAR(20))
TYPE=HEAP;

INSERT INTO temp2(name) SELECT DISTINCT name FROM table2;

DELETE FROM table2;

INSERT INTO table2(id,name) SELECT id,name FROM temp2;

you can place your data in temp table..and display main table without duplication of data by using DISTINCT .....

[Aamit]

you can do like this also....
your main table is paid table.....

insert all data of paid table in table2....
and again insert data in temp table and by using distinct...

you got your output without duplication...in table2....
display table2....

and your paid table remain as original....

[digital-ether]

You don't need to create temp tables to hold the distinct results.

You can just add DISTINCT to your query, for the field that you want to be distinct.

$sql = "SELECT DISTINCT(field), * FROM paid WHERE title LIKE '$alph%' ORDER by asc";

where field is the one you want distinct.

The GROUP BY clause will also serve the same purpose.