Jump to content

MySql Select to get on records with largest weight (MAX(c_weight)???)

darylfromjax

By darylfromjax
in MySQL Help

 Share

Recommended Posts

darylfromjax Newbie

darylfromjax

Posted
Posted

(mysql Ver 12.22 Distrib 4.0.16

PHP 4.4.1

OS: FreeBSD 4.11-STABLE )

 

I am working on a fishing tournament site.  I need to get the the largest catch within a species (such as Dolphin) for each angler.  Each angler can catch multiple Dolphin but only the one that weights the most will be listed.  I must get the fish that weights the most for each angler and then sort that list from anglers with the heaviest fish down to the lightest fish.

 

Sample not working can be seen at http://offshorerodeo.com/fishstandings.htm?fish=Grouper .

 

I have tried the following and then put thru a simple bubble sort.  SELECT statement in RED.

 

[color=green][b]***** Code starts here *********[/b]0
// connect to the database
include "dbconnect.php"; 
mysql_select_db("reg");
mysql_select_db("catch");

//-------------------------------------------------------------------------
$mysql = "[b][color=red]SELECT c_species, c_weight, MAX(c_weight) AS max_weight, r_nick_name, c_date_caught, c_date_uploaded, c_time_uploaded, c_boatname, c_captcatch, c_approved_y_n, r_jr_sr, c_pic FROM `reg` JOIN `catch` ON r_num=c_angler_num WHERE c_species='$fish'  GROUP BY c_angler_num HAVING COUNT(*) >=1[/color][/b] ";


$dbresult = mysql_query($mysql);
	$numrows = mysql_num_rows($dbresult);
$cnt=0;
    while ($list = mysql_fetch_array($dbresult)) { 
      $r_nick_name[$cnt] 		= $list['r_nick_name'];
  $c_species[$cnt] 			= $list['c_species'];
  $c_date_caught[$cnt] 		= $list['c_date_caught'];
  $c_date_uploaded[$cnt] 	= $list['c_date_uploaded'];
  $c_time_uploaded[$cnt] 	= $list['c_time_uploaded'];
  $c_boatname[$cnt] 		= $list['c_boatname'];
  $c_captcatch[$cnt] 		= $list['c_captcatch'];
  $max_weight[$cnt] 		= $list['max_weight'];
  $c_approved_y_n[$cnt] 	= $list['c_approved_y_n'];
  $c_jr_sr[$cnt]			= $list['c_jr_sr'];
  $c_pic[$cnt]				= $list['c_pic'];
  $cnt = $cnt +1;
  }


for ($i = 0; $i < $cnt; $i++) {
	for ($j = $i +1; $j <= $cnt; $j++) {
		if ($max_weight[$i] < $max_weight[$j]) {
	      	$hold_weight			= $max_weight[$i];
			$hold_nick_name			= $r_nick_name[$i];
			$hold_species			= $c_species[$i];
  			$hold_date_caught		= $c_date_caught[$i];
  			$hold_date_uploaded		= $c_date_uploaded[$i];
			$hold_time_uploaded		= $c_time_uploaded[$i];
			$hold_boatname			= $c_boatname[$i];
			$hold_captcatch			= $c_captcatch[$i];
			$hold_approved_y_n		= $c_approved_y_n[$i];
			$hold_jr_sr				= $c_jr_sr[$i];
			$hold_pic				= $c_pic[$i];
			//
	      	$max_weight[$i]			= $max_weight[$j];
			$r_nick_name[$i]		= $r_nick_name[$j];
			$c_species[$i]			= $c_species[$j];
  			$c_date_caught[$i]		= $c_date_caught[$j];
  			$c_date_uploaded[$i]	= $c_date_uploaded[$j];
			$c_time_uploaded[$i]	= $c_time_uploaded[$j];
			$c_boatname[$i]			= $c_boatname[$j];
			$c_captcatch[$i]		= $c_captcatch[$j];
			$c_approved_y_n[$i]		= $c_approved_y_n[$j];
			$c_jr_sr[$i]			= $c_jr_sr[$j];
			$c_pic[$i]				= $c_pic[$j];
			//
	      	$max_weight[$j]			= $hold_weight;
			$r_nick_name[$j]		= $hold_nick_name;
			$c_species[$j]			= $hold_species;
  			$c_date_caught[$j]		= $hold_date_caught;
  			$c_date_uploaded[$j]	= $hold_date_uploaded;
			$c_time_uploaded[$j]	= $hold_time_uploaded;
			$c_boatname[$j]			= $hold_boatname;
			$c_captcatch[$j]		= $hold_captcatch;
			$c_approved_y_n[$j]		= $hold_approved_y_n;
			$c_jr_sr[$j]			= $hold_jr_sr;
			$c_pic[$j]				= $hold_pic;
			}
		if ($max_weight[$i] == $max_weight[$j]) {  // Must check for date / time posted to break same weight tie
			$i_datetime = $c_date_uploaded[$i].$c_time_uploaded[$i];
			$j_datetime = $c_date_uploaded[$j].$c_time_uploaded[$j];
			if ($i_datetime < $j_date_time) {
	      		$hold_weight			= $max_weight[$i];
				$hold_nick_name			= $r_nick_name[$i];
				$hold_species			= $c_species[$i];
  				$hold_date_caught		= $c_date_caught[$i];
  				$hold_date_uploaded		= $c_date_uploaded[$i];
				$hold_time_uploaded		= $c_time_uploaded[$i];
				$hold_boatname			= $c_boatname[$i];
				$hold_captcatch			= $c_captcatch[$i];
				$hold_approved_y_n		= $c_approved_y_n[$i];
				$hold_jr_sr				= $c_jr_sr[$i];
				$hold_pic				= $c_pic[$i];
				//
	      		$max_weight[$i]			= $max_weight[$j];
				$r_nick_name[$i]		= $r_nick_name[$j];
				$c_species[$i]			= $c_species[$j];
  				$c_date_caught[$i]		= $c_date_caught[$j];
  				$c_date_uploaded[$i]	= $c_date_uploaded[$j];
				$c_time_uploaded[$i]	= $c_time_uploaded[$j];
				$c_boatname[$i]			= $c_boatname[$j];
				$c_captcatch[$i]		= $c_captcatch[$j];
				$c_approved_y_n[$i]		= $c_approved_y_n[$j];
				$c_jr_sr[$i]			= $c_jr_sr[$j];
				$c_pic[$i]				= $c_pic[$j];
				//
	      		$max_weight[$j]			= $hold_weight;
				$r_nick_name[$j]		= $hold_nick_name;
				$c_species[$j]			= $hold_species;
  				$c_date_caught[$j]		= $hold_date_caught;
  				$c_date_uploaded[$j]	= $hold_date_uploaded;
				$c_time_uploaded[$j]	= $hold_time_uploaded;
				$c_boatname				= $hold_boatname;
				$c_captcatch			= $hold_captcatch;
				$c_approved_y_n[$j]		= $hold_approved_y_n;
				$c_jr_sr[$j]			= $hold_jr_sr;
				$c_pic[$j]				= $hold_pic;
				}
			}
		}

	} 				
