Trying to format query into a table correctly

TimUSA · January 10, 2008

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:(

Ken2k7 · January 10, 2008

There's not much difference in this one other than understanding some HTML.

Can you fill out some information onto the table from that data so I know what data goes where? Right now, I have no clue. I may help you out here.

TimUSA · January 10, 2008

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?

Ken2k7 · January 10, 2008

A bit, how is the total points being calculated?

TimUSA · January 10, 2008

figured i could do that with php

also forgot to note that what i am trying to acheive is a column for each raceID/date

but have the skipper name grouped so only appearing on table once.

Ken2k7 · January 10, 2008

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>

TimUSA · January 10, 2008

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';";

Ken2k7 · January 10, 2008

Problem is, what to do with the player with multiple results in the database? Get the last one?

TimUSA · January 10, 2008

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

Ken2k7 · January 10, 2008

Well it helps but I can't make a connection between seriesID and the pts_table whatsoever. Do you not want to sort them using seriesID?

TimUSA · January 10, 2008

yep this is done in another query and then

WHERE race_table.seriesID = '$seriesID';";

TimUSA · January 10, 2008

bump

TimUSA · January 10, 2008

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]

TimUSA · January 11, 2008

think i am over my head

Ken2k7 · January 11, 2008

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?

TimUSA · January 11, 2008

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?

TimUSA · January 11, 2008

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.

Ken2k7 · January 11, 2008

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

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?

TimUSA · January 11, 2008

i am assuming you mean what does the query look like when it is run, so here we go:

[attachment deleted by admin]

Ken2k7 · January 11, 2008

Uh that just shows me the database table. I meant what does your code output NOW? Like what format?

TimUSA · January 12, 2008

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]

Ken2k7 · January 12, 2008

Problem is, I don't understand this chart:

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

What's with the brackets [] and the number inside, and what's Pl.(1.R.) etc. ???

TimUSA · January 12, 2008

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,

Ken2k7 · January 12, 2008

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?

TimUSA · January 12, 2008

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.

Sign In

Trying to format query into a table correctly

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information