Page 1 of 1

Show the LAST of a specific field when not last table entry

Posted: Sat Sep 27, 2003 7:59 am
by robster
(not a very descriptive title was that, it was hard to put into a title so here's the history)

Hi all,

The History
I have a table called "archives". In this table are a bunch of entries into a competition. Every month a winner is chosen from that month, and their entry is updated so the "winner" field gets a 1.

So any entry in archives that has 1 in winner has been a winner of a previous months entry.

This is great, and I have a LATEST WINNERS section which php parses thru the database in a loop, and it prints any entries that have 1 in winner... It's a good thing. It works, I like it! :)


The problem:
On my front page (home page) I have a little box that is to show LAST MONTHS WINNER.
Now I went and parsed thru the database once and said print anything with winner = 1. It did this, and showed ALL the winning entries in my little box.. Not good.

So then I said, sort by ID DESC and print if winner = 1. It didn't print anything becuase the LAST entry wasn't the winner. If it had of been, then fine it would have printed, but it isn't, so it doesn't.


I can't figuire out HOW I'm going to tell it to parse thru, print if winner=1 but ONLY if there are no more winners in front (later).

Here's the code I am currently using. Does anyone have any suggestions?


Thanks a heap :)

Rob

Code: Select all

<?
	require("config.php");

	$connection = mysql_connect($dbhost, $dbusername, $dbpassword);

	$content = mysql_db_query($dbname, "SELECT * FROM archives ORDER BY year DESC");
	$Xcontent = mysql_fetch_array($content);

	$ShowMax = mysql_num_rows($content);
//	for ($y=1; $y<=$ShowMax; $y++)
//	{ 
		$id = $Xcontent["id"];
		$year = $Xcontent["year"];
		$month = $Xcontent["month"];
		$number = $Xcontent["number"];
		$title =  $Xcontent["title"];
		$animator =  $Xcontent["animator"];
		$hardware = $Xcontent["hardware"];
		$software = $Xcontent["software"];
		$creationtime = $Xcontent["creationtime"];
		$rendertime =  $Xcontent["rendertime"];
		$viewrecommend =  $Xcontent["viewrecommend"];
		$animdescription = $Xcontent["animdescription"];
		$descriptionofcreation = $Xcontent["descriptionofcreation"];
		$mpg =  $Xcontent["mpg"];
		$jpg =  $Xcontent["jpg"];
		$rating =  $Xcontent["rating"];
		$ratingafter =  $Xcontent["ratingafter"];		
		$winner =  $Xcontent["winner"];
		
		
		//Only display if winner
		if ($winner == "1")
		{
					//print "test<br>";
					print "	<font size="3">";					
					print "<td valign="top" align="left" >";
					print "<a href = "animview_main.php?id=$id"><img src=/files/$year/$month/$jpg></a>";
					print "<br>";
					//print "$title<br>";
					print "</font>";
					print "<b>$animator</b><br>";
					print "Month $month<br>";		
					print "<br>";
					print "<br>";
					print "</td>";
		}

		$Xcontent = mysql_fetch_array($content);
//	}


		
 ?>


You can see I've commented out the loop, that loop was when it displayed EVERY winner=1 entry in the table... I'm at a bit of a loss, some guidance would just be fabulous :)

(Can't wait to show you guys this site after all the help I've had from here!!!).


Rob

Posted: Sat Sep 27, 2003 8:15 am
by Stoneguard
I would change the SQL statment to read:

SELECT * FROM archives where winner=1 ORDER BY year DESC, month DESC

This would still return all the winners, but the very first one would be the latest winner.

Posted: Sat Sep 27, 2003 8:27 am
by robster
That did it! It works! :)

i have one entry in there now, and it is the last winner :)
Thanks SO much, there's so much to learn and no better place to learn it :)


Here's how the code looks now, for anyone else in the future:

Code: Select all

<?php

	require("config.php");

	$connection = mysql_connect($dbhost, $dbusername, $dbpassword);

	$content = mysql_db_query($dbname, "SELECT * FROM archives where winner=1 ORDER BY year DESC,  month DESC ");
	$Xcontent = mysql_fetch_array($content);

	$ShowMax = mysql_num_rows($content);


		$id = $Xcontent["id"];
		$year = $Xcontent["year"];
		$month = $Xcontent["month"];
		$number = $Xcontent["number"];
		$title =  $Xcontent["title"];
		$animator =  $Xcontent["animator"];
		$hardware = $Xcontent["hardware"];
		$software = $Xcontent["software"];
		$creationtime = $Xcontent["creationtime"];
		$rendertime =  $Xcontent["rendertime"];
		$viewrecommend =  $Xcontent["viewrecommend"];
		$animdescription = $Xcontent["animdescription"];
		$descriptionofcreation = $Xcontent["descriptionofcreation"];
		$mpg =  $Xcontent["mpg"];
		$jpg =  $Xcontent["jpg"];
		$rating =  $Xcontent["rating"];
		$ratingafter =  $Xcontent["ratingafter"];		
		$winner =  $Xcontent["winner"];
		
	
					//print "test<br>";
					print "<a href = "animview_main.php?id=$id"><img src=/files/$year/$month/$jpg></a>";
					print "<br>";
					print "$title<br>";		
					print "<b>$animator</b><br>";
					print "<br>";
					print "<br>";
	

		$Xcontent = mysql_fetch_array($content);
		
 ?>

Posted: Sat Sep 27, 2003 1:10 pm
by JAM
You can also use LIMIT in your sql-clause to limit down the hits to 1 (or any). No need to return 50k rows of results (perhaps wont apply to you, yet) when you only want the one.