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!

Specific layout 1

Status
Not open for further replies.
pookie62

pookie62

Technical User
Oct 22, 2004
139
GB
Hi,
I want to display a table in a specific way.
Matchnames(Wedstrijdnaam) as toprow
Firstname + Lastname in second column
Perc in crosscell
Like:
"Empty Cell" | Match1 | Match2 | Match3
Firstname+Lastname | Perc (for this match) | Perc | -- (when there's no score for this match

Here's the script I use now, this displays everything vertically.
Thanks in advance !
Code: 
<?php
 require_once('../../Connections/ASN.php'); ?>
<?php
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN);
    
    $query="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)";
    $result = mysql_query ($query)
        or die ("Query failed");

    // printing HTML result

    print "<table>\n";
    $cnt = 0;
    while ($line = mysql_fetch_assoc($result)):
        print "\t<tr>\n";
        if ($cnt === 0):
            foreach ($line  as $key=>$val):
                echo "<td>$key</td>";
            endforeach;
            echo "</tr><tr>";
            $cnt++;
        endif;        
        foreach ($line as $val):
            print "\t\t<td>$val</td>\n";
        endforeach;
        print "\t</tr>\n";
    endwhile;
    print "</table>\n";
?>
 
Sort by date Sort by votes
it's just a question of outputting <td> </td> etc at the right point


if not all your data for each block is in a single row, you may have to read all the data into an array or set of arrays first
 
Upvote 0 Downvote
Hi jpadie,
Thanks for your reply.
Can you provide an example how to read into sets of arrays and how to call the data then ?
Appreciate..
 
Upvote 0 Downvote
Oke, got the matchnames beneath eachother, but want them besides eachother (toprow)
please advise..

Code: 
$Match="SELECT DISTINCT `wedstrijd`.`Naam` AS `Wedstrijd` FROM `overzicht` LEFT OUTER JOIN `wedstrijd` ON (`overzicht`.`WedstrijdId` = `wedstrijd`.`Id`)";
$res = mysql_query ($Match)
        or die ("Match Query failed");
		while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
$wnaam= $row['Wedstrijd'];
$wnames = array ($wnaam);

print '<html><body><table border=1>';

$table_width = 30;
$table_counter = 0;
foreach ($wnames as $theelement)
{
    if ($table_counter == 0)
    {
        print '<tr><td';
    }
    
    print '<td>' . $theelement . '</td>';
    
    $table_counter++;
    
    if ($table_counter == $table_width)
    {
        print '</td>';
        $table_counter = 0;
    }
}

for ($counter = 0; $counter < $table_width - $table_counter; $counter++)
{
    //print '<td> </td>';
}

print '</table></body></html>';
}
?>
 
Upvote 0 Downvote
so you have a recordset full of matchnames.

