Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting columns 1

Status
Not open for further replies.
pookie62

pookie62

Technical User
Oct 22, 2004
139
GB
Hi,
jpadie wrote me this script for outputting scores from different races. (For which I'm thankfull eternally)
It does work nice, but there's one little thing I'd like enhanced. the sorting of the columns.
I've looked into the sort() function but I don't get it working the way I want.
All races have their own date, which is stored in the table "wedstrijd" field name is "Datum".
I'd like to have the races displayed chronological (is this correct written?).
This is the script:
Code: 
<?php
include("../../Loginsys/include/session.php");
if($session->logged_in){
?>
<style type="text/css">
<!--
body {
	background-image: url(../../images/bg_grad.jpg);
	margin-top: 30px;
}
.style1 {
	color: #FFFFFF;
	font-weight: bold;
	font-size: 14pt;
}
-->
</style>
<?php
 require_once('../../Connections/ASN.php'); ?>
<?php
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN);

$sql = "SELECT `wedstrijd`.`Naam` AS `wedstrijd`,`klasse`.`Klasse`,`overzicht`.`Voornaam` AS `Voornaam`,`deelnemer`.`Naam` AS `Achternaam` , `overzicht`.`Percentage` AS `Perc` FROM `overzicht` INNER JOIN `deelnemer` ON (`overzicht`.`DeelnemerId` = `deelnemer`.`Id`) INNER JOIN `wedstrijd` ON (`overzicht`.`WedstrijdId` = `wedstrijd`.`Id`) INNER JOIN `klasse` ON (`overzicht`.`KlasseId` = `klasse`.`Id`) WHERE (`klasseID` = 1) ORDER BY Voornaam";

$result = mysql_query ($sql) or die ("Query failed ".mysql_error());

// printing HTML result
//read every row into arrays

$athletes = array();
$races = array();


while ($row=mysql_fetch_assoc($result)):
    
    //first the race
    if (!in_array($row['wedstrijd'],$races)):    //this builds an array of race names for later use
        $races[] = $row['wedstrijd'];
    endif;
    $athletes[$row['Voornaam']." ".$row['Achternaam']][$row['wedstrijd']] = $row['Perc'];
endwhile;


//we now have two nicely formed arrays
//alphabetise the races 
//sort($races);
//start the table plot
?>
<CAPTION>
<div align="center" class="style1">Overzicht behaalde percentages
</div>
<table border="1">
<tr>
<th><u>Wedstrijd <br>
</u><br>
  Deelnemer</th>
<?
foreach ($races as $val):
?> <th><?=$val?></th> <?
endforeach;
?>
</tr> 
<?
//now the header rows are down
foreach ($athletes as $key=>$val):    //athletes names are stored in the key
?>
<tr><td nowrap><?=$key?></td>
<?
    foreach($races as $race):
		$perc = isset($val[$race]) ? $val[$race] : "--"; //is did not compete
        $tmpperc = ($perc)*100;
		$totperc = round($tmpperc, 3);
		?><td><?=$totperc?></td><?        
    endforeach;
?></tr><?
endforeach;
?>
</table>
<?php
}
if(!$session->logged_in){
echo "Je bent niet ingelogd, dus geen toegang tot deze pagina.";
}
?>
 
Sort by date Sort by votes
i need some data to populate the db at my side then. can you use phpmyadmin (or whatever) to dump some inserts for me for the relevant tables? if too big to post to tek-tips then you can mail them to my handle AT hotmail.com

cheers
justin
 
Upvote 0 Downvote
sended all relevant tables to your hotmail in zip file..
Thanks a lot !
 
Upvote 0 Downvote
further to email exchange try this code
Code: 
<?
include("../../Loginsys/include/session.php");
if($session->logged_in){
?>
<style type="text/css">
<!--
body {
    background-image: url(../../images/bg_grad.jpg);
    margin-top: 30px;
}
.style1 {
    color: #FFFFFF;
    font-weight: bold;
    font-size: 14pt;
}
-->
</style>
<?php
 require_once('../../Connections/ASN.php'); ?>
<?php
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN);

$sql = 
"SELECT 
  wedstrijd.Id, 
  wedstrijd.Naam AS wedstrijd, 
  wedstrijd.Datum as datum, 
  wedstrijd.Plaats as plaats, 
  deelnemer.Voornaam as voornaam, 
  deelnemer.Naam as surname, 
  overzicht.KlasseId as klasseID,
  klasse.Klasse as klasse, 
  overzicht.Percentage as perc
FROM 
  deelnemer 
  INNER JOIN 
      ((overzicht 
        INNER JOIN 
        klasse 
         ON 
         overzicht.KlasseId = klasse.Id) 
        INNER JOIN 
        wedstrijd 
         ON 
         overzicht.WedstrijdId = wedstrijd.Id) 
      ON deelnemer.Id = overzicht.DeelnemerId
WHERE
	overzicht.KlasseID = 2
ORDER BY
	wedstrijd.Datum,klasse.Klasse,deelnemer.Voornaam
";

$result = mysql_query ($sql) or die ("Query failed ".mysql_error());

// printing HTML result
//read every row into arrays

$athletes = array();
$races = array();


while ($row=mysql_fetch_assoc($result)):
    
    //first the race
    if (!in_array($row['wedstrijd'],$races)):    //this builds an array of race names and dates for later use
        $races[$row['wedstrijd']] = array("date"=>$row['datum'], "location" => $row['plaats']);
    endif;
    $athletes[$row['Voornaam']." ".$row['Achternaam']][$row['wedstrijd']]= $row['Perc'];	//we have now reshaped this array
endwhile;


