How to export filtered results of a query to excel

Topics: User Forum
Jun 10, 2013 at 6:01 AM
Edited Jun 10, 2013 at 10:19 AM
Hi all,

I have a page with a form to filter results. I have put an export button on the page. I want my users to be able to filter data and export only the results of that query (not the whole data in table) to excel.

Of course I made a lot of searches here. And I guess I found some articles but in most of them the user ends the topic with "Ok, it's working now". I couldn't find a topic with a complete solution for three days. That's why I decided to open a thread.

I'm waiting for your help.

__Edit: Should I have session or cookies in dreamweaver and include this (though I don't know how to create)?
It would have been great if there were a how to page explaining with do this first, then that and that.__
Jun 10, 2013 at 12:09 PM
You can find a simplistic example here: https://phpexcel.codeplex.com/discussions/446306#post1054008
Write a complete table or only the result of a filter is exactly the same for PHPExcel, the difference is upstream on the definition of the query.
To build it, you exploited the form data in the same way that you would do to display the results in the browser (without take care of pagination but on the other hand ensuring that criteria will not give an empty result - except in special cases, no utility to generate an Excel file - or too therefore result - you determine the acceptable volume).

Use a cookie... Yes, if the generation is likely to take some time, you can see here: https://phpexcel.codeplex.com/discussions/429094
for a discussion on this topic, but see it after the main works.
Jun 10, 2013 at 12:26 PM
Thanks for reply. In fact I've already tried the simplistic example but it gave error on Line18 and I couldn't find what to do.

I checked the other link, pheeew. It seems I'm down at the mountain while you're at the top.

I shouldn't take your precious time I think. Thanks anyway.
Jun 10, 2013 at 12:33 PM
I added $host='localhost'; to the beginnig and changed ['Field2')) to that ['Field2']) the error gone but the result.xls came blank.
Jun 10, 2013 at 12:40 PM
Ok, I changed Field1 and 2 to the related table column names I got it listed. That's great. Now I can play with it like adding some formattings.

But the biggest problem still continues. How can export the filtered data instead of getting all. Now I'll check the second link again and try to understand. I hope I can do it.
Jun 10, 2013 at 3:52 PM
Edited Jun 10, 2013 at 3:59 PM
A little too fast in writing... Corrected.

There actually is no restrictive clause in this query. Just add it, a simple matter... or not, depending on the case.
As you give no details, I'll have to invent :-)
Say that users can select a year and that they can indicate the first letters of what they seek.
You will retrieve this information probably in $_POST:
$CurY = date('Y');
If(array_key_exists('Year', $_POST)) $Year = (int)$ _POST['Year']; else $Year = $CurY;
if($Year<1980 or $Year>$CurY) $Year = $CurY;
If(array_key_exists('Name', $_POST)) $Name = trim(strip_tags($_POST['Name'])); else $Name = '';
You have your criteria in variables, with a basic cleaning. It remains only to build your query by integrating:
$SQL = 'SELECT TheName, aField, AnotherField FROM MyTable WHERE EXTRACT(YEAR FROM TheDate) ='. $Year.' AND TheName LIKE \''.mysqli_real_escape_string($Link, $Name).'%\') LIMIT 100';
$Year is an integer, so you can use it directly. $Name, you do not know what he has, as a result, it is be sure to neutralize what may be harmful.
The LIMIT clause avoids having too much data, especially if $Name is empty. You adapt to the need. but do not delete unless certain that the table will never contain a too large number of records.
The suite is identical. The recordset is reduced according to the criteria of users, but this does not change anything for PHPExcel.
Jun 10, 2013 at 5:08 PM
Edited Jun 27, 2013 at 10:05 AM
LWol, thanks for your blind help. I couldn't decide what detail I should give. So let me try to explain in detail now.

On localhost I prepared a page with a form on the top that the user can filter data on the recordset below.
For now the user can query a date-range on "satis_tarih" (date of sale) column and chooses the "satis_satici" (salesperson) from a dropdown list. (But I'll add some other fields to query later.)
The recordset gets data from one table only and the result is shown 10 by 10 (repeat region behaviour). You can check the code and preview that page from those links:

CODE: Edit: Link is deleted for security (Did I say security? :))
PREV: Edit: Link is deleted for security (Did I say security? :))

The query is working just as I wanted for now and I want my users to get the filtered data when they click the "Export" button below.

I hope I managed to tell in details because as I told before I'm a baby in dynamic tables and PHP stuff. So I get dizzy when I read your replies. A full page example would be really good indeed.

Edit: I get the results with $_GET
Jun 11, 2013 at 11:27 AM
I would see if I have the time and if it will be necessary to make a more elaborate response.

In the meantime:
  • You already have your procedure to create the query based on user's choice (lines 56, 57).
  • You have a small form for the Export button (lines 316-318).
    At the time where you generate your page, you know the user's choice, you can therefore add to the form three hidden fields whose values are the user's choice.
