Jump to content

Recommended Posts

I have this code:

$raceq = 	"SELECT race_table.raceID, race_table.raceDate, pts_table.skipperName, pts_table.position, pts_table.racePoints	
	FROM race_table
	LEFT JOIN pts_table ON race_table.raceID = pts_table.raceID
	WHERE race_table.seriesID = '$seriesID';";
$result3= mysql_query($raceq);
	while ($row3 = mysql_fetch_array($result3))
	{
	echo'
	<table>
	<tr>
	<td>' . $row3['raceID'] . '</td>
	<td>' . $row3['raceDate'] . '</td>
	<td>' . $row3['skipperName'] . '</td>
	<td>' . $row3['position'] . '</td>
	<td>' . $row3['racePoints'] . '</td>
	</tr>
	</table>';
	}

 

which produces this data:

 

13

2008-01-09

kiwi_bardy

1

50

 

13

2008-01-09

kjd

 

2

47

13

 

2008-01-09

TimUSA

3

44

 

13

2008-01-09

Duaner

4

42

 

13

2008-01-09

vang

 

6 - DNF

38

14

 

2008-01-09

TimUSA

1

50

 

14

2008-01-09

Duaner

2

47

 

14

2008-01-09

vang

 

3

44

14

 

2008-01-09

kiwi_bardy

5 - DNF

40

 

15

2008-01-09

clazza

1

50

 

15

2008-01-09

TimUSA

 

2

47

15

 

2008-01-09

kiwi_bardy

3

44

 

15

2008-01-09

Duaner

4

42

 

15

2008-01-09

Hawkeye353rd

 

5

40

15

 

2008-01-09

kjd

6

38

 

15

2008-01-09

chris

7

37

 

 

and i want it to look like this:

   

       

           

Skipper Name

           

RACE ID:

            DATE:

           

RACE ID

            DATE:

           

RACE ID

            DATE:

           

TOTAL PTS:

       

       

           

Player Name 1

            ORDER BY HIGHEST

            TOTAL POINTS

           

position / points

            0 if null

           

position / points

            0 if null

           

position / points

            0 if null

           

X

       

       

           

Player Name 2

           

 

           

 

           

 

           

 

       

       

           

Player Name 3

           

 

           

 

           

 

           

 

       

       

           

Player Name 4

           

 

           

 

           

 

           

 

       

       

           

Player Name 5

           

 

           

 

           

 

           

 

       

       

           

Player Name 6

           

 

           

 

           

 

           

 

       

       

           

Player Name 7

           

 

           

 

           

 

           

 

       

   

 

anyone wanna take a stab at this?

