Use of Distinct ?

[ben]

This is a PHP / MySQL kind of question. I am making a script which
simply pulls information from a database and displays it on screen. BUT
there will be entries where fields could be the same, and I want to
eliminate the duplicate entries when it displays it on screen.

I would use,

SELECT DISTINCT 'address1' FROM orders

....which does work, but only gives me one field. I also need to get a
few other fields such as name and address1. I cant think of a way to
eliminate the duplicates and still show all fields.

Does anyone know a way round this ?

ben

[LEH]

"ben" <le*****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

This is a PHP / MySQL kind of question. I am making a script which
simply pulls information from a database and displays it on screen. BUT
there will be entries where fields could be the same, and I want to
eliminate the duplicate entries when it displays it on screen.

I would use,

SELECT DISTINCT 'address1' FROM orders

...which does work, but only gives me one field. I also need to get a
few other fields such as name and address1. I cant think of a way to
eliminate the duplicates and still show all fields.

Does anyone know a way round this ?

ben

First a disclaimer : I'm selftaught and there maybe better ways, but this is
the way I have done this.

You can run a series of SQL statements, first selecting the distinct field,
then selecting other records/fields with this distinct field.

But I am assuming that several fields are distinct, or at least the
combination of those fields is distinct. If this is the case, I generally
use the concat function to pull distinct combinations. I will usually also
put in a separator so that i can split afterwards

SELECT DISTINCT CONCAT(address1,"/",name,"/",phone) FROM orders;

then i can splilt the result on / into the appropriate variables.

perhaps at a SQL group you can get a more eloquent answer.

Larry

[Shamrock]

On 2005-02-27, LEH <l.*******@utoronto.ca> wrote:

"ben" <le*****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

This is a PHP / MySQL kind of question. I am making a script which
simply pulls information from a database and displays it on screen. BUT
there will be entries where fields could be the same, and I want to
eliminate the duplicate entries when it displays it on screen.

I would use,

SELECT DISTINCT 'address1' FROM orders

...which does work, but only gives me one field. I also need to get a
few other fields such as name and address1. I cant think of a way to
eliminate the duplicates and still show all fields.

SELECT DISTINCT('address'),other,thing FROM orders
should work

Shamrock

[ben]

dammit ! lol, that last post did the trick

SELECT DISTINCT('address'),other,thing FROM orders

I ended up going with,

$result = mysql_query("SELECT DISTINCT address1 FROM orders WHERE
approved = '2'");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
if(!isset($total)) { $total = ''; }
$i = "0";
$address = $row["address1"];

$result2 = mysql_query("SELECT * FROM orders WHERE address1 =
'$address'");
while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
$address1 = $row["address1"];
$id = $row2["ID"];
$ordertime = $row2["ordertime"];
$title = $row2["title"];
$name = $row2["name"];
$price = $row2["price"];
$total = $total+$price;
$i++;
}

which now seems overly complex and time consuming ! i think i might
just change it.