The target page will receive these values that you can use in a manner similar to that you put in work to display data, in other words the same query except that the clause LIMIT will be set arbitrarily to avoid abuse and not to respect pagination.

You can provide a little javascript test in the form of Export, if the user clicks on the button with no criteria filled. This same test is to be performed on the target script (that generates your Excel file), in case the user's browser does not have javascript enabled or bypasses this test.
Jun 11, 2013 at 1:22 PM
Thank you LWol. I hope you have time for that. Meanwhile I'll try your suggestions.
Jun 14, 2013 at 10:14 AM
Edited Jun 14, 2013 at 10:36 AM
Hi Lwol,

I'm still fighting with the codes, checking the other topics to see if I can find something, opening my mind wide but unfortunately it seems that my mind is not wide enough to understand these stuff. I just created the hidden fields like that and don't know what to write in "benim4.php" :
<form action="Examples/benim4.php" method="post" name="export" target="_blank">
<input type="hidden" name="var1"
 value="<?php echo $_POST['var1']; ?>">
<input type="hidden" name="var2"
 value="<?php echo $_POST['var2']; ?>">
<input type="hidden" name="satici"
 value="<?php echo $_POST['satici']; ?>">
<button type="submit" class="btn btn-info" style="margin:10;">EXPORT</button>
</form>
And the query I have is:
SELECT *
FROM satislar
WHERE satis_satici LIKE v3 AND satis_tarih between v1 and v2
ORDER BY satis_tarih DESC
Jun 15, 2013 at 2:40 PM
Dear LWol,

I'm still waiting for your help. Please see me.
Jun 16, 2013 at 2:48 PM
Let's make it simple.

Replace the <form>...</form> with this :
<?php
if (PHP_VERSION < 6) {
    $var1 = get_magic_quotes_gpc() ? stripslashes($v1_Recordset2) : $v1_Recordset2;
    $var2 = get_magic_quotes_gpc() ? stripslashes($v2_Recordset2) : $v2_Recordset2;
    $var3 = get_magic_quotes_gpc() ? stripslashes($v3_Recordset2) : $v3_Recordset2;
  }else{
    $var1=$v1_Recordset2;
    $var2=$v2_Recordset2;
    $var3=$v3_Recordset2;
  }
  $TheForm='<form action="Examples/benim4.php" method="post" name="export" target="_blank">
            <input type="hidden" name="var1"
             value="'.htmlspecialchars($var1).'">
            <input type="hidden" name="var2"
             value="'.htmlspecialchars($var2).'">
            <input type="hidden" name="satici"
             value="'.htmlspecialchars($var3).'">
            <button type="submit" class="btn btn-info" style="margin:10;">EXPORT</button>
            </form>';
    echo $TheForm;
?>
Note : The attribute "action" in the form, in the file C74tables.php target the script Examples/benim4.php then the script generating the Excel file seems to be called exportXLS.php.
Call it as you want, but the name must be identical (script name, with path if needed, in "action" and the file on the server).

In exportXLS.php :
Add the block (from C74tables.php) : if (!function_exists("GetSQLValueString")) {[...]}}
Add this, before the query definition :
$v1_Recordset2 = "1";
if (isset($_POST['var1'])) {
  $v1_Recordset2 = $_POST['var1'];
}
$v3_Recordset2 = "1";
if (isset($_POST['satici'])) {
  $v3_Recordset2 = $_POST['satici'];
}
$v2_Recordset2 = "1";
if (isset($_POST['var2'])) {
  $v2_Recordset2 = $_POST['var2'];
}
Replace the line $SQL=[...] with this :
$SQL = sprintf("SELECT cus_id, mus_musteriadi, mus_pstnno, urun_satilanurun, satis_satici, satis_tarih  FROM satislar WHERE satis_satici LIKE %s AND satis_tarih between %s and %s ORDER BY satis_tarih DESC LIMIT 500", GetSQLValueString($v3_Recordset2, "text"),GetSQLValueString($v1_Recordset2, "date"),GetSQLValueString($v2_Recordset2, "date"));
Clause LIMIT 500 is arbitrary, replaces the number by a value normal use shall not exceed.
Jun 16, 2013 at 3:23 PM
HURRAY, HURRAY, HURRAY.

Where have you been LWol? I was about to die because of waiting in front the computer without sleeping and eating :) I could never solve that.

It's working now.

May God be with you at all times.

I think I can develop the query with more fields now.

There is another thing I struggle with and couldn't find a proper solution: The Date format. It is because of the cell type mechanism of Excel I guess. I added this:
$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
to make it filtered when opened but the dates in excel aren't listed properly. I mean they are not grouped as usual. So the user can't filter the content by year or month (example: choosing 2012 or January and February)

I tried that:
$objPHPExcel->getActiveSheet()->getStyle('F2')->getNumberFormat()->setFormatCode('dd.mm.yyyy');
but didn't work. But even if it worked it would be applied to some cells not the whole F column.