?>			  
   	<table width="100%" border="0" cellspacing="0">
   		<tr>
      		<th scope="col" width="150" bgcolor="#66CCFF" style="text-align:center">Date Caught </th>
       		<th scope="col" width="150" bgcolor="#66CCFF" style="text-align:center">Date Posted </th>
		<th scope="col" width="150" bgcolor="#66CCFF" style="text-align:center">Angler</th>
       		<th scope="col" width="150" bgcolor="#66CCFF" style="text-align:center">Boat Name </th>
       		<th scope="col" width="150" bgcolor="#66CCFF" style="text-align:center">Photo</th>
		<th scope="col" width="150" bgcolor="#66CCFF" style="text-align:center">Verified</th>
   		</tr>
<?php     
    for ($i = 0; $i <= $cnt; $i++) {
	?>
      		<tr>
        		<td width="150"><?php echo $c_date_caught[$i] ?></td>
        		<td width="150"><?php echo $c_date_uploaded[$i]."  ".$c_time_uploaded[$i] ?></td>
			<td width="150"><?php echo $r_nick_name[$i] ?>
			<?php
			if ($r_jr_sr[$i] == 'J') { 
				echo "<BR>Junior Angler";
				} 
			?>
			</td>
        		<td width="200">
				<?php
				$c_captcatch[$i] = strtoupper($c_captcatch[$i]);
				if ($c_captcatch[$i] == 'Y') { 
					echo "$c_boatname[$i]<BR>(Captains Catch)";
					} else {
						echo $c_boatname[$i];
						}
				?>
			</td>
        		<td width="150">
				<?php
					echo "<a href='fishpics/".$c_pic[$i]."' target='_blank'>";
					if ($c_species[$i] == 'Cobia') {
						echo "<img src='images/fish/minifish/cobia_mini.jpg'></a>";
						} else 
					if ($c_species[$i] == 'Dolphin') {
						echo "<img src='images/fish/minifish/dolphin_mini.jpg'></a>";
						} else 
					if ($c_species[$i] == 'Grouper') {
						echo "<img src='images/fish/minifish/grouper_mini.jpg'></a>";
						} else 
					if ($c_species[$i] == 'Kingfish') {
						echo "<img src='images/fish/minifish/kingfish_mini.jpg'></a>";
						} else 
					if ($c_species[$i] == 'Snapper') {
						echo "<img src='images/fish/minifish/snapper_mini.jpg'></a>";
						} else 
					if ($c_species[$i] == 'Tuna') {
						echo "<img src='images/fish/minifish/tuna_mini.jpg'></a>";
						} else 
					if ($c_species[$i] == 'Wahoo') {
						echo "<img src='images/fish/minifish/wahoo_mini.jpg'></a>";
						} 							
				?>
			</td>
        		<td width="150">
				<?php
					if (strtoupper($c_approved_y_n[$i]) == 'Y'){ echo "Verified";}
					else {echo "  ";} 
				?>
			</td>

      		</tr>
<?php 
	}
