Page 1 of 1

pull only unique records from DB

Posted: Mon Aug 22, 2011 12:24 pm
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!

Re: pull only unique records from DB

Posted: Mon Aug 22, 2011 1:06 pm
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

Re: pull only unique records from DB

Posted: Mon Aug 22, 2011 3:12 pm
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

Re: pull only unique records from DB

Posted: Mon Aug 22, 2011 3:28 pm
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.

Re: pull only unique records from DB

Posted: Mon Aug 22, 2011 4:40 pm
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 ....

Re: pull only unique records from DB

Posted: Mon Aug 22, 2011 6:51 pm
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.

Re: pull only unique records from DB

Posted: Mon Aug 22, 2011 10:15 pm
by inosent1
got it, thanks! ... distinct only works with one column -- that is the issue

Re: pull only unique records from DB

Posted: Tue Aug 23, 2011 3:37 am
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

Re: pull only unique records from DB

Posted: Fri Aug 26, 2011 4:02 am
by poster123
hi

thanks for the reply

this code