[Resolved] using DISTINCT....

Iquil

I was wondering if there was a way of get a distinct result when getting information out of a database for one field and not all fields?

this is what I am using at the moment:

<?php

include "connect.php";

$query = "SELECT DISTINCT type_id, type
		  FROM $dbtable[trailer]
		  ORDER BY type_id";

$result = mysql_query($query)
	or die(mysql_errno().": ".mysql_error());

$content = "<br><b><u><center>SALES</center></u></b><br>";
while($row=mysql_fetch_array($result)) {
	extract($row);
	$content .= "<a href=\"browse2.php?type_id=$row[type_id]\">$row[type]</a><br/>";
}

?>

the field I need to be distinct is the type_id.

Any help would be greatly appreciated.

RossC0

Doesn't this get the Distinct type_ids:

SELECT DISTINCT type_id
FROM $dbtable[trailer]
ORDER BY type_id;

Lars_Berg

I'm assuming you have data like

type_id type

1 B
1 A

What value from the type column do you want?

if the above is not applicable, please supply some sample data and the expected result.

Iquil

This would be the info in the data base:

[CODE] Type ID | Type

1 | Aa
1 | aa
2 | Bb
2 | bb
3 | Cc
3 | cc[/CODE]

And this would be the output that I would like to have:

[CODE] Type ID | Type

1 | Aa
2 | Bb
3 | Cc[/CODE]

laserlight

What if you GROUP BY type_id?

Iquil

would that take out the DISTINCT part altogether??

laserlight

Yes.

RossC0

How efficient this is I have no idea - but if you have to ensure each instance where the first letter of type is Capitalised then this will do that:

SELECT DISTINCT type_id, type
FROM $dbtable[trailer]
WHERE type_id REGEXP BINARY '[A-Z]'
ORDER BY type_id;

laserlight

SELECT type_id, type FROM tablename GROUP BY type_id;

Iquil

Thank you for your help. laserlight and RossC0. It is working now.