?>
    	</table>
[b]***** Code ends here *********[/b][/color]

***** MySql Table catch starts here *********

Field Type

c_numint(11)    

c_angler_numint(11)  

c_date_uploadeddate  

c_time_uploadedtime 

c_speciesvarchar(15)  

c_weightint(11) 

c_boatnamevarchar(25) 

c_boat_numint(3) 

c_captcatchset('y', 'n')  

c_pointsint(4) 

c_pictext  

c_date_caughtdate  

c_approved_y_nchar(2)  

c_admin_approvervarchar(50)  

c_admin_date_approved varchar(19)    

c_approver_extra_text2anglervarchar(200)  

c_approver_interal_notesvarchar(200)  

 

***** MySql Table catch ends here *********

 

***** MySql Table reg starts here *********

Field Type 

r_numint(11)   

r_last_namevarchar(25)   

r_first_namevarchar(25)   

r_nick_namevarchar(25)    

r_jr_srchar(1) 

r_streetvarchar(30)  

****etc********etc****  

****etc********etc****  

 

***** MySql reg catch ends here *********

 

Thanks in advance,

Daryl

 

 

 

 

[attachment deleted by admin]

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

You won't get sensible results if you mix c_weight and MAX(c_weight) in one query.  Mysql will give you a TOTALLY RANDOM value for c_weight, chosen from the possible rows.  It's best if you do one query to find maximum weights, and another query to find individual weights.  The individual weight query should not use GROUP BY.

 

As for your data structures, you may find it easier to use this:

 

$catch_data = array();
while ($list = mysql_fetch_array($dbresult)) {
     $catch_data[$cnt] = $list;
     $cnt = $cnt +1;
}

print "r_nick_name[1] = " . $catch_data[1]['r_nick_name'];

 

It'll save a lot of typing :)  Of course your sorting will have to be done differently.  Feel free to ask about that.

Link to comment
Share on other sites
darylfromjax Newbie

darylfromjax

Posted
  • Author
Posted

I will try this.

 

I also wanted to give some sample data and output:

 

 

Input is like this:

Catch Table

(Catch number) (Catch Angler Number) (catch Weight) (catch picture)  (catch type of fish)

20 300 8 fish20d20070324.jpg Grouper

21 300 10 fish21d20070324.jpg Grouper

22 300 1 fish22d20070324.jpg Snapper

26 200 25 fish26d20070324.jpg Dolphin

33 100 5 fish33d20070324.jpg Grouper

34 150 66 fish34d20070324.jpg Grouper

35 150 65 fish35d20070324.jpg Grouper

 

Reg (Registration) Table:

(Angler number) (Name) (Nickname)

050 Name1 Nickname1

100 Name2 Nickname2

150 Name3 Nickname3

200 Name4 Nickname4

250 Name5 Nickname5

300 Name6 Nickname6

400 Name7 Nickname7

 

 

And the standings result for the group Grouper would be:

(only max weight catch per angler shown)

(Angler Number) (Catch type of fish) (catch weight) (catch pic)

150 Grouper 66 lbs fish34d20070324.jpg

300 Grouper 10 lbs fish21d20070324.jpg

100 Grouper 5 lbs fish33d20070324.jpg

 

 

Link to comment
Share on other sites
darylfromjax Newbie

darylfromjax

Posted
  • Author
Posted

(mysql Ver 12.22 Distrib 4.0.16

PHP 4.4.1

OS: FreeBSD 4.11-STABLE )

 

I tried the following and it did not work after I added the portion is underlined.

 

CREATE TABLE `myCatch` SELECT `c_angler_num`, max(`c_weight`) AS c_max_weight, `c_species` from `catch`

WHERE `c_species`='Grouper' GROUP BY `c_angler_num`;

 

ALTER TABLE `myCatch` ADD INDEX `c_angler_num` ( `c_angler_num` );

 

SELECT catch.c_angler_num, catch.c_species, catch.c_num, catch.c_weight

FROM `catch`

WHERE catch.c_angler_num

IN (SELECT myCatch.c_angler_num FROM `myCatch`);

 

DROP table `myCatch;

 

But, the follwoing produces a list:

CREATE TABLE `myCatch` SELECT `c_angler_num`, max(`c_weight`) AS c_max_weight, `c_species` from `catch`

WHERE `c_species`='Grouper' GROUP BY `c_angler_num`;

 

ALTER TABLE `myCatch` ADD INDEX `c_angler_num` ( `c_angler_num` );

 

SELECT catch.c_angler_num, catch.c_species, catch.c_num, catch.c_weight

FROM `catch` WHERE catch.c_angler_num;

 

DROP table `myCatch;

 

Why did adding the "IN (SELECT...)" not work?

 

Daryl

 

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.