pull only unique records from DB

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

pull only unique records from DB

Post by inosent1 »

if i use:

Code: Select all


<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM data1_c";
$result=mysql_query ($query);
$num=mysql_num_rows ($result);
mysql_close();


?>

then my web page shows all the records from the db

but there is a field, files_id, that has many duplicate entries

what i want to do is eliminate all records from the query where the files_id duplicates

i tried this but it didnt work:

Code: Select all

<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT distinct file_id * FROM data1_c";
$result=mysql_query ($query);
$num=mysql_num_rows ($result);
mysql_close();

?>


any ideas are greatly appreciated!
phphelpme
Forum Contributor
Posts: 261
Joined: Sun Nov 21, 2010 3:32 pm

Re: pull only unique records from DB

Post by phphelpme »

As far as I am aware the correct format would be:

Code: Select all

$query="SELECT DISTINCT file_id FROM data1_c";
This should select none duplicated records from your table data1_c using your column file_id.

Best wishes
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: pull only unique records from DB

Post by inosent1 »

i am getting this error

Code: Select all

Warning: mysql_result() [function.mysql-result]: last_name not found in MySQL result index 3
(which is to say the records are not displayed on the web page)

if i take the 'DISTINCT file_id' out, then every record appears.

maybe i am not asking the question right

my db looks like this for example

name color file_id

smith blue 7
smith red 7
smith red 7
jones blue 8
jones red 8
west green 9
west brown 9

what i want is to pull only 1 smith, 1 jones, 1 west record, instead of all of them

so the result will look like this (my page will print only the name)

smith
jones
west

thanks for the reply
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: pull only unique records from DB

Post by califdon »

The "error" you showed (which is not an error, it's a warning) is telling you that in the function mysql_result() you are asking for a field named "last_name" but there is no such field in the $result array that you created with your query. You need to put a comma after the DISTINCT file_id and before the asterisk (which means "all the fields"), although it would be a better practice to specify all the fields that you need, separated by commas.

Speaking of better practice, is there a good reason why you have 2 fields in your table that appear to refer to the same thing, 'name' and 'file_id'? If the file_id will always match the name, you shouldn't have 2 fields, that's redundant, one of the worst things you can do in a relational database.
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: pull only unique records from DB

Post by inosent1 »

hi

thanks for the reply

this code

Code: Select all


$query="SELECT DISTINCT file_id , * , FROM data1_c";

// or ...

$query="SELECT DISTINCT file_id , *  FROM data1_c";

// or ...

$query="SELECT DISTINCT file_id * FROM data1_c";
results in this warning:

Code: Select all

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean

i am not sure what you mean by referring to the same thing, but the db has 100 records, notations

but each notation refers to a specific client (file_id is the identifier)

20 clients, 100 notes, 5 notes per client

what i want is to pull just the names of the clients, w/o duplicates. i am assuming if i say 'pull all the records, but make sure not to show records where file_id duplicates' would work that there is some code snippet that will do that. i just cant figure it out

i dont want to display records where the file_id is the same as another display, just unique file_id and the associated column content from the requested row

i hope that was a little clearer ....
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: pull only unique records from DB

Post by califdon »

inosent1 wrote:

Code: Select all

$query="SELECT DISTINCT file_id , * , FROM data1_c";

// or ...

$query="SELECT DISTINCT file_id , *  FROM data1_c";

// or ...

$query="SELECT DISTINCT file_id * FROM data1_c";
results in this warning:

Code: Select all

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean
Well the 1st and 3rd queries above are certainly invalid. Commas are used between fieldnames in a field list, never after the last one and they are mandatory.

That warning isn't generated by that line, what it is saying is that the mysql_num_rows() functions 2 lines below that can't return the number of rows because the mysql_query() function didn't generate a valid "resource" (which means that either the connection to the database was unsuccessful or the query was invalid). That's certainly true for the 1st and 3rd queries you show because they are, themselves, invalid. I wasn't sure if the comma followed by an asterisk was valid and I didn't take the time to check the manual (always the best thing to do), but you should do what I said earlier, list the fieldnames you need rather than use the asterisk. So if the fieldname is 'last_name', your query needs to be:

Code: Select all

$query="SELECT DISTINCT file_id, last_name  FROM data1_c";
inosent1 wrote: i am not sure what you mean by referring to the same thing, but the db has 100 records, notations

but each notation refers to a specific client (file_id is the identifier)

20 clients, 100 notes, 5 notes per client

what i want is to pull just the names of the clients, w/o duplicates. i am assuming if i say 'pull all the records, but make sure not to show records where file_id duplicates' would work that there is some code snippet that will do that. i just cant figure it out

i dont want to display records where the file_id is the same as another display, just unique file_id and the associated column content from the requested row

i hope that was a little clearer ....
I am referring to how relational databases work. They are not like spreadsheets where you may repeat data if it should be displayed more than once. In your example, every line where the name was 'Smith', the file_id was '7', and the same for the other names. It looked/looks like both 'name' and 'file_id' refer to the same thing. In a relational database that's called a one-to-one relationship--if the file_id is '7', then the name is 'Smith'. That calls for 2 tables, one for peoples' names and the other that relates a person's name with their file_ids. However, this has nothing to do with the problem you are having with the query. I don't want to confuse the 2 issues for you. This is just normal database theory. Indeed, I don't know understand what the colors are related to, so I can't begin to explain how your database should be designed. From your examples the colors appear not to be related to either the name or the file_id. If this is an actual application, rather than a programming exercise, you should not proceed further until you get your database schema straightened out. If it's just a learning exercise, you should take careful note that it's not at all a practical one, or certainly doesn't seem to be, and if you want to gain any practical knowledge from it, you need to study a little bit about relational databases. There's a ton of material on the web about relational databases.
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: pull only unique records from DB

Post by inosent1 »

got it, thanks! ... distinct only works with one column -- that is the issue
phphelpme
Forum Contributor
Posts: 261
Joined: Sun Nov 21, 2010 3:32 pm

Re: pull only unique records from DB

Post by phphelpme »

Yes, thats right because it is known that if you are using more than one column in your query it might cause issues with the other columns that you search. There is another way and that would be to count() the number of repetitive values and them exclude them from the list.

Just a thought thats all.

Best wishes
poster123
Forum Newbie
Posts: 3
Joined: Fri Aug 26, 2011 3:02 am

Re: pull only unique records from DB

Post by poster123 »

hi

thanks for the reply

this code
Post Reply