Exporting Queries

[stb74]

Hi

I have a soccer/football site and I have been asked to supply newspapers with the latest results and league tables. 

I would like to be able to do this with the use of a button and export it to an excel spreadsheet.

Is this possible.

;)

[stb74]

I have been able to use the tutorial from this site.

http://www.phpfreaks.com/tutorials/114/0.php

Has anyone any ideas how I would add in details to export the fixtures and league table.

Thanks

[fenway]

Details like what?

[stb74]

I need to be able to export the league table and fixture data in a tab delimeted text file.

Is it possible to use the php coding that displays the finished league table as an input to the export script. 

[fenway]

[quote author=stb74 link=topic=112648.msg458107#msg458107 date=1161883515]
I need to be able to export the league table and fixture data in a tab delimeted text file.

Is it possible to use the php coding that displays the finished league table as an input to the export script. 
[/quote]
I'm sorry, I don't quite understand... "input to the export script"?

[stb74]

Sorry never really great a trying to explain these.

If you have a look at my site here you will see the league table.

http://www.nifootball.co.uk/index.php?option=com_wrapper&Itemid=65

And the fixtures.

http://www.nifootball.co.uk/index.php?option=com_wrapper&Itemid=239

I would like to export those to a tab delimited file.

[stb74]

This is my code that displays the league table to the screen.

Can I use the variables from this to output the data to a file.

[code]
// Start Table loop.
$posnum=1;
$line=0;
$i=0;
while($i < $qty)
{
// Start Display Promotion Relegation Lines.
do
{
if($row_divisions['divisionID'] == $defaultdivisionid)
{
$draw_line = explode(",", $row_divisions['divisionLine']);
}
} while($row_divisions = mysql_fetch_assoc($divisions));

if(isset($draw_line))
{
for($k = 0 ; $k < sizeof($draw_line) ; $k++)
{
if($draw_line[$k] == $line)
{
$templine_width = $tb_width-20;
echo '<tr>';
echo '<td height="5" colspan="20" align="center" valign="middle">
<img src="images/line.gif" width="100%" height="5" ALT=""><br>';
echo '</td>';
echo '</tr>';
}
}
}
                    // End Display Promotion Relegation Lines.

// Start Print Table.
echo '<tr>';
echo '<td align="center" valign="middle" class="tablerows">';
echo "$posnum";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo "$teamstatus[$i]";
echo '</td>';

echo '<td align="left" valign="middle" class="tablerows">';
echo "$team[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo "$pld[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$wins[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$draws[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$loses[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$goals_for[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$goals_against[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$diff[$i]";
echo '</td>';

echo '<td align="center" valign="middle" class="tablerows">';
echo"$points[$i]";
echo '</td>';


$i++;
$j++;
$line++;
$posnum++;
}
// End Table loop..

echo '</tr>';
echo '</table>';
echo '</td>';
echo '</tr>';
[/code]

[fenway]

Sure... instead of producing HTML output, simply produce tab-dellimited output.

[stb74]

I have been able to produce some output put it doesn't want to pull in the table to fill the teams and the wins, loses, pts etc.


It will print the headers ok.

Here is my code so far

<?php

include('../../../Connections/nifootball_stats.php');
// Include Configuration files.
include ('../includes/prefs.php');
include ('../includes/lastupdated.php');
include ('../includes/leaguedata.php');

header ("Content-type: application/csv");
header ("Content-Disposition: inline; filename=nafl_tables.txt");

// Column Headers
echo "$row_divisions[divisionName]\n";
echo("\t");
echo("P\t");
echo("W\t");
echo("D\t");
echo("L\t");
echo("F\t");
echo("A\t");
echo("GD\t");
echo("Pts");
echo "\n";

// Start Loop and read teams in to Table.
$i = 0;
do
{
$team[$i] = $row_get_teams['name'];
$teamid[$i] = $row_get_teams['id'];
//$teamstatus[$i] = $row_get_teams['status'];

// Get Home Wins
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_home_wins = "SELECT COUNT(DISTINCT LM.matchID) AS homewins
FROM nafl_matches LM
WHERE LM.matchHomeWin = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid' ";
$home_wins = mysql_query($query_home_wins, $nifootball_stats) or die(mysql_error());
$row_home_wins = mysql_fetch_assoc($home_wins);
$totalRows_home_wins = mysql_num_rows($home_wins);

//Home wins into the table
$homewins[$i] = $row_home_wins['homewins'];

mysql_free_result($home_wins);

// Get Away Wins
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_away_wins = "SELECT COUNT(DISTINCT LM.matchID) AS awaywins
FROM nafl_matches LM
WHERE LM.matchAwayWin = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid' ";
$away_wins = mysql_query($query_away_wins, $nifootball_stats) or die(mysql_error());
$row_away_wins = mysql_fetch_assoc($away_wins);
$totalRows_away_wins = mysql_num_rows($away_wins);

$awaywins[$i] = $row_away_wins['awaywins'];

mysql_free_result($away_wins);

// Get Home Draws
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_home_draws = "SELECT COUNT(DISTINCT LM.matchID) AS homedraws
FROM nafl_matches LM
WHERE LM.matchHomeDraw = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid' ";
$home_draws = mysql_query($query_home_draws, $nifootball_stats) or die(mysql_error());
$row_home_draws = mysql_fetch_assoc($home_draws);
$totalRows_home_draws = mysql_num_rows($home_draws);

//Home draws into the table
//$row_home_draws = mysql_fetch_assoc($home_draws);
$homedraws[$i] = $row_home_draws['homedraws'];

mysql_free_result($home_draws);

// Get Away Draws
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_away_draws = "SELECT COUNT(DISTINCT LM.matchID) AS awaydraws
FROM nafl_matches LM
WHERE LM.matchAwayDraw = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid' ";
$away_draws = mysql_query($query_away_draws, $nifootball_stats) or die(mysql_error());
$row_away_draws = mysql_fetch_assoc($away_draws);
$totalRows_away_draws = mysql_num_rows($away_draws);

$awaydraws[$i] = $row_away_draws['awaydraws'];

mysql_free_result($away_draws);

// Get Home Loses
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_home_loses = "SELECT COUNT(DISTINCT LM.matchID) AS homeloses
FROM nafl_matches LM
WHERE LM.matchHomeLose = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid' ";
$home_loses = mysql_query($query_home_loses, $nifootball_stats) or die(mysql_error());
$row_home_loses = mysql_fetch_assoc($home_loses);
$totalRows_home_loses = mysql_num_rows($home_loses);

//Home loses into the table
$homeloses[$i] = $row_home_loses['homeloses'];

mysql_free_result($home_loses);

// Get Away Loses.
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_away_loses = "SELECT COUNT(DISTINCT LM.matchID) AS awayloses
FROM nafl_matches LM
WHERE LM.matchAwayLose = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid' ";
$away_loses = mysql_query($query_away_loses, $nifootball_stats) or die(mysql_error());
$row_away_loses = mysql_fetch_assoc($away_loses);
$totalRows_away_loses = mysql_num_rows($away_loses);

$awayloses[$i] = $row_away_loses['awayloses'];

mysql_free_result($away_loses);

// Get Home Goals For.
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_home_goals = "SELECT SUM( LM.matchHomeGoals) AS homegoals
FROM nafl_matches LM
WHERE LM.matchHomeID = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid'";
$home_goals = mysql_query($query_home_goals, $nifootball_stats) or die(mysql_error());
$row_home_goals = mysql_fetch_assoc($home_goals);
$totalRows_home_goals = mysql_num_rows($home_goals);

if(is_null($row_home_goals['homegoals']))
$homegoals[$i] = 0;
else
$homegoals[$i] = $row_home_goals['homegoals'];

mysql_free_result($home_goals);

// Get Away Goals For.
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_away_goals = "SELECT SUM( LM.matchAwayGoals) AS awaygoals
FROM nafl_matches LM
WHERE LM.matchAwayID = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid'";
$away_goals = mysql_query($query_away_goals, $nifootball_stats) or die(mysql_error());
$row_away_goals = mysql_fetch_assoc($away_goals);
$totalRows_away_goals = mysql_num_rows($away_goals);

if(is_null($row_away_goals['awaygoals']))
$awaygoals[$i] = 0;
else
$awaygoals[$i] = $row_away_goals['awaygoals'];

mysql_free_result($away_goals);

// Get Home Goals Against.
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_home_goals_against = "SELECT SUM( LM.matchAwayGoals) AS homegoalsagainst
FROM nafl_matches LM
WHERE LM.matchHomeID = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid'";
$home_goals_against = mysql_query($query_home_goals_against, $nifootball_stats) or die(mysql_error());
$row_home_goals_against = mysql_fetch_assoc($home_goals_against);
$totalRows_home_goals_against = mysql_num_rows($home_goals_against);

if(is_null($row_home_goals_against['homegoalsagainst']))
$homegoalsagainst[$i] = 0;
else
$homegoalsagainst[$i] = $row_home_goals_against['homegoalsagainst'];

mysql_free_result($home_goals_against);

//Away Goals Against.
mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_away_goals_against = "SELECT SUM( LM.matchHomeGoals) AS awaygoalsagainst
FROM nafl_matches LM
WHERE LM.matchAwayID = '$teamid[$i]'
AND LM.matchSeasonID LIKE '$defaultseasonid'
AND LM.matchDivisionID LIKE '$defaultdivisionid'";
$away_goals_against = mysql_query($query_away_goals_against, $nifootball_stats) or die(mysql_error());
$row_away_goals_against = mysql_fetch_assoc($away_goals_against);
$totalRows_away_goals_against = mysql_num_rows($away_goals_against);

if(is_null($row_away_goals_against['awaygoalsagainst']))
$awaygoalsagainst[$i] = 0;
else
$awaygoalsagainst[$i] = $row_away_goals_against['awaygoalsagainst'];

mysql_free_result($away_goals_against);

//
//Calculates points and matches
//

$wins[$i] = ($homewins[$i]+$awaywins[$i]);
$draws[$i] = ($homedraws[$i]+$awaydraws[$i]);
$loses[$i] = ($homeloses[$i]+$awayloses[$i]);
$goals_for[$i] = ($homegoals[$i] + $awaygoals[$i]);
$goals_against[$i] = ($homegoalsagainst[$i] + $awaygoalsagainst[$i]);

//
//Lets make change in points if there are data in nafl_deductedpoints-table
//

mysql_select_db($database_nifootball_stats, $nifootball_stats);
$query_get_deductions = "
SELECT points FROM nafl_deductedpoints
WHERE seasonid LIKE '$defaultseasonid'
AND teamid = '$teamid[$i]' LIMIT 1";
$get_deductions = mysql_query($query_get_deductions, $nifootball_stats) or die(mysql_error());
$row_get_deductions = mysql_fetch_assoc($get_deductions);
$totalRows_get_deductions = mysql_num_rows($get_deductions);

$temp_points = 0;

if( mysql_num_rows($get_deductions) > 0)
{
do
{
$temp_points = $temp_points + $row_get_deductions['points'];
} while($row_get_deductions = mysql_fetch_assoc($get_deductions)) ;

}

mysql_free_result($get_deductions);


$points[$i] = $temp_points + (($homewins[$i]+$awaywins[$i])*$for_win) + (($homedraws[$i]+$awaydraws[$i])*$for_draw) + (($homeloses[$i]+$awayloses[$i])*$for_lose);
$pld[$i] = $homewins[$i]+$homedraws[$i]+$homeloses[$i]+$awaywins[$i]+$awaydraws[$i]+$awayloses[$i];

//
//Calculates goal difference
//
$diff[$i] = ($homegoals[$i] + $awaygoals[$i]) - ($homegoalsagainst[$i] + $awaygoalsagainst[$i]);

$i++;
} while($row_get_teams = mysql_fetch_assoc($get_teams)); // End Loop and read teams in to Table.

$qty = $totalRows_get_teams;


// Sort By Points
array_multisort($points, SORT_DESC, SORT_NUMERIC, $diff, SORT_DESC, SORT_NUMERIC, $goals_for, SORT_DESC, SORT_NUMERIC, $wins, SORT_DESC, SORT_NUMERIC, $goals_against, SORT_ASC, SORT_NUMERIC, $draws, $loses, $pld, SORT_DESC, SORT_NUMERIC, $team, $homewins, $homedraws, $homeloses, $awaywins, $awaydraws, $awayloses, $homegoals, $homegoalsagainst, $awaygoals, $awaygoalsagainst);

$i=0;
do
{
echo "$team[$i]\t";
echo "$pld[$i]\t";
echo"$wins[$i]\t";
echo"$draws[$i]\t";
echo"$loses[$i]\t";
echo"$goals_for[$i]\t";
echo"$goals_against[$i]\t";
echo"$diff[$i]\t";
echo"$points[$i]";

$i++;

echo "\n";
} while($i < $qty);

?>

[stb74]

After a lot of trial and error I have gotten it working

:P

Its is working fine for the default division, but I would like to get it working for all divisions in the database.

I have added the following aroung the main code.

do
{


} while($row_divisions = mysql_fetch_assoc($divisions));

How do I increment the division.

[fenway]

"Increment the division"?

[stb74]

Don't matter anymore, after much trial an error I have worked it out.

[stb74]

After thinking I have sorted out the export issue, it works to a certain extent.

I am able to export the league tables and fixtures into a tab delimited txt file.  But when I import it into Quark Express the formatting is being lost.

I have to them open in excel and save as tab delimited txt file and this works.

Can anyone help with what I need to add to keep the formatting intact.

Cheers

[fenway]

I don't know what you mean by formatting, and I don't see how a text file can have any.

[stb74]

When I export the league data into the tab delimited text file, it works great.

This then has to be imported into quark express.

If i try to import the tab delimited text file, the tabs are gone and the text flows entry after entry.

To get it to work I have to open in excel and then save as tab delimited text, then import into quark.

It just seems that there is something missing from the export from php.

[fenway]

Sounds like the issue is with quark... any program should be able to handle tab-separated input.

[stb74]

I believe it is an issue with quark as it works fine  in version 7 of quark.

But the version i need it to export to is old.  If it can't be done then its fine.  But if i resave it from excel it will read it ok.

Thanks

[fenway]

Well, then go via excel... it's not like you'll need to do this daily.

[stb74]

Thats what I will be doing, I only have to do this once a week but have to do 60.

When I open the scores sheet in excel it formats some of the damn scores as dates.

[fenway]

Well, that's just excel being dumb... you have to explicitly import the fields as "text", not "general".  As I said above, this step should be unnecessary, but I don't know why quark is being dumb either.

[stb74]

I know,  I was quite chuffed that I got he export working.  And it looks grand to me.  Unfortunately the quark is really old and it would be too much to upgrade.

I will just have to go with what I have and hope something will come up in the future.

Thanks for the input.