MyBB Community Forums

MyBB Community Forums > Community > General Discussion > EXPORT SQL to Excel
Full Version: EXPORT SQL to Excel
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

Kimmo

2006-07-21, 03:34 PM
Hey,

I've created a little special tool for our internal use that allows you to choose a value from a list (populated from an extra table), this selection is then passed on to the next page and embedded in a query as a filter.

Then the results are formatted with html to present a nice looking table.
There are 3 things I would still like to do however and I can't seem to get my head around them

1) how do I limit the first form selection to a single value (do not allow CTRL+CLICK -way to select multiple values)?

2) how can I pass the variable chosen in the first page to a third page through a form on the second page. I want to have a link "click here to download the output as xls-file". I figured I could just repeat the query on the third page and use the header-thingy to output to a file...

like this:

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

However when I have the stuff there, the system gives an error "Sql error : Query was empty" presumably because it didn't carry the variable chosen on the first page to the third page...

3) I am currently using a check on 

$mybb->usergroup['field'] != "value" )
however I would like to use a check on the persons usergroup....can I just use something like this instead?

$mybb->user[groupid] != "value" )

cheers,
Kimmo

Dennis Tsang

2006-07-21, 04:16 PM
Kimmo Wrote:Hey,

I've created a little special tool for our internal use that allows you to choose a value from a list (populated from an extra table), this selection is then passed on to the next page and embedded in a query as a filter.

Then the results are formatted with html to present a nice looking table.
There are 3 things I would still like to do however and I can't seem to get my head around them

1) how do I limit the first form selection to a single value (do not allow CTRL+CLICK -way to select multiple values)?

2) how can I pass the variable chosen in the first page to a third page through a form on the second page. I want to have a link "click here to download the output as xls-file". I figured I could just repeat the query on the third page and use the header-thingy to output to a file...

like this:

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
Try something like this:
First page...
<form action="nextpage.php" method="post">
<select name="stuff">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
<option value="4">Option 4</option>
<option value="5">Option 5</option>
</select>
<input type="submit" value="To page 2!" />
</form>
Second page...
<form action="lastpage.php" method="post">
<input type="hidden" name="stuff" value="<?php echo htmlspecialchars($_POST['stuff'])?>" />
<input type="submit" value="To page 2!" />
</form>
Third page...
<?php
$stuff = $_POST['stuff'];
// Do what you want with $stuff
Kimmo Wrote:3) I am currently using a check on 

$mybb->usergroup['field'] != "value" )
however I would like to use a check on the persons usergroup....can I just use something like this instead?

$mybb->user[groupid] != "value" )

cheers,
Kimmo

You can get the current user's primary usergroup ID by checking:
$mybb->user['usergroup']
For additional group IDs, they are in a comma separated list in 
$mybb->user['additionalgroups']

Kimmo

2006-07-21, 09:43 PM
grrrr.....still keep getting "Sql error : Query was empty"

here's the code...am I missing something??

firstpage:
	$sql = mysql_query("SELECT countryName FROM mybb_Countries ORDER BY countryName");

// If there are rows, make the select box

echo "<form action=\"uwp_countryextract_file.php\" method=\"post\">";
echo "<select name=\"countrynames\">";
while($row=mysql_fetch_array($sql)) 
	{
	echo "<option value=\"".$row['countryName']."\">".$row['countryName']."</option>";
	}
echo "</select>";
echo "<input type=\"submit\" />";
echo "</form></body>";

second page:
	$sql = mysql_query("SELECT AL1.fid32, AL1.fid33, AL2.address, AL2.address2, AL2.city, AL2.state, AL2.zip, AL3.countryName, AL1.fid45, AL1.fid17, AL2.uid, AL1.fid26, AL1.fid39, AL2.email, AL2.allownotices, AL2.username, AL2.birthday FROM mybb_userfields AL1, mybb_users AL2, mybb_Countries AL3 WHERE (AL2.countryID=AL3.countryID AND AL2.uid=AL1.ufid AND AL3.countryName='".$_POST[countrynames]."')");
	$num=mysql_num_rows($sql);
	?>
<html><body>	
<h1> Data extraction for <? echo $_POST[countrynames]?>.</h1>

<form action="uwp_countryextract_file_output.php" method="post">
<input type="hidden" name="countrynames" value="<?php echo htmlspecialchars($_POST['countrynames'])?>" />
<input type="submit" value="Export!" />
</form>



<br>
<table border="1" cellspacing="2" cellpadding="2" >
<tr>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">FirstName</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">LastName</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Street1</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Street2</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">City</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">State</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">ZIP</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Country</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Hardcopy Mailing Permission</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">STATUS</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">UserID</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Relayed Messaging Permission</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Phone</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Email</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Email-permission</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Username</font></th>
<th align="left"><font size="2" face="Arial, Helvetica, sans-serif">Birthday</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$first=mysql_result($sql,$i,"AL1.fid33");
$last=mysql_result($sql,$i,"AL1.fid32");
$street1=mysql_result($sql,$i,"AL2.address");
$street2=mysql_result($sql,$i,"AL2.address2");
$city=mysql_result($sql,$i,"AL2.city");
$State=mysql_result($sql,$i,"AL2.state");
$zip=mysql_result($sql,$i,"AL2.zip");
$countryName=mysql_result($sql,$i,"AL3.countryName");
$hcpermission=mysql_result($sql,$i,"AL1.fid45");
$status=mysql_result($sql,$i,"AL1.fid17");
$userid=mysql_result($sql,$i,"AL2.uid");
$uwppermission=mysql_result($sql,$i,"AL1.fid26");
$phone=mysql_result($sql,$i,"AL1.fid39");
$email=mysql_result($sql,$i,"AL2.email");
$emailpermission=mysql_result($sql,$i,"AL2.allownotices");
$username=mysql_result($sql,$i,"AL2.username");
$birthday=mysql_result($sql,$i,"AL2.birthday");
$emailfix=str_replace("[email protected]","", $email);

?>

<tr>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $first; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $last; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $street1; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $street2; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $city; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $State; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $zip; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $countryName; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $hcpermission; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $status; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $userid; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $uwppermission; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $phone; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $emailfix; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $emailpermission; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $username; ?></font></td>
<td><font size="2" face="Arial, Helvetica, sans-serif"><? echo $birthday; ?></font></td>
</tr>

<?
$i++;
}
echo "</table>";
?>

and third page:
<?

// Connection and permission stuff goes here but deleted in this example

	$sql = mysql_query("SELECT AL1.fid32, AL1.fid33, AL2.address, AL2.address2, AL2.city, AL2.state, AL2.zip, AL3.countryName, AL1.fid45, AL1.fid17, AL2.uid, AL1.fid26, AL1.fid39, AL2.email, AL2.allownotices, AL2.username, AL2.birthday FROM mybb_userfields AL1, mybb_users AL2, mybb_Countries AL3 WHERE (AL2.countryID=AL3.countryID AND AL2.uid=AL1.ufid AND AL3.countryName='" . $_POST['countrynames'] . "')
	" );
	
	$num=mysql_num_rows($sql);

$export = mysql_query ( $extractsql ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ ) 
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) ) 
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) ) 
        {
            $value = "\t";
        } 
        else 
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );
if ( $data == "" ) 
{
    $data = "\n(0) Records Found!\n";                        
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

Sad

Kimmo

2006-07-24, 12:50 PM
uhmmm.....help Sad
MyBB Community Forums > Community > General Discussion > EXPORT SQL to Excel