PS: Can I donate you a little if you don't mind?
Jun 16, 2013 at 6:13 PM
Yes, you are right, it must take into account the way that Excel handles dates.
What you have achieved giving a format ([...]setFormatCode ('dd.mm.yyyy')) is a correct step.
Now, the value of the cell must be a number (displayed as a date with the format) , not a string. (Today is 41441)

I guess the date from the database is to the universal format (YYYY-MM-DD) and that there are no hours.

In the loop (while), before filling the cell concerned, insert this code:
if($Trs['DateField']==''){
        $TheDate='';
    }else{
        $tmpDate=explode('-', $Trs['DateField']);
        $TheDate=PHPExcel_Shared_Date::FormattedPHPToExcel((int)$tmpDate[0],(int)$tmpDate[1],(int)$tmpDate[2]);
    }
'DateField' is the name of the field that contains a date, to be replaced by the real name, of course.
Jun 16, 2013 at 6:29 PM
In case I may be doing something wrong here I'd like to ask:

With your code it should be like that right?
while($Trs=mysqli_fetch_assoc($rs)){//extract each record
    $F->setCellValue('A'.$Line, $Trs['cus_id'])
        ->setCellValue('B'.$Line, $Trs['mus_musteriadi'])
        ->setCellValue('C'.$Line, $Trs['mus_pstnno'])
        ->setCellValue('D'.$Line, $Trs['urun_satilanurun'])
        ->setCellValue('E'.$Line, $Trs['satis_satici'])
        ->setCellValue('F'.$Line, $Trs['satis_tarih']);
    ++$Line;
    if($Trs['satis_tarih']==''){
        $TheDate='';
    }else{
        $tmpDate=explode('-', $Trs['satis_tarih']);
        $TheDate=PHPExcel_Shared_Date::FormattedPHPToExcel((int)$tmpDate[0],(int)$tmpDate[1],(int)$tmpDate[2]);
    }
}
satis_tarih is the field's name in the database table

}
Jun 16, 2013 at 8:32 PM
This way.
 while($Trs=mysqli_fetch_assoc($rs)){//extract each record
 if($Trs['satis_tarih']==''){
         $TheDate='';
     }else{
         $tmpDate=explode('-', $Trs['satis_tarih']);
         $TheDate=PHPExcel_Shared_Date::FormattedPHPToExcel((int)$tmpDate[0],(int)$tmpDate[1],(int)$tmpDate[2]);
     }
     $F->setCellValue('A'.$Line, $Trs['cus_id'])
         ->setCellValue('B'.$Line, $Trs['mus_musteriadi'])
        ->setCellValue('C'.$Line, $Trs['mus_pstnno'])
        ->setCellValue('D'.$Line, $Trs['urun_satilanurun'])
        ->setCellValue('E'.$Line, $Trs['satis_satici'])
        ->setCellValue('F'.$Line, $TheDate);
     ++$Line;
    
 }
Jun 17, 2013 at 12:38 PM
Cool and works just as I wanted but I have to give a range I guess like F2:F5. It wouldn't be problem if I didn't delete the LIMIT in the query as the query sometimes can be a whole year or two years range.

I tried to give F2:F, it gave error. I tried F2:F60000 but then the size and loading time got bigger. Can I apply that to whole column F without giving a range?
Jun 17, 2013 at 3:12 PM
As I wrote, you adapted the limit necessary: If a normal use can produce 5000 records, you fixed the limit to 5000 (or slightly more) the purpose is to avoid that a jokester arranges to have a full table dump so that you do not want.
If this is not a problem, you delete the limit, but in this case, I strongly advise you to test an extreme case to make sure that the configuration accepts it.

For the range, you do you ask not the right question... Can you know the last line? Yes, it's $Line - 1 except if no record has been found.
Jun 17, 2013 at 3:31 PM
As I wrote, you adapted the limit necessary: If a normal use can produce 5000 records, you fixed the limit to 5000 (or slightly more) the purpose is to avoid that a jokester arranges to have a full table dump so that you do not want. 
If this is not a problem, you delete the limit, but in this case, I strongly advise you to test an extreme case to make sure that the configuration accepts it. 
You're right. I try with 3.800 rows of 5 columns, I haven't tried with my real data with 56.000 + rows of at least 12 columns yet. I wish it won't be useless at the end. Better to try right now before developing more.
For the range, you do you ask not the right question... Can you know the last line? Yes, it's $Line - 1 except if no record has been found.
Don't laugh at me :) but I changed to
//Setting Time Format of Column F
$objPHPExcel->getActiveSheet()
    ->getStyle('F2':.$Line -1)
    ->getNumberFormat()->setFormatCode('dd.mm.yyyy');
Of course it failed. It may involve writing a function but you spared a lot of time for me already and helped me unbelieveably. So I don't want to bother you more.
Coordinator
Jun 17, 2013 at 3:41 PM
Edited Jun 17, 2013 at 4:24 PM
The colon range separator needs to be part of the string, it isn't a PHP operator
$objPHPExcel->getActiveSheet()
    ->getStyle('F2:F' . ($Line -1))
    ->getNumberFormat()->setFormatCode('dd.mm.yyyy');