//we now have two nicely formed arrays
//alphabetise the races 
//sort($races);
//start the table plot
?>
<CAPTION>
<div align="center" class="style1">Overzicht behaalde percentages
</div>
<table border="1">
<tr>
<th><u>Wedstrijd <br>
</u><br>
  Deelnemer</th>
<?
//this has been reshaped to show the date and the location
foreach ($races as $racename=>$racedata):
?> <th align="center"><? echo $racename."<br/><span style=\"font-size:smaller\">".$racedata['location']." (".date("d-M-Y", strtotime($racedata['date'])).")</span>";?></th> <?
endforeach;
?>
</tr> 
<?
//now the header rows are down
foreach ($athletes as $key=>$val):    //athletes names are stored in the key
?>
<tr><td nowrap><?=$key?></td>
<?
    foreach($races as $race):
		$perc = isset($val[$race]) ? $val[$race] : "--"; //is did not compete
        $tmpperc = ($perc)*100;
		$totperc = round($tmpperc, 3);
		?><td><?=$totperc?></td><?        
    endforeach;
?></tr><?
endforeach;
?>
</table>
<?php
}
if(!$session->logged_in){
echo "Je bent niet ingelogd, dus geen toegang tot deze pagina.";
}
?>
 
Upvote 0 Downvote
$perc = isset($val[$race]) ? $val[$race] : "--"; //is did not compete is still giving Illegal offset type
and no scores are visible..

Columnheaders are looking great and displayed chronological !
It's getting there..
Didn't you get the zip file with data ?
 
Upvote 0 Downvote
no zip file i'm afraid.

change the relevant foreach line (line 99) to the following:
Code: 
   foreach($races as $race=>$racedata):
that should fix it!
 
Upvote 0 Downvote
YES !!
Thanks a million, really appreciate all your help !!!
 
Upvote 0 Downvote
no ... it won't...

here is the complete code again.
Code: 
<?
include("../../Loginsys/include/session.php");
if($session->logged_in){
?>
<style type="text/css">
<!--
body {
    background-image: url(../../images/bg_grad.jpg);
    margin-top: 30px;
}
.style1 {
    color: #FFFFFF;
    font-weight: bold;
    font-size: 14pt;
}
-->
</style>
<?php
 require_once('../../Connections/ASN.php'); ?>
<?php
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN);


$sql = 
"SELECT 
  wedstrijd.Id, 
  wedstrijd.Naam AS wedstrijd, 
  wedstrijd.Datum as datum, 
  wedstrijd.Plaats as plaats, 
  deelnemer.Voornaam as voornaam, 
  deelnemer.Naam as surname, 
  overzicht.KlasseId as klasseID,
  klasse.Klasse as klasse, 
  overzicht.Percentage as perc
FROM 
  deelnemer 
  INNER JOIN 
      ((overzicht 
        INNER JOIN 
        klasse 
         ON 
         overzicht.KlasseId = klasse.Id) 
        INNER JOIN 
        wedstrijd 
         ON 
         overzicht.WedstrijdId = wedstrijd.Id) 
      ON deelnemer.Id = overzicht.DeelnemerId
WHERE
	overzicht.KlasseID = 2
ORDER BY
	wedstrijd.Datum,klasse.Klasse,deelnemer.Voornaam
";

$result = mysql_query ($sql) or die ("Query failed ".mysql_error());

// printing HTML result
//read every row into arrays

$athletes = array();
$races = array();


while ($row=mysql_fetch_assoc($result)):
    
    //first the race
    if (!in_array($row['wedstrijd'],$races)):    //this builds an array of race names and dates for later use
        $races[$row['wedstrijd']] = array("date"=>$row['datum'], "location" => $row['plaats']);
    endif;
    $athletes[$row['voornaam']." ".$row['surname']][$row['wedstrijd']]= $row['perc'];	//we have now reshaped this array
endwhile;


//we now have two nicely formed arrays
//alphabetise the races 
//sort($races);
//start the table plot
?>
<CAPTION>
<div align="center" class="style1">Overzicht behaalde percentages
</div>
<table border="1">
<tr>
<th><u>Wedstrijd <br>
</u><br>
  Deelnemer</th>
<?
//this has been reshaped to show the date and the location
foreach ($races as $racename=>$racedata):
?> <th align="center"><? echo $racename."<br/><span style=\"font-size:smaller\">".$racedata['location']." (".date("d-M-Y", strtotime($racedata['date'])).")</span>";?></th> <?
endforeach;
?>
</tr> 
<?
//now the header rows are down
foreach ($athletes as $key=>$val):    //athletes names are stored in the key
?>
<tr><td nowrap><?=$key?></td>
<?
    foreach($races as $race=>$racedata):
		$perc = isset($val[$race]) ? $val[$race] : "--"; //is did not compete
        $tmpperc = ($perc)*100;
		$totperc = round($tmpperc, 3);
		?><td><?=$totperc?></td><?        
    endforeach;
?></tr><?
endforeach;
?>
</table>
<?php
}
if(!$session->logged_in){
echo "Je bent niet ingelogd, dus geen toegang tot deze pagina.";
}
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

A1Simon
  • Locked
  • Question
PHP to create table columns
Replies
2
Views
117
southbeach
southbeach
Kaylech
  • Locked
  • Question
Stumped on sorting issues
Replies
5
Views
170
Kaylech
Kaylech
southbeach
  • Locked
  • Question
Read and List MySQL table columns even if table is blank
Replies
1
Views
117
southbeach
southbeach
Dashley
  • Locked
  • Question
String to date sorting array 1
Replies
3
Views
103
Dashley
Dashley
southbeach
  • Locked
  • Question
A question about text file + arrays + sorting arrays before I dive in! 1
Replies
5
Views
136
jpadie
jpadie

Part and Inventory Search

Sponsor

Back
Top