[Resolved] Trouble with SELECT Query

Mr_Mako

Hi everyone.

I know the answer to this is going to be really simple... but I can't seem to work out how to accomplish what I'm after.

I'm working with this database :

PATROL_ID	PATROL_NO	PATROL_NOTES
--------------------------------------------
1		1		blah
2		1		blah
3		1		blah
4		2		blah
5		2		blah
6		3		blah

The code I've worked up for this is as follows:

$pt = "SELECT patrol_id, patrol_no FROM patrol ORDER BY patrol_no ASC";
$ptresult = @mysql_query($pt, $connect) or die(mysql_error());

$pt_num = mysql_num_rows($ptresult);

if($pt_num == "0") {
	$patrol_links = "";
} else {
	while ($row = mysql_fetch_array($ptresult)) {
		$ptid = $row['patrol_no'];

	$patrol_links .= "
		&nbsp;&nbsp;-&nbsp;<a href=index.php?content=patrol&patrolid=$ptid>Patrol $ptid</a><br>";
}
}

What I want to do is list the results by Patrol Number... but only as a single link.

For example... in the above database I have 3 patrols. I want the links provided by the query to return :

Patrol 1
Patrol 2
Patrol 3

With the code as it is now, I get this :

Patrol 1
Patrol 1
Patrol 1
Patrol 2
Patrol 2
Patrol 3

Can anyone shed some light on how I can get the results I'm looking for? Again... I'm sure the solution is probably stupidly simple... but I just can't seem to work it out.

Thanks...

AstroTeg

You need to do some grouping in your sql:

SELECT patrol_no FROM patrol GROUP BY patrol_no ORDER BY patrol_no ASC

Or in this case, you could use DISTINCT

SELECT DISTINCT patrol_no FROM patrol ORDER BY patrol_no ASC

I'm not sure which one will be faster/easier on the database engine.

Mr_Mako

Doh.

I never realized I could use GROUP and ORDER in the same SELECT statement.

Told ya it was easy... thank you for the suggestion, that worked fine.