Jump to content

Exporting Queries


stb74

Recommended Posts

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.

;)

Link to comment
Share on other sites

[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"?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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);

?>
Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.