my guess is i need to change the query, but i am stumped:(

SKIPPER NAME  $row3['raceID'] / $row3['raceDate']  TOTAL POINTS

$row3['skipperName']  $row3['position'] / $row3['racePoints'] total of points in row

 

if $row3['position'] is Null = 0

if $row3['racePoints'] is Null = 0

 

easier to read?

Well you can group that in the query.

 

As for the table, it'll be in this format:

 

<table>
<tr>
<th>Skipper Name</th>
<th>Race ID / Race Date</th>
<th>Total Points</th>
</tr>

<?php
// query your stuff
while ($row = mysql_fetch_assoc($result)){
echo "<tr><td>{$row['skipperName']}</td><td>{$row['position']} / {$row['racePoints']}</td><td>TOTAL POINTS</td></tr>";
}
?>

</table>

hmmm stumped again:

 

what i need is

 

 

                Unique          Unique

                raceID          raceID

Name:    Date              Date ect...        Total Pts.

 

Player 1  pos/pts          pos/pts ect...    totpts

            (for UniqueID)  (for UniqueID)

 

Player 2  pos/pts          pos/pts ect...    totpts

            (for UniqueID)  (for UniqueID)

 

Player 3  pos/pts          pos/pts ect...    totpts

            (for UniqueID)  (for UniqueID)

 

Player 4  pos/pts          pos/pts ect...    totpts

            (for UniqueID)  (for UniqueID)

 

and then a column for each unique raceID is formed

am positive my query is wrong for this, but am lost

 

$raceq = "SELECT race_table.raceID, race_table.raceDate, pts_table.skipperName, pts_table.position, pts_table.racePoints
FROM race_table
LEFT JOIN pts_table ON race_table.raceID = pts_table.raceID
WHERE race_table.seriesID = '$seriesID';";

the relevent info in the two tables look something like this

 

race_table

raceID / raceDate / seriesID

  1        01/02/08    5 //if you see the full code, the cuurent query filters raceID by a selected seriesID

  2        01/03/08    5

  3        01/04/08    5

 

 

pts_table

ptsID / raceID / skipperName / position / racePoints

1              1          skip1            1            50

2              1          skip2            2            47

3              1          skip3            3            44

4              2          skip1            1            50

5              2          skip2            2            47

6              2          skip3            3            44

7              3          skip1            1            50

8              3          skip2            2            47

9              3          skip3            3            44

 

if this helps at all

 

still lost on this, but getting closer.

the queries are as follows:

if (isset($_POST['submitted'])) 
{
$series = mysql_real_escape_string($_POST['series']);
$seriesq = "SELECT seriesID
	FROM series_table
	WHERE seriesName = '$series';";
$result2 = mysql_query($seriesq);
$row2 = mysql_fetch_row($result2);
$seriesID = $row2[0];

$raceq =	"SELECT race_table.raceID, race_table.raceDate, pts_table.skipperName, pts_table.position, pts_table.racePoints
	FROM pts_table
	LEFT OUTER JOIN race_table ON pts_table.raceID = race_table.raceID
	WHERE race_table.seriesID = '$seriesID'
	GROUP BY pts_table.skipperName, race_table.raceID
	ORDER BY race_table.raceID, pts_table.racePoints DESC;";
$result3 = mysql_query($raceq);

	while($row3 = mysql_fetch_array($result3))
	{
	echo'
	//DO STUFF
	}
}

 

and this is where I got with this, in a separate conversation, however I don't know how to execute this in PHP

Tom says (4:42 PM):

you can write a pretty complicated query that uses nested selects in a select statement

well, you could, but why?

just use this one, it comes out in the order you want.

Tom says (4:43 PM):

you'll need to modify it to add rows for races a skipper didn't sail in (which is easy, do a LEFT OUTER JOIN instead of a LEFT JOIN)

I'd then join the table to the race table, to insure I get all the races, not just the races people raced in

Tom says (4:44 PM):

once you do that, you can simply have a loop in your code that looks something like this:

write HTML columns for skipper

while (raceID is the same as the last raceID)

write HTML for race ID, race finsih, race points

end while

Tom says (4:45 PM):

that's obviously psuedo code, because I don't know PHP for beans

 

so end of the short is trying to get a table that looks like similar to this (css stuff later important thing is to get the colums and rows):

 

 

 

[attachment deleted by admin]

Assuming you queried it correctly, your while loop should be something like:

 

<?php
echo "<table>";
while($row3 = mysql_fetch_array($result3)){
echo "<tr><td>{$row['skipperName']}</td><td>{$row['position']} / {$row['racePoints']}</td><td>TOTAL POINTS</td></tr>";
}
echo "</table>";

 

Right?

well maybe close,

 

what i cant seem to get is

 

1. one table column for skipperName

  -table rows with names grouped

 

2. one table column for each race id

    -table rows with position / points for that particular raceID

    (this would expand out for however many raceIDs)

 

3. one table column for total

    -this would add the points from all the other columns

 

Last but not least I need to sort the rows by total points.

in this case the math is easier to do in php because their are some variables for discards that will be set.

 

wow, this is a pain.

correct. but how do i get the raceID as the column headers?

if I do this inside

while($row3 = mysql_fetch_array($result3))
{

then it will loop the header?

Then do it outside the loop  :-\ Use common sense here  ;D

 

 

well maybe close,

 

what i cant seem to get is

 

1. one table column for skipperName

  -table rows with names grouped

 

2. one table column for each race id

    -table rows with position / points for that particular raceID

    (this would expand out for however many raceIDs)

 

3. one table column for total

    -this would add the points from all the other columns

 

Last but not least I need to sort the rows by total points.

in this case the math is easier to do in php because their are some variables for discards that will be set.

 

wow, this is a pain.

So what's the output?

basically like this: which is wrong, but it works for now!

and the full code for the page is this:

global $scripturl;
echo'
<a href="http://vsk-ayc.totalh.com/index.php?page=210">RETURN TO THE START PAGE</a><br>';
$query1 = 	"SELECT seriesName
                     FROM series_table
                     WHERE seriesID NOT IN ( 1, 2 ) 
                     LIMIT 0 , 30;";
$result = mysql_query($query1);

echo '
<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
<TR class ="catbg">
<TD width = "100%">Series Results:</td>
</table>
<form action="'.$scripturl.'?page=214'.$GET['page'].'" method="post">
<input type="hidden" name="submitted" value="true">
	<table>
		<tr>
			<td align="left"><p><b>Select Race Series To View :</b></p><SELECT id="series" name="series" style="WIDTH: 200px" value ="';
			echo '" />';
				while($row = mysql_fetch_row($result))
				{
				print("<option value=\"$row[0]\">$row[0]</option>");
				}	
			echo'
			</select></td>
		</tr>
		<tr>
			<td><input type="submit" value="Submit">
			<input type="reset" value="Reset"></td>
		</tr>
	</table>
</form><br>';

if (isset($_POST['submitted'])) 
{
$series = mysql_real_escape_string($_POST['series']);
$seriesq = 	"SELECT seriesID
		FROM series_table
		WHERE seriesName = '$series';";
$result2 = mysql_query($seriesq);
$row2 = mysql_fetch_row($result2);
$seriesID = $row2[0];

$raceq =	"SELECT pts_table.skipperName, sum( pts_table.racePoints ) AS sumseries
		FROM pts_table
		LEFT OUTER JOIN race_table ON pts_table.raceID = race_table.raceID
		WHERE race_table.seriesID = '$seriesID'
		GROUP BY pts_table.skipperName
		ORDER BY sumseries DESC;";
$result3 = mysql_query($raceq);
echo'
	<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
	<TR class ="catbg">
	<TD width = "100%">' . $series . '</td>
	</table>
	<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
	<TR class ="catbg4">
	<TD width = "75%">Skipper Name:</td>
	<TD width = "25%">Total Race Points:</td>
	</table>';
$x = 1;
	while($row3 = mysql_fetch_array($result3))
	{		 
	echo'
		<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
		<TR>
		<TD width = "75%" class = "titlebg">' . $x++ . ':  ' . $row3['skipperName'] . '</td><br>
		<TD width = "25%" class = "catbg2">' . $row3['sumseries'] . '</td><br>
		</tr>
		</table>';
	}
echo'
	<br>
	<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
	<TR class ="catbg">
	<TD width = "100%">Individual Race Results</td>
	</table>
	<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
	<TR class ="catbg4">
	<TD width = "10%">Race ID:</td>
	<TD width = "10%">Date:</td>
	<TD width = "40%">Skipper Name:</td>
	<TD width = "20%">Position:</td>
	<TD width = "20%">Race Points:</td>
	</table>';

$ptsq =	"SELECT race_table.raceID, race_table.raceDate, pts_table.skipperName, pts_table.position, pts_table.racePoints
		FROM pts_table
		LEFT OUTER JOIN race_table ON pts_table.raceID = race_table.raceID
		WHERE race_table.seriesID = '$seriesID'
		GROUP BY pts_table.skipperName, race_table.raceID
		ORDER BY race_table.raceID, pts_table.racePoints DESC;";
$result4 = mysql_query($ptsq);
	while($row4 = mysql_fetch_array($result4))
	{
	echo'
		<TABLE class = "bordercolor" cellSpacing="1" cellPadding="1" width="95%" border="0">
		<TR class ="windowbg">
		<TD width = "10%">' . $row4['raceID'] . '</td>
		<TD width = "10%">' . $row4['raceDate'] . '</td>
		<TD width = "40%">' . $row4['skipperName'] . '</td>
		<TD width = "20%">' . $row4['position'] . '</td>
		<TD width = "20%">' . $row4['racePoints'] . '</td>
		</table>';
	}			
}

 

and aqain, trying to get it t olook more like this:

index.php?action=dlattach;topic=176448.0;attach=3537;image

 

[attachment deleted by admin]

the Pl(1.R) ect... would be Position in Race 1 or for our purposes Position in raceID with the numbers inside that column being the finish position.

 

The brackets are for discarded races (not count in total.) This is often done in regatta scoring, and basically gives the sailor the option of throwing out the worst race. For My purposes we dont need to do that,

Ah okay. I think I get you.

 

Before I start, I rather not second-think myself on what certain things are. Can you tell me how you're sorting them? How did you get peteygfx number 1? Like what's determining the ordering of the list? I don't see how the raceID would work here. I mean peteygfx has 3, 8 and 1. How did he/she get rank 1? Is the ranking column already sorted at all times? If not, how is it calculated?

 

So the question is: how is the table being sorted?

the table shown uses a low point scoring system for finish positions where:

(it should also be noted that this was generated in another software package)

1 = 0pts

2 = 3

3 = 5.7 ect

 

so the lowest total points scored = 1st place in the sorting

 

in my db i use a high point system so highest points = 1st place in the sorting

but the principle is the same.

 

the points calculation is down in another script.

 

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.

×
×
  • 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.