Jump to content


Photo

Exporting Queries


  • Please log in to reply
20 replies to this topic

#1 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 25 October 2006 - 10:15 AM

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.

;)


nifootball.co.uk

#2 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 25 October 2006 - 10:34 AM

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

http://www.phpfreaks...rials/114/0.php

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

Thanks

nifootball.co.uk

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 October 2006 - 05:21 PM

Details like what?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 26 October 2006 - 05:25 PM

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. 
nifootball.co.uk

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 October 2006 - 05:50 PM

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. 

I'm sorry, I don't quite understand... "input to the export script"?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 26 October 2006 - 05:57 PM

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.nifootbal...apper&Itemid=65

And the fixtures.

http://www.nifootbal...pper&Itemid=239

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


nifootball.co.uk

#7 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 27 October 2006 - 10:19 AM

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.

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

nifootball.co.uk

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 03:53 PM

Sure... instead of producing HTML output, simply produce tab-dellimited output.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 30 October 2006 - 09:56 AM

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

?>
nifootball.co.uk

#10 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 30 October 2006 - 11:06 AM

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.




nifootball.co.uk

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 October 2006 - 05:08 PM

"Increment the division"?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 31 October 2006 - 04:10 PM

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


nifootball.co.uk

#13 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 01 November 2006 - 01:41 PM

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

nifootball.co.uk

#14 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 November 2006 - 08:42 PM

I don't know what you mean by formatting, and I don't see how a text file can have any.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#15 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 01 November 2006 - 09:47 PM

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.
nifootball.co.uk

#16 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 November 2006 - 11:01 PM

Sounds like the issue is with quark... any program should be able to handle tab-separated input.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#17 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 01 November 2006 - 11:14 PM

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


nifootball.co.uk

#18 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 November 2006 - 06:47 PM

Well, then go via excel... it's not like you'll need to do this daily.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#19 stb74

stb74
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationNorthern Ireland

Posted 03 November 2006 - 10:57 AM

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.


nifootball.co.uk

#20 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 November 2006 - 12:36 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users