Code: 
echo "<table><tr>";
$cnt =0;
while ($row = mysql_fetch_assoc($results):
 
  if ($cnt === 30):
    echo "</tr><tr>";
    $cnt=0;
  endif;
?>
<td><?=$row['Wedstrijd']?></td>
<?
  $cnt++;
endwhile;
echo "</tr></table>";
 
Upvote 0 Downvote
Hi jpadie,
Probably doing something stupid, but I get parse error all the time om line 12 > while ($row........
Replaced the semicolon with { but no success
Any suggestions ?
This is the complete code:
Code: 
<?php
error_reporting(E_ALL); 
mysql_select_db($database_ASN, $ASN);
 
$Match="SELECT DISTINCT `wedstrijd`.`Naam` AS `Wedstrijd` FROM `overzicht` LEFT OUTER JOIN `wedstrijd` ON (`overzicht`.`WedstrijdId` = `wedstrijd`.`Id`)";
$res = mysql_query ($Match)
        or die ("Match Query failed");
 
echo "<table><tr>";
$cnt =0;
while ($row = mysql_fetch_assoc($res){
   if ($cnt === 30){
    echo "</tr><tr>";
    $cnt=0;
  endif;
}
?>
<td><?=$row['Wedstrijd']?></td>
<?
  $cnt++;
endwhile;
echo "</tr></table>";
}
?>
 
Upvote 0 Downvote
it needs two closing brackets. also the if statement within it has had its syntax mixed. either:
Code: 
if (criterion): //colon

else: //colon
endif; //semicolon
or
Code: 
if (criterion) { //open curly brace

} else { //close - open curly braces

} //close curly brace
 
Upvote 0 Downvote
Oke, that's clear (didn't know this) but anyhow.. still getting the error..
pasted your original example in the code but there's no difference..
BTW Thank you very much for helping me out!!!

Code: 
$Match="SELECT DISTINCT `wedstrijd`.`Naam` AS `Wedstrijd` FROM `overzicht` LEFT OUTER JOIN `wedstrijd` ON (`overzicht`.`WedstrijdId` = `wedstrijd`.`Id`)";
$res = mysql_query ($Match)
        or die ("Match Query failed");
 
echo "<table><tr>";
$cnt =0;
while ($row = mysql_fetch_assoc($results):
  if ($cnt === 30):
    echo "</tr><tr>";
    $cnt=0;
  endif;
?>
<td><?=$row['Wedstrijd']?></td>
<?
  $cnt++;
endwhile;
echo "</tr></table>";
?>
 
Upvote 0 Downvote
it still needs two closing brackets!

Code: 
while ($row = mysql_fetch_assoc($results)): //extra close bracket
 
Upvote 0 Downvote
Oops.. missed that..! Sorry and thanks again..
 
Upvote 0 Downvote
Oke, now this:
How do I combine this code with the previous ?
Output from this code is the Klasse, firstname lastname en Perc (this is the competitors result.
But this result has to link to the match in which he got this result of course..
Code: 
 $query="SELECT `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)";
    $result = mysql_query ($query)
        or die ("Query failed");

    // printing HTML result

    print "<table>\n";
    $cnt = 0;
    while ($line = mysql_fetch_assoc($result)):
        print "\t<tr>\n";
        if ($cnt === 0):
            foreach ($line  as $key=>$val):
                echo "<td>$key</td>";
            endforeach;
            echo "</tr><tr>";
            $cnt++;
        endif;        
        foreach ($line as $val):
            print "\t\t<td>$val</td>\n";
        endforeach;
        print "\t</tr>\n";
    endwhile;
    print "</table>\n";
?>
 
Upvote 0 Downvote
can you post your table structures and a couple of lines of output from the query you have included in the post above.
 
Upvote 0 Downvote
Table structure "overzicht":
`WedstrijdId` decimal(10,0) NOT NULL default '0', `Voornaam` varchar(255) NOT NULL default '', `DeelnemerId` decimal(10,0) NOT NULL default '0', `KlasseId` varchar(10) NOT NULL default '0',
`Percentage` decimal(10,2) NOT NULL default '0.00')

Output from query in last post:
Voornaam Achternaam Perc //This isn't needed neither
Pietje Puk 0.85
Pietje Puk 0.80
Pietje Puk 0.52
Arie Dekker 0.86
Arie Dekker 0.90
Arie Dekker 0.90
The results you see, are from matches.( I imported three matches for testing)
I was thinking that names of competitors should only appear once and with their results in the column of the match, which you created in the posts above.
 
Upvote 0 Downvote
is there a maximum number of matches you want to display? and matches are on the x-axis, people on the y-axis?
 
Upvote 0 Downvote
No maximum on matches (last year there were 20, this year (until now) there are 20, could become more..
Yes Matches displayed horizontal (that is the x-axis right?(can't get that in my head..) and people vertical
 
Upvote 0 Downvote
so it's however many matches there are? and i guess it's possible that a particular player does not play in a particular match?

i think you may have to reverse your query so that you are pulling up match, name, score so that we can be certain the table will work.
 
Upvote 0 Downvote
so it's however many matches there are? and i guess it's possible that a particular player does not play in a particular match?
Click to expand...
Yep and correct. :)
i think you may have to reverse your query so that you are pulling up match, name, score so that we can be certain the table will work.
Click to expand...
Like this?:
Code: 
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)
 
Upvote 0 Downvote
RH Challenge OKP Pietje Puk 0,52
RH Challenge OKP Theo Schoen 0,74
PGein OKP Harry Hofstad 0,98
PGein OKP Hans Eger 0,81
PGein OKP Rinus Bree 0,68

Note: I'm already selting in the query the OKP Klasse, so no need to display.
 
Upvote 0 Downvote
sorry for delay - real work intervenes!

here is some code for you to try. i couldn't work out whether klasse was a function of the race or the athlete so i have ignored it.

i also think that there may be another way of doing this that is more elegant, with fewer nested loops, but (i) it's nearly 2 am my time; and (ii) with only 30 races max you are hardly going to notice the difference!

note i've also changed the AS clause to naam as i kept spelling wedstrijd wrong (despite having a dutch wife).

lastly i have not been able to test this code for anything other than parse errors as i don't have your table structure replicated nor dummy date to insert.

Code: 
<?
$sql = "SELECT 
`wedstrijd`.`Naam` AS `naam`,
`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);
";

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

    // printing HTML result
//first read everything into arrays

/* used for debug
$rows[] = array("naam"=>"RH Challenge","Voornaam"=> "Pietje", "Achternaam"=> "Puk", "Perc"=>"0,52");
$rows[] = array("naam"=>"RH Challenge","Voornaam"=> "Theo", "Achternaam"=> "Schoen", "Perc"=>"0.74");
$rows[] = array("naam"=>"PGein","Voornaam"=> "Harry", "Achternaam"=> "Hofstad", "Perc"=>"0.98");
$rows[] = array("naam"=>"PGein","Voornaam"=> "Hans", "Achternaam"=> "Eger", "Perc"=>"0.81");
$rows[] = array("naam"=>"PGein","Voornaam"=> "Rinus", "Achternaam"=> "Bree", "Perc"=>"0.68");
*/

//read every row into arrays
$athletes = array();
$races = array();


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

/*
FOR TESTING WITH DUMMY ARRAY#
foreach ($rows as $row):	//first the race
	if (!in_array($row['naam'],$races)):	//this builds an array of race names for later use
		$races[] = $row['naam'];
	endif;
	$athletes[$row['Voornaam']." ".$row['Achternaam']][$row['naam']] = $row['Perc'];
endforeach;
*/

//we now have two nicely formed arrays
//alphabetise the races 
sort($races);
//start the table plot
?>
<table>
<tr>
<th>Races</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><?=$key?></td>
<?
	foreach($races as $race):
		$perc = isset($val[$race]) ? $val[$race] : "DNC"; //DNC is did not compete
		?><td><?=$perc?></td><?		
	endforeach;
?></tr><?
endforeach;
?>
</table>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dkemas
  • Locked
  • Question
adding specific results from a db
Replies
4
Views
90
vacunita
vacunita
dkemas
  • Locked
  • Question
Update specific cell in a spreadsheet
Replies
3
Views
74
jpadie
jpadie
FireGeek21
  • Locked
  • Question
Retrieve specific data from mysql based on HTML value
Replies
10
Views
79
jpadie
jpadie
mcall
  • Locked
  • Question
Uploading file from specific location 1
Replies
3
Views
88
mcall
mcall
explorer1979
  • Locked
  • Question
How to re layout to two col with multi row with MySQL Data
Replies
0
Views
44
explorer1979
explorer1979

Part and Inventory Search

Sponsor

Back
Top