distinct help

[mikew101]

Hello,

 

I extracted some data from the web, and then imported the data into mysql.

 

Currently I have several rows that have the same data within fields. The id fields are unique, and the url fields are unique, but the rest of the fields on the table will be duplicated within many rows.

 

I created a query that looks like this: SELECT DISTINCT description FROM table WHERE description = '$somephpvariable'

 

This actually does work in eliminating the rows that have the same descriptions, but the other rows when displayed after gathering the results of the mysql query however will no longer display. So when I place the other fields in php variables the variables will be blank or incorrect.

 

Any help would be great. I cant seem to find anyone on the web with this problem.

[bubblegum.anarchy]

How are you determining which of the records other columns information should be displayed with the unique description?

[mikew101]

I have four fields that I am displaying. So when I do a select query I am selecting fields based on their relevance to a search peformed on the data within the fields. I am displaying an image which is a field with the image url. A name field which is the name of the product, and becomes a link for the actual page url which is in a seperate field. Then I am displaying the description, and price. These fields are all contained in a row along with the id field.

 

So the fields look like: id, name, desci, price, imageurl, url

 

Each row has those fields. In every row no matter what the url and id field will always be unique. However the name, desci, and price fields sometimes will have multiple instances. So I may have 10 rows with the same name desci and price, but each has a different id, and different url. Even the url is very similar, but still different.

 

Using the distinct keyword I have been able to eliminate the rows with the same data, but when I select and fill php variables with the row data from the select query results the variables will not hold the correct information.

 

I display all other fields besides the id.

[Illusion]

when I select and fill php variables with the row data from the select query results the variables will not hold the correct information.

It's depends on how you written the query. I have not seen any Q? in your description. So tell exactly what results you are expecting from the query so that we can help you.

[mikew101]

Sorry for the confusion.

 

My original select statement looks like this:

 

$query = "SELECT * FROM table WHERE name LIKE '%$search%' OR desci LIKE '%$search%' Or price LIKE '%$search%'";

 

The above query displays all of the duplicate rows. Like I said before the id and url fields are always different.

 

My new one that eliminates duplicates is like this:

 

$query = "SELECT DISTINCT desci FROM shoes WHERE name LIKE '%$search%' OR desci LIKE '%$search%' Or price LIKE '%$search%'";

 

The above will eliminate the duplicates, but when I use code like this:

 

$name = mysql_result($result,$i,"name");

 

The name variable in php will not store the name that is in the database, and the same goes for all variables other then the desciprtion. If I add the variables into the query like this:

 

$query = "SELECT DISTINCT desci,name,price,imageurl FROM shoes WHERE name LIKE '%$search%' OR desci LIKE '%$search%' Or price LIKE '%$search%'";

 

This fixes the display problems. However because the url field is always unique the url output will not output correctly. If I add the url field above then everything will come up as the url is distinct in every case.

 

I hope this clears things up thanks very in advance for any help.

[fenway]

You mean group by, not distinct.

[Illusion]

What I understood from your description is you are expecting  multiple URLs  to be resturned with DISTINCT desc , How can it possible if multiple URLs have the same description. I didn't understand what is your problem in displaying same description(duplicates ) as you have unique id and url fields.