Howto limit same results to one?

marxx · 03-08-2008, 12:47 PM

Hi all!

How do I limit same results to as one?!

example: if my query fetch like this:

Code:

Banana
Banana
Banana
Apple
Tomato
Kiwi
Kiwi
Kiwi
Kiwi
Kiwi
Kiwi
Onion
Onion

I need to limit those results as below:

Code:

Banana
Apple
Tomato
Kiwi
Onion

Like that.. To limit multiple results as one! =)

Thanks for all help!

Alan @ CIT · 03-08-2008, 01:18 PM

Are you fetching the list from a database? If so, there are a couple of options you can use.

The first is to use the DISTINCT keyword in your query:

SQL Code:

SELECT DISTINCT fruit FROM products

The second is to use the GROUP BY clause in your query:

SQL Code:

SELECT fruit FROM products GROUP BY fruit

Both will give you the desired result

Alan

marxx · 03-08-2008, 01:55 PM

Hi Alan and thanks 4 replying.
Yes I'm fetching these from database (sorry for not metion that ;))

Amm.. What if I don't know which results will be multiple at the time I'm coding this?

Or then I didn't quite understand your examples?
Well let's bake one example this way.

I have news-script where is several posts for each month.
I want to create archive links for each those months, automaticly ofcourse..

I would do it something like this:

PHP Code:


			
$q = mysql_query("SELECT added FROM news ORDER BY added DESC") or die(mysql_error());

while($q1 = mysql_fetch_array($q)) {



$timestamp = $q1['added'];

$datetime = date("F Y" , strtotime("$timestamp"));

$year = date("Y", strtotime("$timestamp"));

$month = date("m", strtotime("$timestamp"));



print("<li><a href=\"/news/".$year."/".$month."/\">".$datetime."</a></li>\n");

}

Okey, works but if I have 7 news for march then I have 7 March 2007 links over there.. Now, those I must put as one.

Maybe your example Alan works but I have now clue how I should do it?

Thanks for all help! =)

Alan @ CIT · 03-08-2008, 02:12 PM

In your example above I would move the unique check to the PHP code.

For example, instead of print()'ing your date links, I would add them to an array and then use array_unique() on it to strip out duplicates.

Something like the following (untested) should work:

PHP Code:


			
$q = mysql_query("SELECT added FROM news ORDER BY added DESC") or die(mysql_error()); 

while($q1 = mysql_fetch_array($q)) { 



$timestamp = $q1['added']; 

$datetime = date("F Y" , strtotime("$timestamp")); 

$year = date("Y", strtotime("$timestamp")); 

$month = date("m", strtotime("$timestamp")); 



$dates[] = "<li><a href=\"/news/".$year."/".$month."/\">".$datetime."</a></li>\n";

}



$dates = array_unique($dates);

foreach ($dates as $datelink)

{

echo $datelink;

}

Alan

marxx · 03-08-2008, 02:17 PM

Whau.. You are gooood Alan!! =)

Thank You!

Alan @ CIT · 03-08-2008, 02:20 PM

Glad I could help

Alan

DeMo · 03-08-2008, 06:50 PM

If the type of the added column is DATE or DATETIME you can do it with a simple query:

SQL Code:

SELECT DISTINCT MONTHNAME(`added`) AS `month`, YEAR(`added`) AS `year` FROM `news` ORDER BY `year` DESC, `month` DESC

This will return a table like this:

Code:

+--------+  +------+
| month  |  | year |
+--------+  +------+
|March   |  | 2008 |
|February|  | 2008 |
|January |  | 2008 |
|December|  | 2007 |
+--------+  +------+

Now to display the list you simply do:

PHP Code:


			
while ($q1 = mysql_fetch_array($q)) {

  echo $q1['month'] . "/" . $q1['year'];

}

You can also increment that query to ignore the current month/year and to limit the number of results (let's say you want only 5 links):

SQL Code:

SELECT DISTINCT MONTHNAME(`added`) AS `month`, YEAR(`added`) AS `year` FROM `news` WHERE MONTH(`added`) != MONTH(NOW()) AND YEAR(`added`) != YEAR(NOW()) ORDER BY `year` DESC, `month` DESC LIMIT 5