Jun 17, 2013 at 3:48 PM
So do I have to use LIMIT in query and stick with the previous code:

->getStyle('F2:F500')

I was asking that because the export depends on a user's date-range and seller query. That's why the resulting excel may have 5 rows onlu but in another may be over a thousand.
Jun 17, 2013 at 4:05 PM
Look at the response by Mark - I hesitate to write that he could miss a 'F':...->getStyle('F2:F'. ($Line-1)) ...

For the limit... A normal use can produce 56k rows ? Ok, fix the limit to 60k. This limit is for safety not for bothering a normal user.

With this number of rows, you must try on real case (and add a margin).
Jun 17, 2013 at 4:12 PM
Yes, I tried Mark's code and it didn't work. As I can understand from his first words "colon ange separatoe" he should have written it quite quickly. Anyway, I'd like to thank Mark.

Now it's OK.

LWol, thanks a thousand times.
Coordinator
Jun 17, 2013 at 4:12 PM
Edited Jun 17, 2013 at 4:13 PM
Yeah, sorry.... I was typing quickly as I was on the phone to a recruitment agency
fixed now
Jun 21, 2013 at 10:42 AM
Edited Jun 27, 2013 at 10:04 AM
Oh no!

I did all the developments and made it work quite well on localhost but I was afraid that the system wouldn't operate on real host.

And now the fear came true. I uploaded everything to the host, changed the database connection info, made the filtering and clicked the orange "Rapor Oluştur" button. It operates and brings up the download quite fast because the downloaded excel file is empty. The titles and others are there but there are no values from the database.

The strange thing is I can still have it on localhost with no change.

It seems to me that the cause of the problem is that the variables don't work, so no values are exported to the script. Or maybe because of any limitation on host.

I beg you to give me a last hand?

THE LINK: Edit: Link is deleted for security (Did I say security? :))

THE FINAL CODE OF THE FIRST PAGE: Edit: Link is deleted for security (Did I say security? :))

THE FINAL CODE OF THE EXPORT PAGE: Edit: Link is deleted for security (Did I say security? :))

PS: There is another green button next to it which is expected to download the whole data without filtering and also works well on localhost. But in real world when I click it gives a "benim2_Tum_Karakterproblemli.php" not found error.

EDIT: I thought enabling get_magic_quotes_gpc could solve the problem. I contacted my host-provider. They did it but it is still the same.
Jun 21, 2013 at 12:27 PM
Edited Jun 21, 2013 at 12:28 PM
I realized LWol used "if (PHP_VERSION < 6)".

I think my host uses version 5.x.

Edit: Misunderstanding. The code says below 6.
Jun 21, 2013 at 12:42 PM
I would go for a problem with the DBMS, directly or indirectly.
In your benim6 page you use both MySQL and MySQLi interfaces. Choose one and corrected accordingly.
If it still does not work, display (or writes to a file) your final request (when variables replacements are made).
Is this correct?
When you have executed the query, you get a resource or false? If false, that says mysql_error (mysqli_error)?

magic_quotes : the test exists because the function don't exists in PHP 6. You can't enable get_magic_quotes_gpc (well, a provider can disable the function, but none do it) but you can have a host with magic_quotes_gpc disabled (better), depending on the version of php, you can or you can't enable it.
Jun 21, 2013 at 1:01 PM
Edited Jun 27, 2013 at 10:06 AM
Let me write what I tried:

1- I gave a temporary name to the table on real host and imported the table on localhost. It didn't work.

2- I changed all mysql to mysqli. It didn't work.

3- I changed all mysqli to mysql. It didn't work, either.

And yes. The first query page works good and gives me the results.
Jun 21, 2013 at 1:11 PM
Jun 21, 2013 at 2:00 PM
Hold on. If I understand you have imported the real table in your development server and it fails to give you a result?

In these cases, the question remains: mysql_query returns a resource or false? If it is false, it is the error (mysql_error)?
Jun 21, 2013 at 2:16 PM
Not exactly. On localhost I was working on a database table called "satislar" you know. In fact on real host there is a also a table with the same name that the users add new records daily. The table I was working on localhost was a former copy of that.

That's why I just uploaded everything in the folder I have on localhost to server and changed database connection info on supr.php which was used by the first query page and on benim.php which was used to export to Excel.

When I saw it didn't work, I started to make some trials. One of them was changing the table on the server with the table on localhost but I saw it didn't work again and reverted the tables back.

As I wanted to show you the results in pictures of localhost and real server, there is not an error message. The Excel file is prepared and downloaded but there aren't any values from the database.
Jun 21, 2013 at 2:22 PM
Sorry, I think I couldn't understand your question properly.

