Ad:

PHP Discussion Thread
Unsolved
Views: 804

Dec 4th, 2008

Remov duplicates from array

Expand Post »

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 Syntax (Toggle Plain Text)
<?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>";
}}
?>
<?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!!

bigbob

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jun 2008

Permalink

Dec 4th, 2008

Re: Remov duplicates from array

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.

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

ShawnCplus

Sponsor

Reputation Points: 520

Solved Threads: 268

Code Monkey

Offline

1,564 posts
since Apr 2005

Permalink

Dec 5th, 2008

Re: Remov duplicates from array

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

PHP Syntax (Toggle Plain Text)
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
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

PHP Syntax (Toggle Plain Text)

DROP TABLE old_table;

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

PHP Syntax (Toggle Plain Text)

RENAME TABLE new_table TO old_table;

Aamit

Reputation Points: 3

Solved Threads: 15

Posting Whiz

Offline

341 posts
since Apr 2008

Permalink

Dec 5th, 2008

Re: Remov duplicates from array

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

bigbob

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jun 2008

Permalink

Dec 5th, 2008

Re: Remov duplicates from array

then you do like this...example

PHP Syntax (Toggle Plain Text)
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;
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 .....

Last edited by Aamit; Dec 5th, 2008 at 9:05 am.

Aamit

Reputation Points: 3

Solved Threads: 15

Posting Whiz

Offline

341 posts
since Apr 2008

Permalink

Dec 5th, 2008

Re: Remov duplicates from array

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....

Aamit

Reputation Points: 3

Solved Threads: 15

Posting Whiz

Offline

341 posts
since Apr 2008

Permalink

Dec 8th, 2008

Re: Remov duplicates from array

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.

PHP Syntax (Toggle Plain Text)

$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.

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Sms facility

Next Thread in PHP Forum Timeline: sql query string logical problem

DaniWeb Message

Cancel Changes

User Name
Password