eliminate duplicates on query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

eliminate duplicates on query

Post by inosent1 »

i have used a query like this and it will show me a list of records

Code: Select all

$sql = mysql_query("select * from m_data where file_id = '$file_id' ORDER BY id DESC");
but now i want to run a query from a table that looks like this

file_id name item#
1 smith 1
1 smith 2
1 smith 3
1 smith 4

i want my result to be

1 smith

all i need is for the query to pull the first instance and show it on the list that follows.

how can i change the query to eliminate showing a duplicate entry?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: eliminate duplicates on query

Post by requinix »

Throw in a DISTINCT.

Code: Select all

SELECT DISTINCT file_id, name FROM table
But I have a question: why is there so much duplicate data?
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: eliminate duplicates on query

Post by inosent1 »

thanks for the reply.

it is a table of assigned tasks related to on particular property. i want the user on one screen to see a list of props they need to work on and in a different area review tasks
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: eliminate duplicates on query

Post by califdon »

Without seeing your database schema (tables and columns), it is hard to give you any suggestions, but the reason requinix raised the question is that what you showed strongly hints that your database is unnormalized, which means that it is very inefficient. Just because you want to display data in some particular manner is NOT a reason to STORE the data that way. That's what you do in spreadsheets, but NOT in databases! This is a critical factor in being able to retrieve data with queries.
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: eliminate duplicates on query

Post by inosent1 »

this works:

Code: Select all

$query="SELECT * FROM assignments WHERE uid ='$row5[0]' AND dt_complete ='$btn' ORDER BY id DESC";

this doesnt:

Code: Select all

$query="SELECT DISTINCT file_id FROM assignments WHERE uid ='$row5[0]' AND dt_complete ='$btn' ORDER BY id DESC";

with the first one all the data related to the search shows up. but when i use the distinct i get

Code: Select all

Warning: mysql_result() [function.mysql-result]: b_fname not found in MySQL result index 9 in /home/content/20/8370920/html/f/acq_pipe.php on line 908

x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: eliminate duplicates on query

Post by x_mutatis_mutandis_x »

Code: Select all

$query="SELECT DISTINCT file_id, b_fname /*,column_2, column_3 etc.*/ FROM assignments WHERE uid ='$row5[0]' AND dt_complete ='$btn' ORDER BY id DESC"; 
Post Reply