Asking "mysql_query returns a resource or false?" I understood that you're asking about the first page (Filtering page). Now I see that you are asking the Excel file. The answer is no, it doesn't include resource from the first page. Just empty.
Jun 21, 2013 at 3:06 PM
I will try to be precise and clear.
(Temporarily) change the benim6 script:
-Add a $SQL echo just before calling mysql_query.
-Change: $rs = mysql_query($SQL); in $rs = mysql_query($SQL) or die (mysql_error ());
(if you use MySQLi, adapted accordingly).

Run your main script (C74tables_extended), valid selection that produced you the display of records then click on the Export button.

You have no Excel file (normal), but it must show you the query. Is 'clean'?
The script stops on the die? In this case, you must have a SQL error message.
Jun 21, 2013 at 3:22 PM
I changed $rs=mysql_query($Link, $SQL); to $rs=mysql_query($Link, $SQL)or die (mysql_error ());. No change.

By saying "Add a $SQL echo" do you mean I should add something before the line starting with $rs ?

I'm going crazy. That's the final thing I'll do and I'm (we're) at the end of a month time job but...
Jun 21, 2013 at 4:16 PM
No change ? ok. It's an answer.

Yes,, add before $rs=...
Jun 21, 2013 at 7:44 PM
But they are already successive if I understand you correctly.
//your request
$SQL = sprintf("SELECT cus_id, 
                        mus_musteriadi, 
                        mus_pstnno, 
                        urun_satilanurun, 
                        satis_satici, 
                        satis_tarih,
                        urun_pstnadslhizturu,
                        satis_verilentarifepstn,
                        satis_verilentarifeadsl,
                        satis_kampanya,
                        satis_modem,
                        akt_aktalan,
                        akt_aktdurum,
                        akt_ttonayi,
                        akt_aktnotu
                        FROM satislar WHERE satis_satici LIKE %s 
                                        AND urun_satilanurun LIKE %s 
                                        AND urun_pstnadslhizturu LIKE %s 
                                        AND akt_aktalan LIKE %s 
                                        AND satis_modem LIKE %s 
                                        AND akt_aktdurum LIKE %s 
                                        AND satis_tarih between %s and %s ORDER BY satis_tarih DESC ", GetSQLValueString($v3_Recordset2, "text"), GetSQLValueString($v4_Recordset2, "text"), GetSQLValueString($v5_Recordset2, "text"), GetSQLValueString($v6_Recordset2, "text"), GetSQLValueString($v7_Recordset2, "text"), GetSQLValueString($v8_Recordset2, "text"), GetSQLValueString($v1_Recordset2, "date"),GetSQLValueString($v2_Recordset2, "date"));
$rs=mysql_query($Link, $SQL)or die (mysql_error ());//get the result (ressource)
Jun 21, 2013 at 7:50 PM
My host is served with PHP 5.2.17 if that helps you.
Jun 21, 2013 at 8:24 PM
Edited Jun 21, 2013 at 8:29 PM
There's no SQL error. But the parameters are correct? Show query will see. Eventually the request can be copied in a SQL client to check the product result.
There is a problem whose cause is not obvious: we must explore ways to find.

Edit : Knowing the version is always helpful, even it does not trigger the inspiration
Jun 21, 2013 at 8:42 PM
I just sent the server requirements to the support service and they replied me that all 4 already exist in their shared hosting plan.

» PHP version 5.2.0 or newer
» PHP extension php_zip enabled *)
» PHP extension php_xml enabled
» PHP extension php_gd2 enabled (if not compiled in)

LWol, as I did before I'm waiting in front of the computer praying you will be able to finish this project. I wish you find.
Jun 21, 2013 at 8:54 PM
It would be nice to find, indeed.
That give the echo of $SQL? And if no visible abnormality at the first glance, copy there in a MySQL client to verify that you have well the result expected.
Jun 21, 2013 at 9:10 PM
Sorry, I couldn't understand. Did you ask me to do anything?

Meanwhile I tried to make it saved in xls instead of xlsx (Excel5) but the result is the same. BLANK.
Jun 21, 2013 at 9:20 PM
Please, add a line echo $SQL; before $rs=mysql_query[...] and after $SQL=sprintf([...]
Jun 21, 2013 at 9:28 PM
I did that:
$SQL = sprintf("SELECT cus_id, 
                        mus_musteriadi, 
                        mus_pstnno, 
                        urun_satilanurun, 
                        satis_satici, 
                        satis_tarih,
                        urun_pstnadslhizturu,
                        satis_verilentarifepstn,
                        satis_verilentarifeadsl,
                        satis_kampanya,
                        satis_modem,
                        akt_aktalan,
                        akt_aktdurum,
                        akt_ttonayi,
                        akt_aktnotu
                        FROM satislar WHERE satis_satici LIKE %s 
                                        AND urun_satilanurun LIKE %s 
                                        AND urun_pstnadslhizturu LIKE %s 
                                        AND akt_aktalan LIKE %s 
                                        AND satis_modem LIKE %s 
                                        AND akt_aktdurum LIKE %s 
                                        AND satis_tarih between %s and %s ORDER BY satis_tarih DESC ", GetSQLValueString($v3_Recordset2, "text"), GetSQLValueString($v4_Recordset2, "text"), GetSQLValueString($v5_Recordset2, "text"), GetSQLValueString($v6_Recordset2, "text"), GetSQLValueString($v7_Recordset2, "text"), GetSQLValueString($v8_Recordset2, "text"), GetSQLValueString($v1_Recordset2, "date"),GetSQLValueString($v2_Recordset2, "date"));
echo $SQL;
$rs=mysqli_query($Link, $SQL)or die (mysqli_error ());//get the result (ressource)
The difference is it was coming with Excel's message box asking whether to save or not and when I clicked Yes it was opening the file with no values except the header titles.

Now there comes a message box with just one option saying cannot open the file.
Jun 21, 2013 at 9:35 PM
The inability to open is normal. Saves the file and open it with a text editor: at the beginning you must see the query.
(I would have looked well, but it tells me that it cannot find the benim6 page)
Jun 21, 2013 at 9:38 PM
It is because I keep on playing with the file trying "PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);" and other stuff. It should work now. I mean can be downloaded at least.

Ok, let me open it with text editor.
Jun 21, 2013 at 9:42 PM
Yes, but characters after the query are corrupted.
SELECT cus_id, 
                        mus_musteriadi, 
                        mus_pstnno, 
                        urun_satilanurun, 
                        satis_satici, 
                        satis_tarih,
                        urun_pstnadslhizturu,
                        satis_verilentarifepstn,
                        satis_verilentarifeadsl,
                        satis_kampanya,
                        satis_modem,
                        akt_aktalan,
                        akt_aktdurum,
                        akt_ttonayi,
                        akt_aktnotu
                        FROM satislar WHERE satis_satici LIKE NULL 
                                        AND urun_satilanurun LIKE NULL 
                                        AND urun_pstnadslhizturu LIKE NULL 
                                        AND akt_aktalan LIKE NULL 
                                        AND satis_modem LIKE NULL 
                                        AND akt_aktdurum LIKE NULL 
                                        AND satis_tarih between NULL and NULL ORDER BY satis_tarih DESC
Jun 21, 2013 at 9:43 PM
Ok. Something is wrong with parameters : All are NULL in the query..
Jun 21, 2013 at 9:45 PM
But then how do they work quite good on localhost?
Jun 21, 2013 at 9:55 PM
Forgive me to stating an obvious fact: something is different between your test server and your production server.

Added a print_r($_POST) that we check that we have the data at this location, then one or two var_dump on the variables ($ v1_Recordset2, $v2_Recordset2, for example) themselves.
If everything is good up there, the culprit will be the function GetSQLValueString
Jun 21, 2013 at 10:07 PM
I saw the expression var_dump in my google checks some hours ago (how to test if a variable works). In fact I built a "tester.php" for that and directed the form to it.
<?php

$var = '';

// This will evaluate to TRUE so the text will be printed.
if (isset($var1)) {
    echo "This var is set so I will print.";
}

// In the next examples we'll use var_dump to output
// the return value of isset().

$a = "var1";
$b = "satici";

var_dump(isset($a));      // TRUE
var_dump(isset($a, $b)); // TRUE

unset ($a);

var_dump(isset($a));     // FALSE
var_dump(isset($a, $b)); // FALSE

$foo = NULL;
var_dump(isset($foo));   // FALSE

?>
When I set this:
// This will evaluate to TRUE so the text will be printed.
if (isset($var1)) {
    echo "This var is set so I will print.";
}
to "var" it printed the sentence but when I set it to var1 and then satici, it gave nothing.

But of course I didn't know about that, I couldn't be sure what that means.
Jun 21, 2013 at 10:34 PM
I write you an explanation and meanwhile, you put:
print_r($_POST);
var_dump($v1_Recordset2, $v2_Recordset2);
before $SQL = sprintf([...]
OK?
Jun 21, 2013 at 10:35 PM
OK.
Jun 21, 2013 at 10:40 PM
That's the result without echo $SQL;
Array
(
    [var1] => 2013-06-01
    [var2] => 2013-06-03
    [satici] => %
    [urunler] => %
    [pstnadslhizmetturu] => %
    [aktalan] => %
    [modem] => %
    [aktdurum] => %
)
string(10) "2013-06-01"
string(10) "2013-06-03"
And that's the result with echo $SQL;
Array
(
    [var1] => 2013-06-01
    [var2] => 2013-06-03
    [satici] => %
    [urunler] => %
    [pstnadslhizmetturu] => %
    [aktalan] => %
    [modem] => %
    [aktdurum] => %
)
string(10) "2013-06-01"
string(10) "2013-06-03"
SELECT cus_id, 
                        mus_musteriadi, 
                        mus_pstnno, 
                        urun_satilanurun, 
                        satis_satici, 
                        satis_tarih,
                        urun_pstnadslhizturu,
                        satis_verilentarifepstn,
                        satis_verilentarifeadsl,
                        satis_kampanya,
                        satis_modem,
                        akt_aktalan,
                        akt_aktdurum,
                        akt_ttonayi,
                        akt_aktnotu
                        FROM satislar WHERE satis_satici LIKE NULL 
                                        AND urun_satilanurun LIKE NULL 
                                        AND urun_pstnadslhizturu LIKE NULL 
                                        AND akt_aktalan LIKE NULL 
                                        AND satis_modem LIKE NULL 
                                        AND akt_aktdurum LIKE NULL 
                                        AND satis_tarih between NULL and NULL ORDER BY satis_tarih DESC
Jun 21, 2013 at 10:53 PM
Edited Jun 21, 2013 at 11:13 PM
Your test script shows mainly operation of isset.
isset tests whether a variable is set and just a wee bit its contents.

And you defined a variable by:
$MaVariable = "Something";

The only other case where isset returns false while the variable is set, it is when the value of the variable is null (or NULL, but not "NULL" or \0).

var_dump displays the contents of a variable, its type, its structure.
print_r deals only arrays, displays only the keys, values, but not their type.
You use one or the other as needed.
Jun 21, 2013 at 10:59 PM
Thank you and instead of a whole new php file you finished it with just two lines.

But I guess the result shows us that the variables are OK, right? So is the problem because that the function GetSQLValueString not able to pass them to the query?
Jun 21, 2013 at 11:11 PM
Edited Jun 21, 2013 at 11:12 PM
Ok. It is closer to the culprit.
It is sure that the data arrives as we expect them in the script. Variables arising therefrom are correct (at least two dates).
So either sprintf is a whim (unlikely), or function GetSQLValueString.
It is getting late, we are not going to the debugger, it will make in the brutal.

We have text and dates, both define the same way.
Replaces them:
GetSQLValueString($v3_Recordset2, "text")

by
'\''.mysql_real_escape_string($v3_Recordset2, $Link).'\''

Ditto for the $v1 to $v8.
If you use mysqli, caution, invert the variables (first the $Link, then the string).
If it works, it will take a little narrow when even, but we will see it.
Jun 21, 2013 at 11:18 PM
Yes, it is 2 a.m. here and I can't keep my eyes open even to read what you write. Although I die for the result, I have to try it after a sleep.

Thanks a million times LWol (when we finish, I'll thank you billion times :)), I'll try and write the result. I hope we get it. Bye for now.
Jun 22, 2013 at 12:32 PM
__LWOOOOOOOOL

HIP, HIP, HURRAY...

IT WORKS, IT WORKS, IT WORKS.

Thanks X 1,000,000,000, LWol...__

Just one tiny thing left though. I've built the script to create a two-sheets excel file. A sheet for main page which lists the user's filter criteria and a second sheet for the actual details.

The second sheet is built up good now but on the main sheet we still have NULL

PIC: https://dl.dropboxusercontent.com/u/5326510/null.jpg

It is because we haven't changed that part:
$objPHPExcel->getActiveSheet()->setCellValue('C4', GetSQLValueString($v1_Recordset2, "date"));
$objPHPExcel->getActiveSheet()->setCellValue('C5', GetSQLValueString($v2_Recordset2, "date"));
$objPHPExcel->getActiveSheet()->setCellValue('B6', 'Seçilen Satıcı:');
$objPHPExcel->getActiveSheet()->setCellValue('C6', GetSQLValueString($v3_Recordset2, "text"));
$objPHPExcel->getActiveSheet()->setCellValue('B7', 'Seçilen Ürün:');
$objPHPExcel->getActiveSheet()->setCellValue('C7', GetSQLValueString($v4_Recordset2, "text"));
$objPHPExcel->getActiveSheet()->setCellValue('B8', 'Seçilen Hizmet Türü:');
$objPHPExcel->getActiveSheet()->setCellValue('C8', GetSQLValueString($v5_Recordset2, "text"));
$objPHPExcel->getActiveSheet()->setCellValue('B9', 'Seçilen Aktivasyoncu:');
$objPHPExcel->getActiveSheet()->setCellValue('C9', GetSQLValueString($v6_Recordset2, "text"));
$objPHPExcel->getActiveSheet()->setCellValue('B10', 'Seçilen Modem Durumu:');
$objPHPExcel->getActiveSheet()->setCellValue('C10', GetSQLValueString($v7_Recordset2, "text"));
$objPHPExcel->getActiveSheet()->setCellValue('B11', 'Seçilen Akt Durumu:');
$objPHPExcel->getActiveSheet()->setCellValue('C11', GetSQLValueString($v8_Recordset2, "text"));
$objPHPExcel->getActiveSheet()->getStyle('B4:B11')->getFont()->setBold(true);
Of course I played with it but again of course I couldn't make it.
Jun 22, 2013 at 3:39 PM
Not going too fast. We have bypassed the function for us make sure that the problem came from it, but we have not realized fully what she does.
Therefore, we do a complete rewrite, either we find what is wrong.

Your production server does not display errors (which is normal), but he must write down them somewhere.
Accessing the error file can save time. To find out where it is... The simplest: you created in the same directory as your file benim6 a small file containing <?php phpinfo();?>
You access this file using your browser, it should show you lots of information. Audited as log_errors to On (column Local Value), notes the value of error_reporting, viewing the path indicated by error_log. Can you access this file? Contain information in relation to our problematic script?

Otherwise, you must dig: display the value at the beginning of the function, after the stripslashes after mysql_real_escape_string.

Note: I don't know if you're using MySQL or MySQLi, but the transition from one to the other without precaution can cause problems, so the definition of the character set:
mysql_set_charset ('utf8', $Link);
mysqli_set_charset ($Link, 'utf8');
Jun 22, 2013 at 3:54 PM
Edited Jun 27, 2013 at 10:02 AM
Pardon me because of being quite excited.

I did what you said but I'm scared of that big info in front of me. So I downloaded it as html file:
Edit: Link is deleted for security (Did I say security? :))

I changed all mysql to mysqli.
Jun 22, 2013 at 6:37 PM
OK, no file.

Do simple.
At the beginning of your benim6 script, add:
error_reporting(E_ALL);
ini_set('display_errors', '1');

Run your script and see what happens. If the resulting Excel file does not open, same technique as last time: open it with a text editor. There is probably an error that trolling... And that interests us.

PS: Removes the export of the phpinfo.
Jun 26, 2013 at 9:32 AM
Hi LWol,

Sorry for replying late. My 4-year-old daughter caught pneumonia. We were in the hospital. Now I'm back after an exhausting 3 days.

I tried your suggestions and in the resulting excel file it says:
<br />
<b>Notice</b>:  Use of undefined constant Calibri - assumed 'Calibri' in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>145</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
<br />
<b>Warning</b>:  mysqli_real_escape_string() expects exactly 2 parameters, 1 given in <b>/var/www/vhosts/turktelekomksk.com/httpdocs/HARICILER/raporlamasistemi/Examples/benim6.php</b> on line <b>15</b><br />
Jun 26, 2013 at 10:06 AM
When I look into phpinfo, magic_quotes_gpc, magic_quotes_runtime and magic_quotes_sybase all look Off. Should I contact my host provider to make them On?
Jun 26, 2013 at 3:52 PM
Your little daugter has the priority, of course, no need to apologize. I hope that she will return quickly and without sequelae.

Don't requests to your provider to change the magic_quote, they are fine as they are. This does not prevent your script to take precautions and to be able to treat the two cases.

Notice about Calibri: you have a 'name' =>Calibri line.
Noted as this, php seeks a constant by that name and do not find and transformed it into a string. Should be corrected to make it a string:
'name '=>' Calibri'

Warning about mysqli_real_escape_string.
Unlike mysql_real_escape_string, it must provide the connection to the database.
mysqli_real_escape_string ($GLOBALS ['Link'], $TheValue)

Note the use of $GLOBALS, indeed the $Link variable is not accessible inside the function directly, the alternative would be to use global $Link before using it.
Jun 27, 2013 at 5:42 AM
Hi LWol,

Thank you for your wishes about my daughter.

I did what you told me to. Everything looks OK now.

And thanks a zillion times for your precious help. I guess we did it finally.

But I'm a bit sad that it's over. I'm gonna miss the excitement while waiting for your replies believing LWol (I realize that I just know your username and here is the only way I can contact you) resolve it and satisfaction after applying and seeing it works. I'll always remember what you did for me. Noone would help me that much. Take good care of yourself.

THANK YOU, THANK YOU, THANK YOU.
Jun 27, 2013 at 9:29 AM
This actually ended? :-)

Think to remove the ini_set.

No problems with an export containing a lot of data?
Jun 27, 2013 at 10:00 AM
:) I removed, or didn't I? Let me check. :) Don't call me names. :)

Of course I have problems with making another export button to download whole sales data in case if a user is not satisfied with the info they have now and want to see other columns as well. But how can I ask you to help more while you helped that much?

I'll try to get this over by omitting some columns and making more buttons to download partial queries like buttons for 2010, 2011, 2012, 2013. May look crowded but it doesn't matter. I don't want to busy you any more.

It's been a lot of thanks already but thanks gazillion times (I don't know what comes next-so don't force me) for being so helpful and offering more help.
Coordinator
Jun 27, 2013 at 10:27 AM
Thanks for your patience LWol
Jun 27, 2013 at 11:54 AM
Mark: Just a small indirect contribution to PHPExcel (which would not exist or would be not as advanced without you).

Cihat: from what you write, I guess a few points which can you push you scratching your head :
  • Integrate in the form more choices.
    -Get these particular elements and incorporate them into your query.
    -Convince PHPExcel to create a file containing all your data.
    This last point could be insurmountable unless Mark has implemented numerous solutions to clear the obstacle. An out of memory problem, reading Chapter 4.2.1 is required.
    Starts with this, because if you are not successful, it is pointless to create your form and manage settings.