Re: [PHP] Exporting large data from mysql to html using php

[Ashley Sheridan]

On Wed, 2011-11-02 at 19:47 -0400, Jason Pruim wrote:

> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> On Oct 31, 2011, at 7:52 PM, Ashley Sheridan wrote:
> 
> > On Mon, 2011-10-31 at 19:29 -0400, Jason Pruim wrote:
> >> 
> >> Jason Pruim
> >> li...@pruimphotography.com
> >> 
> >> 
> >> 
> >> On Oct 31, 2011, at 7:11 PM, Jim Lucas wrote:
> >> 
> >> > On 10/24/2011 5:50 PM, Jason Pruim wrote:
> >> >> Now that I've managed to list 3 separate programming languages and 
> >> >> somewhat tie it back into php here's the question...
> >> >> 
> >> >> I have about 89 million records in mysql... the initial load of the 
> >> >> page takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on 
> >> >> the SQL query's... But they just aren't going fast enough...
> >> >> 
> >> >> What I would like to do, is pull the data out of MySQL and store it in 
> >> >> the HTML files, and then update the HTML files once a day/week/month... 
> >> >> I can figure most of it out... BUT... How do I automatically link to 
> >> >> the individual pages?
> >> >> 
> >> >> I have the site working when you pull it from MySQL... Just the load 
> >> >> time sucks... Any suggestions on where I can pull some more info from? 
> >> >> :)
> >> >> 
> >> >> Thanks in advance!
> >> >> 
> >> >> 
> >> >> Jason Pruim
> >> >> li...@pruimphotography.com
> >> >> 
> >> > 
> >> > Jason,
> >> > 
> >> > How large a data set are you starting with?  How many records in all.
> >> > 
> >> > Will you show us your DB schema?
> >> 
> >> Hey Jim,
> >> 
> >> I am working with 89 Million records right now... Going to be expanding to 
> >> a much larger dataset as the site expands.
> >> 
> >> Here is the main table that I am using:
> >> 
> >> mysql> describe main;
> >> ++-+--+-+-++
> >> | Field  | Type| Null | Key | Default | Extra  |
> >> ++-+--+-+-++
> >> | areacode   | int(3)  | NO   | MUL | NULL||
> >> | exchange   | int(3)  | NO   | | NULL||
> >> | subscriber | char(4) | NO   | | NULL||
> >> | id | int(11) | NO   | PRI | NULL| auto_increment |
> >> | state  | varchar(20) | YES  | | NULL||
> >> | config | text| YES  | | NULL||
> >> ++-+--+-+-++
> >> 
> >> 
> >> 
> >> config is just going to contain varius settings for commenting on records, 
> >> and future expansion. State will actually be the state spelled out. 
> >> 
> >> Thanks for taking a looking!
> >> 
> >> 
> > 
> > I'd put the spelling of the state in another table and just include the 
> > reference to it in this table, it will save a lot on storage and it's easy 
> > to do a join to get it. That way, it's also much faster to look up entries 
> > by state, as a numerical index is quicker that a string index.
> > 
> > On the subject of indexes, what other ones do you have apart from the 
> > primary key there?
> > 
> > -- 
> > Thanks,
> > Ash
> > http://www.ashleysheridan.co.uk
> > 
> > 
> 
> Hey Ash,
> 
> Sorry for the delay... Had a sick kid at home which kept me very busy and 
> away from the computer!
> 
> You think putting the spelling of the state in another table with some kind 
> of a reference code (such as 1, 2, 3, 4, etc etc ) would be faster then 
> searching based on some spelling of the name? I am using the state name in 
> the URL that I'm referencing... So something like: 
> www.example.com/new-york/212/314 would bring up the entire 10,000 records for 
> 212-314- 
> 
> Trying to avoid the normal index.php?vars=blah&you=Blah type stuff...
> 
> Indexes... I'm still learning about those... I created a index based on the 
> areacode, and exchange fields... But more reading is needed to figure out 
> just how to use it properly :)
> 
> Thanks Ash!
> 
> 
> 
> 
> 


You could still search based on the state name, but you could then use a
join or first search the states table for the ids of each matching state
and use an IN clause in the query against the phone numbers.

-- 
Thanks,
Ash
http://www.ashleysheridan.co.uk

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 31, 2011, at 7:52 PM, Ashley Sheridan wrote:

> On Mon, 2011-10-31 at 19:29 -0400, Jason Pruim wrote:
>> 
>> Jason Pruim
>> li...@pruimphotography.com
>> 
>> 
>> 
>> On Oct 31, 2011, at 7:11 PM, Jim Lucas wrote:
>> 
>> > On 10/24/2011 5:50 PM, Jason Pruim wrote:
>> >> Now that I've managed to list 3 separate programming languages and 
>> >> somewhat tie it back into php here's the question...
>> >> 
>> >> I have about 89 million records in mysql... the initial load of the page 
>> >> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
>> >> query's... But they just aren't going fast enough...
>> >> 
>> >> What I would like to do, is pull the data out of MySQL and store it in 
>> >> the HTML files, and then update the HTML files once a day/week/month... I 
>> >> can figure most of it out... BUT... How do I automatically link to the 
>> >> individual pages?
>> >> 
>> >> I have the site working when you pull it from MySQL... Just the load time 
>> >> sucks... Any suggestions on where I can pull some more info from? :)
>> >> 
>> >> Thanks in advance!
>> >> 
>> >> 
>> >> Jason Pruim
>> >> li...@pruimphotography.com
>> >> 
>> > 
>> > Jason,
>> > 
>> > How large a data set are you starting with?  How many records in all.
>> > 
>> > Will you show us your DB schema?
>> 
>> Hey Jim,
>> 
>> I am working with 89 Million records right now... Going to be expanding to a 
>> much larger dataset as the site expands.
>> 
>> Here is the main table that I am using:
>> 
>> mysql> describe main;
>> ++-+--+-+-++
>> | Field  | Type| Null | Key | Default | Extra  |
>> ++-+--+-+-++
>> | areacode   | int(3)  | NO   | MUL | NULL||
>> | exchange   | int(3)  | NO   | | NULL||
>> | subscriber | char(4) | NO   | | NULL||
>> | id | int(11) | NO   | PRI | NULL| auto_increment |
>> | state  | varchar(20) | YES  | | NULL||
>> | config | text| YES  | | NULL||
>> ++-+--+-+-++
>> 
>> 
>> 
>> config is just going to contain varius settings for commenting on records, 
>> and future expansion. State will actually be the state spelled out. 
>> 
>> Thanks for taking a looking!
>> 
>> 
> 
> I'd put the spelling of the state in another table and just include the 
> reference to it in this table, it will save a lot on storage and it's easy to 
> do a join to get it. That way, it's also much faster to look up entries by 
> state, as a numerical index is quicker that a string index.
> 
> On the subject of indexes, what other ones do you have apart from the primary 
> key there?
> 
> -- 
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
> 
> 

Hey Ash,

Sorry for the delay... Had a sick kid at home which kept me very busy and away 
from the computer!

You think putting the spelling of the state in another table with some kind of 
a reference code (such as 1, 2, 3, 4, etc etc ) would be faster then searching 
based on some spelling of the name? I am using the state name in the URL that 
I'm referencing... So something like: www.example.com/new-york/212/314 would 
bring up the entire 10,000 records for 212-314- 

Trying to avoid the normal index.php?vars=blah&you=Blah type stuff...

Indexes... I'm still learning about those... I created a index based on the 
areacode, and exchange fields... But more reading is needed to figure out just 
how to use it properly :)

Thanks Ash!

Re: [PHP] Exporting large data from mysql to html using php

[Ashley Sheridan]

On Mon, 2011-10-31 at 19:29 -0400, Jason Pruim wrote:

> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> On Oct 31, 2011, at 7:11 PM, Jim Lucas wrote:
> 
> > On 10/24/2011 5:50 PM, Jason Pruim wrote:
> >> Now that I've managed to list 3 separate programming languages and 
> >> somewhat tie it back into php here's the question...
> >> 
> >> I have about 89 million records in mysql... the initial load of the page 
> >> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
> >> query's... But they just aren't going fast enough...
> >> 
> >> What I would like to do, is pull the data out of MySQL and store it in the 
> >> HTML files, and then update the HTML files once a day/week/month... I can 
> >> figure most of it out... BUT... How do I automatically link to the 
> >> individual pages?
> >> 
> >> I have the site working when you pull it from MySQL... Just the load time 
> >> sucks... Any suggestions on where I can pull some more info from? :)
> >> 
> >> Thanks in advance!
> >> 
> >> 
> >> Jason Pruim
> >> li...@pruimphotography.com
> >> 
> > 
> > Jason,
> > 
> > How large a data set are you starting with?  How many records in all.
> > 
> > Will you show us your DB schema?
> 
> Hey Jim,
> 
> I am working with 89 Million records right now... Going to be expanding to a 
> much larger dataset as the site expands.
> 
> Here is the main table that I am using:
> 
> mysql> describe main;
> ++-+--+-+-++
> | Field  | Type| Null | Key | Default | Extra  |
> ++-+--+-+-++
> | areacode   | int(3)  | NO   | MUL | NULL||
> | exchange   | int(3)  | NO   | | NULL||
> | subscriber | char(4) | NO   | | NULL||
> | id | int(11) | NO   | PRI | NULL| auto_increment |
> | state  | varchar(20) | YES  | | NULL||
> | config | text| YES  | | NULL||
> ++-+--+-+-++
> 
> 
> 
> config is just going to contain varius settings for commenting on records, 
> and future expansion. State will actually be the state spelled out. 
> 
> Thanks for taking a looking!
> 
> 


I'd put the spelling of the state in another table and just include the
reference to it in this table, it will save a lot on storage and it's
easy to do a join to get it. That way, it's also much faster to look up
entries by state, as a numerical index is quicker that a string index.

On the subject of indexes, what other ones do you have apart from the
primary key there?

-- 
Thanks,
Ash
http://www.ashleysheridan.co.uk

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 31, 2011, at 7:11 PM, Jim Lucas wrote:

> On 10/24/2011 5:50 PM, Jason Pruim wrote:
>> Now that I've managed to list 3 separate programming languages and somewhat 
>> tie it back into php here's the question...
>> 
>> I have about 89 million records in mysql... the initial load of the page 
>> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
>> query's... But they just aren't going fast enough...
>> 
>> What I would like to do, is pull the data out of MySQL and store it in the 
>> HTML files, and then update the HTML files once a day/week/month... I can 
>> figure most of it out... BUT... How do I automatically link to the 
>> individual pages?
>> 
>> I have the site working when you pull it from MySQL... Just the load time 
>> sucks... Any suggestions on where I can pull some more info from? :)
>> 
>> Thanks in advance!
>> 
>> 
>> Jason Pruim
>> li...@pruimphotography.com
>> 
> 
> Jason,
> 
> How large a data set are you starting with?  How many records in all.
> 
> Will you show us your DB schema?

Hey Jim,

I am working with 89 Million records right now... Going to be expanding to a 
much larger dataset as the site expands.

Here is the main table that I am using:

mysql> describe main;
++-+--+-+-++
| Field  | Type| Null | Key | Default | Extra  |
++-+--+-+-++
| areacode   | int(3)  | NO   | MUL | NULL||
| exchange   | int(3)  | NO   | | NULL||
| subscriber | char(4) | NO   | | NULL||
| id | int(11) | NO   | PRI | NULL| auto_increment |
| state  | varchar(20) | YES  | | NULL||
| config | text| YES  | | NULL||
++-+--+-+-++



config is just going to contain varius settings for commenting on records, and 
future expansion. State will actually be the state spelled out. 

Thanks for taking a looking!

Re: [PHP] Exporting large data from mysql to html using php

[Jim Lucas]

On 10/24/2011 5:50 PM, Jason Pruim wrote:
> Now that I've managed to list 3 separate programming languages and somewhat 
> tie it back into php here's the question...
> 
> I have about 89 million records in mysql... the initial load of the page 
> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
> query's... But they just aren't going fast enough...
> 
> What I would like to do, is pull the data out of MySQL and store it in the 
> HTML files, and then update the HTML files once a day/week/month... I can 
> figure most of it out... BUT... How do I automatically link to the individual 
> pages?
> 
> I have the site working when you pull it from MySQL... Just the load time 
> sucks... Any suggestions on where I can pull some more info from? :)
> 
> Thanks in advance!
> 
> 
> Jason Pruim
> li...@pruimphotography.com
> 

Jason,

How large a data set are you starting with?  How many records in all.

Will you show us your DB schema?

-- 
Jim Lucas

http://www.cmsws.com/
http://www.cmsws.com/examples/
http://www.bendsource.com/

C - (541) 408-5189
O - (541) 323-9113
H - (541) 323-4219

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 27, 2011, at 4:08 PM, Nathan Nobbe wrote:

> On Mon, Oct 24, 2011 at 6:50 PM, Jason Pruim  
> wrote:
> Now that I've managed to list 3 separate programming languages and somewhat 
> tie it back into php here's the question...
> 
> I have about 89 million records in mysql... the initial load of the page 
> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
> query's... But they just aren't going fast enough...
> 
> What I would like to do, is pull the data out of MySQL and store it in the 
> HTML files, and then update the HTML files once a day/week/month... I can 
> figure most of it out... BUT... How do I automatically link to the individual 
> pages?
> 
> I have the site working when you pull it from MySQL... Just the load time 
> sucks... Any suggestions on where I can pull some more info from? :)
> 
> Thanks in advance!
> 
> dial in the db schema (think keys) and queries; then investigate a reverse 
> proxy like varnish to cache the generated html.
> 
> you'll be able to handle a couple thousand requests per second against the 
> proxy in no time.
> 
> might be worth pre-generating some of the pages if they are still really slow 
> after db optimization.

Would it make sense that COUNT()'ing 89 million records would kill the 
performance? Once that was gone... It worked perfectly! 

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 27, 2011, at 2:44 PM, Tommy Pham wrote:

> On Wed, Oct 26, 2011 at 5:47 PM, Jason Pruim  
> wrote:
> 
> Jason Pruim
> li...@pruimphotography.com
> 
> The server that's running it is a home computer with a VPS installed... It's 
> not my dev environment :)
> 
> 
> Home computer used for a production environment?  Wow.. I'm speechless.

Ummm... No... Home computer for a dev environment... The final product will be 
moved to a better host. This is temporary... And actually isn't a bad idea... 
If you get it running good on a cheap home computer, why wouldn't it run 
*BETTER* on the high-end production server?

>  
> The information being searched is specifically phone numbers, and the bosses 
> want to provide the public away to browse them, hence the pagination... Once 
> I removed a COUNT from mysql it started working alot better... So I still 
> need to provide a better pagination system, but it's working.
> 
> 
> If just showing phone numbers only and no other information, what's the point 
> since there are already several other white and yellow pages on the net?  
> There's even a reverse number look-up too.  There's also a well known paid 
> service for getting a DB of contacts (name, address, phone numbers of persons 
> and businesses).  Just out of curiosity, what is it that your boss intend to 
> offer that would stand out for already existing services?  I doubt anyone 
> would be sane enough to sit there and just browse through phone numbers only. 
>  Even if it does show the owner's name of the registered number, I highly 
> doubt anyone sane would browse unless you provide some kind of filter/search.

I'm not expecting people to look at every single page... But I need away to 
allow curious people the ability to page through results if they want. 
>  
> Oh, and the 89 million is just for one state :) We are talking the 
> possibility of I believe 10 billion numbers to cover the US, not to mention 
> Canada which I believe uses the same numbering system as we do so that could 
> be another 10 billion...
> 
> >
> > As I've mentioned, something of this magnitude is better to leave it to the
> > DBA and work together with that DBA.  Either hire/contract one or become one
> > :)
> 
> I'm working on becoming one ontop of web designer and programmer :)
> 
> Good luck, that's a LOT of reading.  I'd estimate that's about 3k+ pages of 
> reading. :)

I've always been a fan of reading :) Just wish I had more time to do it! :)

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

>
> Good luck, that's a LOT of reading.  I'd estimate that's about 3k+ pages 
> of
> reading. :)
>
>
>> >
>> >
>> > Regards,
>> > Tommy
>>
>>
>

nice to see someone else is finally getting the point that I'm been making. 



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Nathan Nobbe]

On Mon, Oct 24, 2011 at 6:50 PM, Jason Pruim wrote:

> Now that I've managed to list 3 separate programming languages and somewhat
> tie it back into php here's the question...
>
> I have about 89 million records in mysql... the initial load of the page
> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL
> query's... But they just aren't going fast enough...
>
> What I would like to do, is pull the data out of MySQL and store it in the
> HTML files, and then update the HTML files once a day/week/month... I can
> figure most of it out... BUT... How do I automatically link to the
> individual pages?
>
> I have the site working when you pull it from MySQL... Just the load time
> sucks... Any suggestions on where I can pull some more info from? :)
>
> Thanks in advance!
>

dial in the db schema (think keys) and queries; then investigate a reverse
proxy like varnish to cache the generated html.

you'll be able to handle a couple thousand requests per second against the
proxy in no time.

might be worth pre-generating some of the pages if they are still really
slow after db optimization.

-nathan

Re: [PHP] Exporting large data from mysql to html using php

[Tommy Pham]

On Wed, Oct 26, 2011 at 5:47 PM, Jason Pruim wrote:

>
> Jason Pruim
> li...@pruimphotography.com
>
> The server that's running it is a home computer with a VPS installed...
> It's not my dev environment :)
>
>
Home computer used for a production environment?  Wow.. I'm speechless.


> The information being searched is specifically phone numbers, and the
> bosses want to provide the public away to browse them, hence the
> pagination... Once I removed a COUNT from mysql it started working alot
> better... So I still need to provide a better pagination system, but it's
> working.
>
>
If just showing phone numbers only and no other information, what's the
point since there are already several other white and yellow pages on the
net?  There's even a reverse number look-up too.  There's also a well known
paid service for getting a DB of contacts (name, address, phone numbers of
persons and businesses).  Just out of curiosity, what is it that your boss
intend to offer that would stand out for already existing services?  I doubt
anyone would be sane enough to sit there and just browse through phone
numbers only.  Even if it does show the owner's name of the registered
number, I highly doubt anyone sane would browse unless you provide some kind
of filter/search.


> Oh, and the 89 million is just for one state :) We are talking the
> possibility of I believe 10 billion numbers to cover the US, not to mention
> Canada which I believe uses the same numbering system as we do so that could
> be another 10 billion...
>
> >
> > As I've mentioned, something of this magnitude is better to leave it to
> the
> > DBA and work together with that DBA.  Either hire/contract one or become
> one
> > :)
>
> I'm working on becoming one ontop of web designer and programmer :)
>

Good luck, that's a LOT of reading.  I'd estimate that's about 3k+ pages of
reading. :)


> >
> >
> > Regards,
> > Tommy
>
>

Re: [PHP] Exporting large data from mysql to html using php

[David Robley]

Jim Giner wrote:

> "David Robley"  wrote in message
> news:49.50.34068.1b567...@pb1.pair.com...
>>
>> Consider running EXPLAIN on all your queries to see if there is something
>> Mysql thinks could be done to improve performance.
>>
> 
> Why do so many responders seem to think the problem here is in the
> preparation of the query?
> 
> It is the Approach that it is the problem.  It needs to re-addressed, not
> simply tinkered with.  I don't care if someone figures out how to spew out
> 89M+ records in 5 seconds flat.  What are you doing pulling up that many
> detail records at one time for? Who is going to look at them?  You?  If
> so, see my previous post on how long that is going to take you just to
> examine (!) the first million alone.
> 
> This problem needs a re-working of the data processing and user interfaces
> to allow for the selection of an appropriate amount of individual records
> in any result set, otherwise simply preparing a summary of some sort.

Jason originally said that he was using LIMIT to grab a subset of the data,
so I don't see why you would think he is trying to pull the full data set
in one hit. My response suggesting EXPLAIN was made in the knowledge that
he is using LIMIT.


Cheers
-- 
David Robley

"I was the first to climb Mount Everest," said Tom hilariously.
Today is Setting Orange, the 8th day of The Aftermath in the YOLD 3177. 


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Lester Caine]

Jim Giner wrote:

Your boss wants to give access to phone numbers to the public in general?
Then what?

Glad mine's unlisted.


Is it?
Does it start 518248 ?
I often forget to get a phone number when a parcel has to go by carrier, and 
paypal does not include that info, but only rarely does one not come up on 
google ... even if in your case I had to check the cashed copy of the the result ;)


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 26, 2011, at 9:09 PM, Jim Giner wrote:

> Your boss wants to give access to phone numbers to the public in general? 
> Then what?
> 
> Glad mine's unlisted.

There's no identifying information on the phone numbers... Simply just the 
phone number... Eventually maybe some info about who owns the phone line... And 
a few other things. 

The rest is covered under an NDA until the site goes live :) but there 
shouldn't be any privacy related issues...



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

Your boss wants to give access to phone numbers to the public in general? 
Then what?

Glad mine's unlisted.



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 26, 2011, at 6:23 AM, Tommy Pham wrote:

> On Wed, Oct 26, 2011 at 1:40 AM, Lester Caine  wrote:
> 
>> Tommy Pham wrote:
>> 
>>> I wonder ... The real question is what's the purpose of the DB?  Is it for
>>> OLAP
>>> or OLTP? ;)
>>> As for dealing with DB having millions of rows, you're crossing over into
>>> DBA area.
>>> 
>> 
>> Many of my customers have coming up on 20 years of data available. There
>> has been a debate on transferring historic data to a separate database, but
>> having it available is not causing a problem, except for some counts and
>> larger search actions, and being able to see how long a client has been
>> visiting is often useful. Statistical analysis is always done on a separate
>> machine, with a replicated copy of the data, so as not to affect the active
>> users ...
>> 
>> 
> What kind of counts/filters?  What kind of RAID subsystem is the storage?
> What's the total size of the DB?  Up to 20 years of data should be in the
> peta range.  In that peta range, if you're not having performance issue and
> not using either RAID 0, 0+1, 10, 50, or 60, I'd love to hear about the
> application and database design in details. :)
> 
> 
>> That said, we are now using the much more detailed LLPG address data rather
>> than simple postcode, and that has added another order of magnitude to data
>> that is being searched live ...
>> 
>> 
>> --
>> Lester Caine - G8HFL
>> -
>> Contact - 
>> http://lsces.co.uk/wiki/?page=**contact
>> L.S.Caine Electronic Services - http://lsces.co.uk
>> EnquirySolve - http://enquirysolve.com/
>> Model Engineers Digital Workshop - http://medw.co.uk//
>> Firebird - 
>> http://www.firebirdsql.org/**index.php
>> 
>> 
> Since Jason didn't disclose sufficient information, I suggested that RAID
> storage based on the worst case scenario being this:  as a business analyst,
> I'd want to do drill-downs, filters, counts of a the following for an
> up-coming marketing campaign:
> 
> * county(ies)
> * city(ies)
> * zip code(s)
> * address type (business and/or residential)
> * of business (commercial, industrial/manufacturing, etc)
> * of residential ( homes - single/townhouses - or apartments/condos )
> 
> The filters and counts will any combinations of the above, ie: 5 random zip
> codes within the given state that lists all other criteria and break down.
> As Jason mention having 89million rows for a given state, how long would it
> take to run the drill-downs if the DB isn't sitting on a fast storage
> medium?  That 89 million is the most likely the average count in the USA.
> For California and New York, the number can double that easily.  That's only
> the basic filtering.  What of a specific business industry such as
> landscaping?  What of the filtering by yearly income and/or real estate
> value?  BTW, as a business analyst, I don't want to wait hours for the info
> to update every time I change a criteria/filter to get the counts before I
> look into a few random individual records from the results.

The server that's running it is a home computer with a VPS installed... It's 
not my dev environment :)  

The information being searched is specifically phone numbers, and the bosses 
want to provide the public away to browse them, hence the pagination... Once I 
removed a COUNT from mysql it started working alot better... So I still need to 
provide a better pagination system, but it's working.

Oh, and the 89 million is just for one state :) We are talking the possibility 
of I believe 10 billion numbers to cover the US, not to mention Canada which I 
believe uses the same numbering system as we do so that could be another 10 
billion... 

> 
> As I've mentioned, something of this magnitude is better to leave it to the
> DBA and work together with that DBA.  Either hire/contract one or become one
> :)

I'm working on becoming one ontop of web designer and programmer :)
> 
> 
> Regards,
> Tommy


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Tommy Pham]

On Wed, Oct 26, 2011 at 4:14 AM, Lester Caine  wrote:

> Tommy Pham wrote:
>
>>
>>Many of my customers have coming up on 20 years of data available.
>> There has
>>been a debate on transferring historic data to a separate database, but
>>having it available is not causing a problem, except for some counts
>> and
>>larger search actions, and being able to see how long a client has been
>>visiting is often useful. Statistical analysis is always done on a
>> separate
>>machine, with a replicated copy of the data, so as not to affect the
>> active
>>users ...
>>
>>
>> What kind of counts/filters?  What kind of RAID subsystem is the storage?
>> What's the total size of the DB?  Up to 20 years of data should be in the
>> peta
>> range.  In that peta range, if you're not having performance issue and not
>> using
>> either RAID 0, 0+1, 10, 50, or 60, I'd love to hear about the application
>> and
>> database design in details. :)
>>
>
> We are still only in hundreds on Mb and historic data is has less detail
> than the current 'transactions'. The current postcode table is 500Mb, and
> while the LLPG data would increase that by the order of 100, it's currently
> only restricted to a councils immediate working area, so we keep the problem
> contained. Dropping back to postcode for out of area enquiries. Users
> complain if an enquiry takes more than a few seconds, and Firebird is giving
> me more than adequate performance, and allows shadow data to be created via
> triggers to reduce the need for 'counting'.
>
> I have a new 'application' which is using the same search criteria but the
> data volume is growing a lot faster, 10Gb on the test system here, but I am
> still seeing the same search speeds once the correct indexes have been
> generated. But it will take a few more years before that starts reaching the
> 100Gb level :)
>
>
> --
> Lester Caine - G8HFL
> -
> Contact - 
> http://lsces.co.uk/wiki/?page=**contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk//
> Firebird - 
> http://www.firebirdsql.org/**index.php
>
>
>
I'm just curious.  What's the total rows count?  Data accumulated in 20
years and only taking that much space doesn't seem like there's a lot going
on each year over the years.  All the DBAs that I know they deal with
minimum addition/import of 1 million rows per week, currently.  I didn't
bother asking them how far back they keep the data as that amount of rows is
too overwhelming for me, for the moment, as I'm not a DBA :)

Re: [PHP] Exporting large data from mysql to html using php

[Lester Caine]

Tommy Pham wrote:


Many of my customers have coming up on 20 years of data available. There has
been a debate on transferring historic data to a separate database, but
having it available is not causing a problem, except for some counts and
larger search actions, and being able to see how long a client has been
visiting is often useful. Statistical analysis is always done on a separate
machine, with a replicated copy of the data, so as not to affect the active
users ...


What kind of counts/filters?  What kind of RAID subsystem is the storage?
What's the total size of the DB?  Up to 20 years of data should be in the peta
range.  In that peta range, if you're not having performance issue and not using
either RAID 0, 0+1, 10, 50, or 60, I'd love to hear about the application and
database design in details. :)


We are still only in hundreds on Mb and historic data is has less detail than 
the current 'transactions'. The current postcode table is 500Mb, and while the 
LLPG data would increase that by the order of 100, it's currently only 
restricted to a councils immediate working area, so we keep the problem 
contained. Dropping back to postcode for out of area enquiries. Users complain 
if an enquiry takes more than a few seconds, and Firebird is giving me more than 
adequate performance, and allows shadow data to be created via triggers to 
reduce the need for 'counting'.


I have a new 'application' which is using the same search criteria but the data 
volume is growing a lot faster, 10Gb on the test system here, but I am still 
seeing the same search speeds once the correct indexes have been generated. But 
it will take a few more years before that starts reaching the 100Gb level :)


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Tommy Pham]

On Wed, Oct 26, 2011 at 3:23 AM, Tommy Pham  wrote:

> On Wed, Oct 26, 2011 at 1:40 AM, Lester Caine  wrote:
>
>> Tommy Pham wrote:
>>
>>> I wonder ... The real question is what's the purpose of the DB?  Is it
>>> for OLAP
>>> or OLTP? ;)
>>> As for dealing with DB having millions of rows, you're crossing over into
>>> DBA area.
>>>
>>
>> Many of my customers have coming up on 20 years of data available. There
>> has been a debate on transferring historic data to a separate database, but
>> having it available is not causing a problem, except for some counts and
>> larger search actions, and being able to see how long a client has been
>> visiting is often useful. Statistical analysis is always done on a separate
>> machine, with a replicated copy of the data, so as not to affect the active
>> users ...
>>
>>
> What kind of counts/filters?  What kind of RAID subsystem is the storage?
> What's the total size of the DB?  Up to 20 years of data should be in the
> peta range.  In that peta range, if you're not having performance issue and
> not using either RAID 0, 0+1, 10, 50, or 60, I'd love to hear about the
> application and database design in details. :)
>

Brain fart... that should be tera not peta ... lol..


>
>
>> That said, we are now using the much more detailed LLPG address data
>> rather than simple postcode, and that has added another order of magnitude
>> to data that is being searched live ...
>>
>>
>> --
>> Lester Caine - G8HFL
>> -
>> Contact - 
>> http://lsces.co.uk/wiki/?page=**contact
>> L.S.Caine Electronic Services - http://lsces.co.uk
>> EnquirySolve - http://enquirysolve.com/
>> Model Engineers Digital Workshop - http://medw.co.uk//
>> Firebird - 
>> http://www.firebirdsql.org/**index.php
>>
>>
> Since Jason didn't disclose sufficient information, I suggested that RAID
> storage based on the worst case scenario being this:  as a business analyst,
> I'd want to do drill-downs, filters, counts of a the following for an
> up-coming marketing campaign:
>
> * county(ies)
> * city(ies)
> * zip code(s)
> * address type (business and/or residential)
> * of business (commercial, industrial/manufacturing, etc)
> * of residential ( homes - single/townhouses - or apartments/condos )
>
> The filters and counts will any combinations of the above, ie: 5 random zip
> codes within the given state that lists all other criteria and break down.
> As Jason mention having 89million rows for a given state, how long would it
> take to run the drill-downs if the DB isn't sitting on a fast storage
> medium?  That 89 million is the most likely the average count in the USA.
> For California and New York, the number can double that easily.  That's only
> the basic filtering.  What of a specific business industry such as
> landscaping?  What of the filtering by yearly income and/or real estate
> value?  BTW, as a business analyst, I don't want to wait hours for the info
> to update every time I change a criteria/filter to get the counts before I
> look into a few random individual records from the results.
>
> As I've mentioned, something of this magnitude is better to leave it to the
> DBA and work together with that DBA.  Either hire/contract one or become one
> :)
>
>
> Regards,
> Tommy
>

Re: [PHP] Exporting large data from mysql to html using php

[Tommy Pham]

On Wed, Oct 26, 2011 at 1:40 AM, Lester Caine  wrote:

> Tommy Pham wrote:
>
>> I wonder ... The real question is what's the purpose of the DB?  Is it for
>> OLAP
>> or OLTP? ;)
>> As for dealing with DB having millions of rows, you're crossing over into
>> DBA area.
>>
>
> Many of my customers have coming up on 20 years of data available. There
> has been a debate on transferring historic data to a separate database, but
> having it available is not causing a problem, except for some counts and
> larger search actions, and being able to see how long a client has been
> visiting is often useful. Statistical analysis is always done on a separate
> machine, with a replicated copy of the data, so as not to affect the active
> users ...
>
>
What kind of counts/filters?  What kind of RAID subsystem is the storage?
What's the total size of the DB?  Up to 20 years of data should be in the
peta range.  In that peta range, if you're not having performance issue and
not using either RAID 0, 0+1, 10, 50, or 60, I'd love to hear about the
application and database design in details. :)


> That said, we are now using the much more detailed LLPG address data rather
> than simple postcode, and that has added another order of magnitude to data
> that is being searched live ...
>
>
> --
> Lester Caine - G8HFL
> -
> Contact - 
> http://lsces.co.uk/wiki/?page=**contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk//
> Firebird - 
> http://www.firebirdsql.org/**index.php
>
>
Since Jason didn't disclose sufficient information, I suggested that RAID
storage based on the worst case scenario being this:  as a business analyst,
I'd want to do drill-downs, filters, counts of a the following for an
up-coming marketing campaign:

* county(ies)
* city(ies)
* zip code(s)
* address type (business and/or residential)
* of business (commercial, industrial/manufacturing, etc)
* of residential ( homes - single/townhouses - or apartments/condos )

The filters and counts will any combinations of the above, ie: 5 random zip
codes within the given state that lists all other criteria and break down.
As Jason mention having 89million rows for a given state, how long would it
take to run the drill-downs if the DB isn't sitting on a fast storage
medium?  That 89 million is the most likely the average count in the USA.
For California and New York, the number can double that easily.  That's only
the basic filtering.  What of a specific business industry such as
landscaping?  What of the filtering by yearly income and/or real estate
value?  BTW, as a business analyst, I don't want to wait hours for the info
to update every time I change a criteria/filter to get the counts before I
look into a few random individual records from the results.

As I've mentioned, something of this magnitude is better to leave it to the
DBA and work together with that DBA.  Either hire/contract one or become one
:)


Regards,
Tommy

Re: [PHP] Exporting large data from mysql to html using php

[Lester Caine]

Tommy Pham wrote:

I wonder ... The real question is what's the purpose of the DB?  Is it for OLAP
or OLTP? ;)
As for dealing with DB having millions of rows, you're crossing over into DBA 
area.


Many of my customers have coming up on 20 years of data available. There has 
been a debate on transferring historic data to a separate database, but having 
it available is not causing a problem, except for some counts and larger search 
actions, and being able to see how long a client has been visiting is often 
useful. Statistical analysis is always done on a separate machine, with a 
replicated copy of the data, so as not to affect the active users ...


That said, we are now using the much more detailed LLPG address data rather than 
simple postcode, and that has added another order of magnitude to data that is 
being searched live ...


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Tommy Pham]

On Wed, Oct 26, 2011 at 12:52 AM, Lester Caine  wrote:

> Tommy Pham wrote:
>
>> It turns out the issue was actually in the pagination... I'm reworking the
>>> >  whole thing and stream lining it... But in the pagination that I found
>>> on
>>> >  the internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and
>>> the
>>> >  COUNT was killing the time... Once that was removed, I was displaying
>>> >  records faster then I could imagine... So it's off to pagination land
>>> to fix
>>> >  it! And possibly redo the entire thing!
>>> >
>>>
>> If you're encountering performance issues while doing SELECT COUNT(*), it
>> sounds like you have serious disk IO performance issue.  Is the DB on RAID
>> subsystem?  If not, why not? If so, what level?  Also, what type of HDDs?
>> For something like this, it should be RAID 10 with HDDs spinning at least
>> 7200RPM, 10,000+ RPM recommended, connected to a good RAID controller,
>> like
>> 3ware's.  Also, the controller should be in slot PCI-X or, preferably,
>> PCI-e.
>>
>
> What a load of twoddle ...
>
>
I wonder ... The real question is what's the purpose of the DB?  Is it for
OLAP or OLTP? ;)
As for dealing with DB having millions of rows, you're crossing over into
DBA area.


> SELECT COUNT(*) is a problem on any fully transactional database, since it
> has to be generated from the currently active view of the data.
> Rather than trying to get the database engine access every record faster,
> the correct action is to either avoid the count altogether, or more
> practically maintain a separate table with useful counts that have been
> generated from the committed data.
>
> Jason, it is not unreasonable that an initial view will be displaying
> unfiltered data, so you just need to restrict the number of records
> displayed. As you have found out, telling the user how many more records
> there are is the real problem, so if it's not important ... don't, but if
> it's useful to know, then keep a 'cache' of counts that link to your initial
> filter options. Once you are down to a few thousand records, then a SELECT
> COUNT(*) may be appropriate ;) Where it becomes a problem is when there the
> user can set up a more complex filter that the cache does not cover ...
>
> --
> Lester Caine - G8HFL
> -
> Contact - 
> http://lsces.co.uk/wiki/?page=**contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk//
> Firebird - 
> http://www.firebirdsql.org/**index.php
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP] Exporting large data from mysql to html using php

[Lester Caine]

Tommy Pham wrote:

It turns out the issue was actually in the pagination... I'm reworking the
>  whole thing and stream lining it... But in the pagination that I found on
>  the internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and the
>  COUNT was killing the time... Once that was removed, I was displaying
>  records faster then I could imagine... So it's off to pagination land to fix
>  it! And possibly redo the entire thing!
>

If you're encountering performance issues while doing SELECT COUNT(*), it
sounds like you have serious disk IO performance issue.  Is the DB on RAID
subsystem?  If not, why not? If so, what level?  Also, what type of HDDs?
For something like this, it should be RAID 10 with HDDs spinning at least
7200RPM, 10,000+ RPM recommended, connected to a good RAID controller, like
3ware's.  Also, the controller should be in slot PCI-X or, preferably,
PCI-e.


What a load of twoddle ...

SELECT COUNT(*) is a problem on any fully transactional database, since it has 
to be generated from the currently active view of the data.
Rather than trying to get the database engine access every record faster, the 
correct action is to either avoid the count altogether, or more practically 
maintain a separate table with useful counts that have been generated from the 
committed data.


Jason, it is not unreasonable that an initial view will be displaying unfiltered 
data, so you just need to restrict the number of records displayed. As you have 
found out, telling the user how many more records there are is the real problem, 
so if it's not important ... don't, but if it's useful to know, then keep a 
'cache' of counts that link to your initial filter options. Once you are down to 
a few thousand records, then a SELECT COUNT(*) may be appropriate ;) Where it 
becomes a problem is when there the user can set up a more complex filter that 
the cache does not cover ...


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Tommy Pham]

On Tue, Oct 25, 2011 at 7:06 PM, Jason Pruim wrote:

>
> It turns out the issue was actually in the pagination... I'm reworking the
> whole thing and stream lining it... But in the pagination that I found on
> the internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and the
> COUNT was killing the time... Once that was removed, I was displaying
> records faster then I could imagine... So it's off to pagination land to fix
> it! And possibly redo the entire thing!
>
>
If you're encountering performance issues while doing SELECT COUNT(*), it
sounds like you have serious disk IO performance issue.  Is the DB on RAID
subsystem?  If not, why not? If so, what level?  Also, what type of HDDs?
For something like this, it should be RAID 10 with HDDs spinning at least
7200RPM, 10,000+ RPM recommended, connected to a good RAID controller, like
3ware's.  Also, the controller should be in slot PCI-X or, preferably,
PCI-e.

Regards,
Tommy

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 25, 2011, at 10:10 PM, Jim Giner wrote:

> 
> - Original Message - From: "Jason Pruim" 
> To: "Jim Giner" 
> Cc: 
> Sent: Tuesday, October 25, 2011 10:06 PM
> Subject: Re: [PHP] Exporting large data from mysql to html using php
> 
> 
> It turns out the issue was actually in the pagination... I'm reworking the 
> whole thing and stream lining it... But in the pagination that I found on the 
> internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and the COUNT 
> was killing the time... Once that was removed, I was displaying records 
> faster then I could imagine... So it's off to pagination land to fix it! And 
> possibly redo the entire thing!
> 
> 
> So you're still going to generate pages and pages of an un-manageable volume 
> of data?
> 
> Sorry, but I offer my congratulations on producing a technically marvelous 
> solution, and my condolences for a completely worthless application.

It's walking the fine line between what I want, and what the people who are 
paying for it want... What I always try and do is give them what they ask for, 
but also give them what they want... because what they ask for != what they 
want MOST of the time...  So most likely I will end up pulling the pagination 
and really limiting the searching... 

But until I convince the right people... I go on as planned.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

- Original Message - 
From: "Jason Pruim" 

To: "Jim Giner" 
Cc: 
Sent: Tuesday, October 25, 2011 10:06 PM
Subject: Re: [PHP] Exporting large data from mysql to html using php


It turns out the issue was actually in the pagination... I'm reworking the 
whole thing and stream lining it... But in the pagination that I found on 
the internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and the 
COUNT was killing the time... Once that was removed, I was displaying 
records faster then I could imagine... So it's off to pagination land to fix 
it! And possibly redo the entire thing!



So you're still going to generate pages and pages of an un-manageable volume 
of data?


Sorry, but I offer my congratulations on producing a technically marvelous 
solution, and my condolences for a completely worthless application.








--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 25, 2011, at 9:58 PM, Jim Giner wrote:

> "David Robley"  wrote in message 
> news:49.50.34068.1b567...@pb1.pair.com...
>> 
>> Consider running EXPLAIN on all your queries to see if there is something
>> Mysql thinks could be done to improve performance.
>> 
> 
> Why do so many responders seem to think the problem here is in the 
> preparation of the query?
> 
> It is the Approach that it is the problem.  It needs to re-addressed, not 
> simply tinkered with.  I don't care if someone figures out how to spew out 
> 89M+ records in 5 seconds flat.  What are you doing pulling up that many 
> detail records at one time for? Who is going to look at them?  You?  If so, 
> see my previous post on how long that is going to take you just to examine 
> (!) the first million alone.
> 
> This problem needs a re-working of the data processing and user interfaces 
> to allow for the selection of an appropriate amount of individual records in 
> any result set, otherwise simply preparing a summary of some sort. 


It turns out the issue was actually in the pagination... I'm reworking the 
whole thing and stream lining it... But in the pagination that I found on the 
internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and the COUNT was 
killing the time... Once that was removed, I was displaying records faster then 
I could imagine... So it's off to pagination land to fix it! And possibly redo 
the entire thing!



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[David Robley]

Jason Pruim wrote:

> 
> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> On Oct 25, 2011, at 10:51 AM, Jim Giner wrote:
> 
>> I disagree.  It's not about "tuning the queries", it is more about the
>> appl. design that currently thinks it SHOULD do such huge queries.
>> 
>> My approach would be to prompt the user for filtering criteria that
>> automatically would reduce the result set size.  Although at this time I
>> believe the OP mentioned that the db is just telephone numbers so that
>> doesn't leave much room for filter-criteria.
>> 
>> 
> 
> Yes it is just phone numbers... The only select that I'm running on the
> entire site is related to the pagination... A simple: $sqlCount = "SELECT
> COUNT(*) FROM main WHERE state = '{$state}'";
> 
> which limits it to everything inside the state... Unfortunately if you
> look at the possibilities, it's still quite a large dataset... 89 million
> :)
> 
> The rest of the query's will be much more limited to areacode, exchange,
> and in some cases the full phone number... Maybe the better way to do it
> would be not to count the records But set a variable with the total
> count... That way I don't have to load all the data... The data amount
> won't change alot... Easy enough to set a variable...  Just need to see if
> I can integrate that with the pagination...
> 
> Back to the drawing board! :)
> 

Consider running EXPLAIN on all your queries to see if there is something
Mysql thinks could be done to improve performance.



Cheers
-- 
David Robley

Why do they put Braille dots on the keypad of the drive-up ATM?
Today is Prickle-Prickle, the 7th day of The Aftermath in the YOLD 3177. 


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

"David Robley"  wrote in message 
news:49.50.34068.1b567...@pb1.pair.com...
>
> Consider running EXPLAIN on all your queries to see if there is something
> Mysql thinks could be done to improve performance.
>

Why do so many responders seem to think the problem here is in the 
preparation of the query?

It is the Approach that it is the problem.  It needs to re-addressed, not 
simply tinkered with.  I don't care if someone figures out how to spew out 
89M+ records in 5 seconds flat.  What are you doing pulling up that many 
detail records at one time for? Who is going to look at them?  You?  If so, 
see my previous post on how long that is going to take you just to examine 
(!) the first million alone.

This problem needs a re-working of the data processing and user interfaces 
to allow for the selection of an appropriate amount of individual records in 
any result set, otherwise simply preparing a summary of some sort. 



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Bastien]

On 2011-10-25, at 6:56 PM, Jason Pruim  wrote:

> 
> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> On Oct 25, 2011, at 6:35 PM, Jim Giner wrote:
> 
>> Again  why even do a detail query?  Nobody is going to examine pages and 
>> pages and etc.  
>> Do a summary qry if u just need a count - no pagination there
>> jg
> 
> 
> The bosses wanted them to be able to page through the results... But I might 
> bring that up to them again... Working with 89 million records (With the 
> number going up and expanding in the future) could cause lots of issues in 
> the long run...

Guarantee the bosses will never use it. If they had to page thru 89m records, 
they'd rebel too







> 
> 
>> 
>> On Oct 25, 2011, at 6:26 PM, Jason Pruim  wrote:
>> 
>>> 
>>> Jason Pruim
>>> li...@pruimphotography.com
>>> 
>>> 
>>> 
>>> On Oct 25, 2011, at 10:51 AM, Jim Giner wrote:
>>> 
 I disagree.  It's not about "tuning the queries", it is more about the 
 appl. 
 design that currently thinks it SHOULD do such huge queries.
 
 My approach would be to prompt the user for filtering criteria that 
 automatically would reduce the result set size.  Although at this time I 
 believe the OP mentioned that the db is just telephone numbers so that 
 doesn't leave much room for filter-criteria. 
 
 
>>> 
>>> Yes it is just phone numbers... The only select that I'm running on the 
>>> entire site is related to the pagination... A simple:
>>> $sqlCount = "SELECT COUNT(*) FROM main WHERE state = '{$state}'";
>>> 
>>> which limits it to everything inside the state... Unfortunately if you look 
>>> at the possibilities, it's still quite a large dataset... 89 million :)
>>> 
>>> The rest of the query's will be much more limited to areacode, exchange, 
>>> and in some cases the full phone number... Maybe the better way to do it 
>>> would be not to count the records But set a variable with the total 
>>> count... That way I don't have to load all the data... The data amount 
>>> won't change alot... Easy enough to set a variable...  Just need to see if 
>>> I can integrate that with the pagination...
>>> 
>>> Back to the drawing board! :)
>>> 
>>> 
>>> 
 
 -- 
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
>>> 
>>> 
>> 
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>> 
> 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

Bastien Koert
905-904-0334
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 25, 2011, at 6:35 PM, Jim Giner wrote:

> Again  why even do a detail query?  Nobody is going to examine pages and 
> pages and etc.  
> Do a summary qry if u just need a count - no pagination there
> jg


The bosses wanted them to be able to page through the results... But I might 
bring that up to them again... Working with 89 million records (With the number 
going up and expanding in the future) could cause lots of issues in the long 
run...


> 
> On Oct 25, 2011, at 6:26 PM, Jason Pruim  wrote:
> 
>> 
>> Jason Pruim
>> li...@pruimphotography.com
>> 
>> 
>> 
>> On Oct 25, 2011, at 10:51 AM, Jim Giner wrote:
>> 
>>> I disagree.  It's not about "tuning the queries", it is more about the 
>>> appl. 
>>> design that currently thinks it SHOULD do such huge queries.
>>> 
>>> My approach would be to prompt the user for filtering criteria that 
>>> automatically would reduce the result set size.  Although at this time I 
>>> believe the OP mentioned that the db is just telephone numbers so that 
>>> doesn't leave much room for filter-criteria. 
>>> 
>>> 
>> 
>> Yes it is just phone numbers... The only select that I'm running on the 
>> entire site is related to the pagination... A simple:
>> $sqlCount = "SELECT COUNT(*) FROM main WHERE state = '{$state}'";
>> 
>> which limits it to everything inside the state... Unfortunately if you look 
>> at the possibilities, it's still quite a large dataset... 89 million :)
>> 
>> The rest of the query's will be much more limited to areacode, exchange, and 
>> in some cases the full phone number... Maybe the better way to do it would 
>> be not to count the records But set a variable with the total count... 
>> That way I don't have to load all the data... The data amount won't change 
>> alot... Easy enough to set a variable...  Just need to see if I can 
>> integrate that with the pagination...
>> 
>> Back to the drawing board! :)
>> 
>> 
>> 
>>> 
>>> -- 
>>> PHP General Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>> 
>> 
>> 
> 
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

Again  why even do a detail query?  Nobody is going to examine pages and pages 
and etc.  
Do a summary qry if u just need a count - no pagination there
jg


On Oct 25, 2011, at 6:26 PM, Jason Pruim  wrote:

> 
> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> On Oct 25, 2011, at 10:51 AM, Jim Giner wrote:
> 
>> I disagree.  It's not about "tuning the queries", it is more about the appl. 
>> design that currently thinks it SHOULD do such huge queries.
>> 
>> My approach would be to prompt the user for filtering criteria that 
>> automatically would reduce the result set size.  Although at this time I 
>> believe the OP mentioned that the db is just telephone numbers so that 
>> doesn't leave much room for filter-criteria. 
>> 
>> 
> 
> Yes it is just phone numbers... The only select that I'm running on the 
> entire site is related to the pagination... A simple:
> $sqlCount = "SELECT COUNT(*) FROM main WHERE state = '{$state}'";
> 
> which limits it to everything inside the state... Unfortunately if you look 
> at the possibilities, it's still quite a large dataset... 89 million :)
> 
> The rest of the query's will be much more limited to areacode, exchange, and 
> in some cases the full phone number... Maybe the better way to do it would be 
> not to count the records But set a variable with the total count... That 
> way I don't have to load all the data... The data amount won't change alot... 
> Easy enough to set a variable...  Just need to see if I can integrate that 
> with the pagination...
> 
> Back to the drawing board! :)
> 
> 
> 
>> 
>> -- 
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>> 
> 
> 

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 25, 2011, at 10:51 AM, Jim Giner wrote:

> I disagree.  It's not about "tuning the queries", it is more about the appl. 
> design that currently thinks it SHOULD do such huge queries.
> 
> My approach would be to prompt the user for filtering criteria that 
> automatically would reduce the result set size.  Although at this time I 
> believe the OP mentioned that the db is just telephone numbers so that 
> doesn't leave much room for filter-criteria. 
> 
> 

Yes it is just phone numbers... The only select that I'm running on the entire 
site is related to the pagination... A simple:
$sqlCount = "SELECT COUNT(*) FROM main WHERE state = '{$state}'";

which limits it to everything inside the state... Unfortunately if you look at 
the possibilities, it's still quite a large dataset... 89 million :)

The rest of the query's will be much more limited to areacode, exchange, and in 
some cases the full phone number... Maybe the better way to do it would be not 
to count the records But set a variable with the total count... That way I 
don't have to load all the data... The data amount won't change alot... Easy 
enough to set a variable...  Just need to see if I can integrate that with the 
pagination...

Back to the drawing board! :)



> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

I disagree.  It's not about "tuning the queries", it is more about the appl. 
design that currently thinks it SHOULD do such huge queries.

My approach would be to prompt the user for filtering criteria that 
automatically would reduce the result set size.  Although at this time I 
believe the OP mentioned that the db is just telephone numbers so that 
doesn't leave much room for filter-criteria. 



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[tamouse mailing lists]

On Mon, Oct 24, 2011 at 7:50 PM, Jason Pruim  wrote:
> I have about 89 million records in mysql... the initial load of the page 
> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
> query's... But they just aren't going fast enough...
>
> What I would like to do, is pull the data out of MySQL and store it in the 
> HTML files, and then update the HTML files once a day/week/month... I can 
> figure most of it out... BUT... How do I automatically link to the individual 
> pages?

Does this help?

http://peeling.hubpages.com/hub/Generate_Static_HTML_Pages_From_A_Database

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Bastien]

On 2011-10-24, at 11:14 PM, Jason Pruim  wrote:

> 
> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> On Oct 24, 2011, at 9:20 PM, Bastien wrote:
> 
>> 
>> 
>> On 2011-10-24, at 8:50 PM, Jason Pruim  wrote:
>> 
>>> Now that I've managed to list 3 separate programming languages and somewhat 
>>> tie it back into php here's the question...
>>> 
>>> I have about 89 million records in mysql... the initial load of the page 
>>> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
>>> query's... But they just aren't going fast enough...
>>> 
>>> What I would like to do, is pull the data out of MySQL and store it in the 
>>> HTML files, and then update the HTML files once a day/week/month... I can 
>>> figure most of it out... BUT... How do I automatically link to the 
>>> individual pages?
>>> 
>>> I have the site working when you pull it from MySQL... Just the load time 
>>> sucks... Any suggestions on where I can pull some more info from? :)
>>> 
>>> Thanks in advance!
>>> 
>>> 
>>> Jason Pruim
>>> li...@pruimphotography.com
>>> 
>>> 
>>> 
>>> 
>>> -- 
>>> PHP General Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>> 
>> 
>> That's a ton of data. So there are a couple of questions:
>> 
>> 1. Is the data ordered in any way? 
> 
> Data is in numeric order in the database, but not specifing a ORDER BY on the 
> SELECT..
>> 
>> The issue is that you might need to regenerate the files if new data needs 
>> to be interspersed.
> 
> Indexes are freshly done... I might need to tweak them and learn more about 
> them... Working on that right now!
>> 
>> 2. Why is pagination slow? 
> 
> Pagination isn't all that slow... Just the initial load of the site... The 
> rest of it works fairly well... But the guys with the paychecks aren't going 
> for a 2-3 minute initial load time on a public website
>> 
>> A LIMIT with an OFFSET should be very quick if the table is properly 
>> indexed. Is there any tuning that you can do to further filter the results? 
>> Say by date or some other criteria? Have you run an EXPLAIN plan on the db 
>> to show how the queries are being run?
>> 
>> Other thoughts:
>> 
>> - are your indexes up to date? Have you optimized those index to be clean, 
>> not fragmented, and therefore fast?
>> 
> 
> Just generated the indexes so it should be completely perfect.
> 
>> - can you organize the data by some criteria to better show the results? 
>> Like by date? Or by starting alpha character? Or by category?
> 
> Trying to split up the data some... It's phone numbers... So I have the area 
> code, exchange, and the subscriber number stored in 3 separate fields... But 
> that doesn't seem any faster then when I had it all in one field
> 
> I think I just need to rethink the design a little bit, and optimize the 
> entire app... But I think that will hit tomorrow.
> 
> Thanks for your suggestions!
>> 
>> Bastien Koert
>> 905-904-0334
>> 
>> 
>> 
>> 
>> 
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>> 
> 

My preference would be to have that as one numeric field ( bigint ) and search 
on that. DBs like numbers and should be able to return the data very quickly

As for the app start, can you present a tiny ( say 10 rows ) subset that you 
could read in from a cache? Perhaps the 10 most common searched on or last 10 
used? You could cache it for an hour and then refresh it. Same thing applies if 
the start is like a dashboard with various reports. Cache out what you can and 
hit the DB for the bare minimum to keep people happy

Then your app should be right quick 
to start 

Bastien Koert
905-904-0334


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Bastien]

On 2011-10-24, at 11:26 PM, "Jim Giner"  wrote:

> Yes - but - we're talking about a user-app that the OP is trying to provide 
> 89M records to.  Sure - "some" users might have need of looking at even as 
> much as a million records IF they were researching something that needed it. 
> But - for the 'general' user of an app - I cannot see a need to be providing 
> even that much data.  Think about it - you give the user 1M records, how 
> long is that user going to page thru the results?  Let's say there are 20 
> results on a page and it takes a mere (wow!) 2 seconds to scan thru them 
> looking for something apparently very obvious.  That's 600 results viewed 
> per minute at best.  Six hundred into a M is 1666 minutes which is 27+ 
> hours.  Even at 1 second per page view time, it's still more time than in a 
> normal work day.  And you want come up with a way to feed him 89M?
> 
> The problem needs to be looked at differently - put together a 
> business-smart solution to finding the  needle in this haystack of  89M 
> pieces of straw and only then apply technology to it.
> 
> 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

I agree, Jim. Which is why I was asking a about tuning the queries. Jason 
already indicated he was using limits and offsets, so it's other avenues i was 
looking for.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

Yes - but - we're talking about a user-app that the OP is trying to provide 
89M records to.  Sure - "some" users might have need of looking at even as 
much as a million records IF they were researching something that needed it. 
But - for the 'general' user of an app - I cannot see a need to be providing 
even that much data.  Think about it - you give the user 1M records, how 
long is that user going to page thru the results?  Let's say there are 20 
results on a page and it takes a mere (wow!) 2 seconds to scan thru them 
looking for something apparently very obvious.  That's 600 results viewed 
per minute at best.  Six hundred into a M is 1666 minutes which is 27+ 
hours.  Even at 1 second per page view time, it's still more time than in a 
normal work day.  And you want come up with a way to feed him 89M?

The problem needs to be looked at differently - put together a 
business-smart solution to finding the  needle in this haystack of  89M 
pieces of straw and only then apply technology to it.



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Jason Pruim
li...@pruimphotography.com



On Oct 24, 2011, at 9:20 PM, Bastien wrote:

> 
> 
> On 2011-10-24, at 8:50 PM, Jason Pruim  wrote:
> 
>> Now that I've managed to list 3 separate programming languages and somewhat 
>> tie it back into php here's the question...
>> 
>> I have about 89 million records in mysql... the initial load of the page 
>> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
>> query's... But they just aren't going fast enough...
>> 
>> What I would like to do, is pull the data out of MySQL and store it in the 
>> HTML files, and then update the HTML files once a day/week/month... I can 
>> figure most of it out... BUT... How do I automatically link to the 
>> individual pages?
>> 
>> I have the site working when you pull it from MySQL... Just the load time 
>> sucks... Any suggestions on where I can pull some more info from? :)
>> 
>> Thanks in advance!
>> 
>> 
>> Jason Pruim
>> li...@pruimphotography.com
>> 
>> 
>> 
>> 
>> -- 
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>> 
> 
> That's a ton of data. So there are a couple of questions:
> 
> 1. Is the data ordered in any way? 

Data is in numeric order in the database, but not specifing a ORDER BY on the 
SELECT..
> 
> The issue is that you might need to regenerate the files if new data needs to 
> be interspersed.

Indexes are freshly done... I might need to tweak them and learn more about 
them... Working on that right now!
> 
> 2. Why is pagination slow? 

Pagination isn't all that slow... Just the initial load of the site... The rest 
of it works fairly well... But the guys with the paychecks aren't going for a 
2-3 minute initial load time on a public website
> 
> A LIMIT with an OFFSET should be very quick if the table is properly indexed. 
> Is there any tuning that you can do to further filter the results? Say by 
> date or some other criteria? Have you run an EXPLAIN plan on the db to show 
> how the queries are being run?
> 
> Other thoughts:
> 
> - are your indexes up to date? Have you optimized those index to be clean, 
> not fragmented, and therefore fast?
> 

Just generated the indexes so it should be completely perfect.

> - can you organize the data by some criteria to better show the results? Like 
> by date? Or by starting alpha character? Or by category?

Trying to split up the data some... It's phone numbers... So I have the area 
code, exchange, and the subscriber number stored in 3 separate fields... But 
that doesn't seem any faster then when I had it all in one field

I think I just need to rethink the design a little bit, and optimize the entire 
app... But I think that will hit tomorrow.

Thanks for your suggestions!
> 
> Bastien Koert
> 905-904-0334
> 
> 
> 
> 
> 
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jason Pruim]

On Oct 24, 2011, at 10:44 PM, Jim Giner wrote:

> Why would any user need to have access to 89M records?

They don't need access to it to edit it... Just to be able to view it... Also, 
it will be expanding in the future to include alot more data.



Jason Pruim
li...@pruimphotography.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Bastien]

On 2011-10-24, at 10:44 PM, "Jim Giner"  wrote:

> Why would any user need to have access to 89M records?
> 
> 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

History or audit trail data? I can think of lots. I know of an app with a 
poorly designed system that easily gets over 240 million

Bastien Koert
905-904-0334
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Jim Giner]

Why would any user need to have access to 89M records?



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Bastien]

On 2011-10-24, at 8:50 PM, Jason Pruim  wrote:

> Now that I've managed to list 3 separate programming languages and somewhat 
> tie it back into php here's the question...
> 
> I have about 89 million records in mysql... the initial load of the page 
> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL 
> query's... But they just aren't going fast enough...
> 
> What I would like to do, is pull the data out of MySQL and store it in the 
> HTML files, and then update the HTML files once a day/week/month... I can 
> figure most of it out... BUT... How do I automatically link to the individual 
> pages?
> 
> I have the site working when you pull it from MySQL... Just the load time 
> sucks... Any suggestions on where I can pull some more info from? :)
> 
> Thanks in advance!
> 
> 
> Jason Pruim
> li...@pruimphotography.com
> 
> 
> 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

That's a ton of data. So there are a couple of questions:

1. Is the data ordered in any way? 

The issue is that you might need to regenerate the files if new data needs to 
be interspersed.

2. Why is pagination slow? 

A LIMIT with an OFFSET should be very quick if the table is properly indexed. 
Is there any tuning that you can do to further filter the results? Say by date 
or some other criteria? Have you run an EXPLAIN plan on the db to show how the 
queries are being run?

Other thoughts:

- are your indexes up to date? Have you optimized those index to be clean, not 
fragmented, and therefore fast?

- can you organize the data by some criteria to better show the results? Like 
by date? Or by starting alpha character? Or by category?

Bastien Koert
905-904-0334





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting large data from mysql to html using php

[Shiplu Mokaddim]

2-3 minutes is long enough. I think your SELECT query and MySQL schema has 
something that can be improved.

Sent from a handheld device.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting large data from mysql to html using php

[Jason Pruim]

Now that I've managed to list 3 separate programming languages and somewhat tie 
it back into php here's the question...

I have about 89 million records in mysql... the initial load of the page takes 
2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL query's... 
But they just aren't going fast enough...

What I would like to do, is pull the data out of MySQL and store it in the HTML 
files, and then update the HTML files once a day/week/month... I can figure 
most of it out... BUT... How do I automatically link to the individual pages?

I have the site working when you pull it from MySQL... Just the load time 
sucks... Any suggestions on where I can pull some more info from? :)

Thanks in advance!


Jason Pruim
li...@pruimphotography.com




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting text with chinese characters in CSV

[Michael Shadle]

The php script language has no bearing on the output unless you have  
characters In the php file itself.


We had some issue like this at work. They found a way using iconv to  
to it but had to change because redhats iconv isn't updated. They do  
something with saving the output to a utf8 encoded page and then  
sending it out or something. I assume you're trying to have this be  
used in excel?


On Mar 27, 2009, at 2:59 AM, Ashley Sheridan  
 wrote:



On Fri, 2009-03-27 at 17:40 +0800, Ai Leen wrote:

Hi Everyone,

I need to export data from database with UTF-8 encoding to an csv  
file. I am

outputing html tables with the Content Type set to msexcel.

The chinese texts came out as symbols. I tried
using mb_convert_encoding the text from UTF-8 to UTF-16LE
iconv from UTF8 to gb2312
iconv from UTF-8 to cp1252

Can anyone who has successfully export english text with chinese  
characters

mixed in to CSV help?

Thank you very much,
Ai Leen



Strictly speaking, a csv file won't contain HTML markup, so you should
probably just stick to delimited value lines in your file. Have you
tried changing the Content Type to text/plain and then save your PHP
script as utf-8. It's this last one that sometimes causes problems,  
as I

believe it is needed for PHP to correctly output utf-8.


Ash
www.ashleysheridan.co.uk


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting text with chinese characters in CSV

[Ashley Sheridan]

On Fri, 2009-03-27 at 17:40 +0800, Ai Leen wrote:
> Hi Everyone,
> 
> I need to export data from database with UTF-8 encoding to an csv file. I am 
> outputing html tables with the Content Type set to msexcel.
> 
> The chinese texts came out as symbols. I tried
> using mb_convert_encoding the text from UTF-8 to UTF-16LE
> iconv from UTF8 to gb2312
> iconv from UTF-8 to cp1252
> 
> Can anyone who has successfully export english text with chinese characters 
> mixed in to CSV help?
> 
> Thank you very much,
> Ai Leen 
> 
> 
Strictly speaking, a csv file won't contain HTML markup, so you should
probably just stick to delimited value lines in your file. Have you
tried changing the Content Type to text/plain and then save your PHP
script as utf-8. It's this last one that sometimes causes problems, as I
believe it is needed for PHP to correctly output utf-8.


Ash
www.ashleysheridan.co.uk


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting text with chinese characters in CSV

[Ai Leen]

Hi Everyone,

I need to export data from database with UTF-8 encoding to an csv file. I am 
outputing html tables with the Content Type set to msexcel.


The chinese texts came out as symbols. I tried
using mb_convert_encoding the text from UTF-8 to UTF-16LE
iconv from UTF8 to gb2312
iconv from UTF-8 to cp1252

Can anyone who has successfully export english text with chinese characters 
mixed in to CSV help?


Thank you very much,
Ai Leen 



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting to Excel

[Dan Shirah]

>
>Come on, Shirah.  GOYA and RTFM!  ;-P
>
>http://php.net/ifx_fieldtypes
>
>Drop the $i parameter below:
>
>
> [snip!]
> > $query = ifx_query ($sql, $connect_id);
> [snip!]
> >  $head[] = ifx_fieldtypes($query, $i);
> [snip!]


Son of a Bisquick! I'll chalk that up to looking at the Manual for like 4
hours today!

I removed the $i, but instead of the column headers it just says: Array
Array Array Array Array Arrayinstead of the column titles...and none of
the column data displays.

Okay, Dan...now I have to GOMA and go home before my wife KMA! Thanks again
for poiting that out.

Re: [PHP] Exporting to Excel

[Daniel Brown]

On Wed, Jun 18, 2008 at 5:53 PM, Dan Shirah <[EMAIL PROTECTED]> wrote:
> Hello all,
>
[snip!]
>
> Well, when I execute the page I get the popup to save/open the output as an
> Excel file.  When I open it, instead of getting data, it returns an error
> message of: PHP Warning: Wrong parameter count for ifx_fieldtypes()

Come on, Shirah.  GOYA and RTFM!  ;-P

http://php.net/ifx_fieldtypes

Drop the $i parameter below:


[snip!]
> $query = ifx_query ($sql, $connect_id);
[snip!]
>  $head[] = ifx_fieldtypes($query, $i);
[snip!]

-- 

Dedicated Servers - Intel 2.4GHz w/2TB bandwidth/mo. starting at just
$59.99/mo. with no contract!
Dedicated servers, VPS, and hosting from $2.50/mo.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting to Excel

[Dan Shirah]

Hello all,

I am trying to query an INFORMIX database, and then take the results and
export them into a CSV or Excel file. I saw TONS of examples on how to do
this using MySQL and tried to adpt them for ifx, but I can't get the darn
thing working.  My code below sets variables for values passed from my form.
I then connect to my database and run a query that gets results based on the
variable values. After that I want to count the number of fields(columns)
and begin to build the export file.

Well, when I execute the page I get the popup to save/open the output as an
Excel file.  When I open it, instead of getting data, it returns an error
message of: PHP Warning: Wrong parameter count for ifx_fieldtypes()

I am trying to use ifx_fieldtypes() to replace the mysql function of
mysql_field_name()

Any ideas?

$fieldName);
  } // End loop
  $data .= join(',', $row)."\n"; // Create a new row of data and append it
to the last row
  $row = ''; // Clear the contents of the $row variable to start a new row
 }
 // Start our output of the CSV
 header("Content-type: application/x-msdownload");
 header("Content-Disposition: attachment; filename=data.csv");
 header("Pragma: no-cache");
 header("Expires: 0");
 echo $headers.$data;
} else {
 // Nothing needed to be output. Put an error message here or something.
 echo 'No data available for this CSV.';
}
?>

Re: [PHP] Exporting from Multiple mySQL Tables into One - SOLVED!

[Rahul S. Johari]

Ave,

I was fiddling my way around with foreach and found a solution to my
problem. Here's my code:

// define the array
$tChoice = array(
   "lodispo_osma" => "ATL",
   "lodispo_osmh" => "HOU",
   "lodispo_osmj" => "JAX",
   "lodispo_osmt" => "TPA",
   "lodispo" => "VB",
   "lodispo_hfd" => "HFD"
);

// database "definition"
$def = array(
  array("phone","C", 10),
  array("comments","C", 254),
  array("starttime","C", 10),
  array("endtime","C", 10),
  array("dispo","C", 80),
  array("lo", "C", 50),
  array("verifier", "C", 50),
  array("branch","C",3)
);

// creation
if (!dbase_create('dbf/ALL.dbf', $def)) {
  echo "Error, can't create the database";
}

   
// open in read-write mode
$db2 = dbase_open("dbf/ALL.dbf", 2);
if ($db2) {

// Let's Run Array Loops!
foreach ($tChoice as $tblQ => $bxQ) {

// connect to mySQL table
mysql_select_db($database_imslead_transfer, $imslead_transfer);
$query_loDispo = "SELECT * FROM $tblQ";
$loDispo = mysql_query($query_loDispo, $imslead_transfer) or
die(mysql_error());
$row_loDispo = mysql_fetch_assoc($loDispo);
$totalRows_loDispo = mysql_num_rows($loDispo);
  
// write mySql data to Dbf
do {
 dbase_add_record($db2, array(
 $row_loDispo['phone'],
 $row_loDispo['comments'],
 $row_loDispo['starttime'],
 $row_loDispo['endtime'],
 $row_loDispo['dispo'],
 $row_loDispo['loanofficer'],
 $row_loDispo['verifier'],
 $bxQ));
 } while ($row_loDispo = mysql_fetch_assoc($loDispo));
}
 
 
dbase_close($db2);
}

mysql_free_result($loDispo);
header("Location: dbf/ALL.dbf");

Works like a charm!

Thanks!

Rahul S. Johari
Supervisor, Internet & Administration
Informed Marketing Services Inc.
500 Federal Street, Suite 201
Troy NY 12180

Tel: (518) 687-6700 x154
Fax: (518) 687-6799
Email: [EMAIL PROTECTED]
http://www.informed-sources.com



On 1/18/07 11:14 AM, "Rahul S. Johari" <[EMAIL PROTECTED]> wrote:

> Ave,
> 
> I¹m not sure how to execute this. I can easily write a long, tedious code to
> handle each table separately, but I¹m sure there is an Array/For Each way to
> do this. 
> 
> I¹ve got 6 mySQL tables. I¹ve written a code that dumps data from each table
> into one corresponding DBF table. While writing to the DBF, I also add a
> field & a value to it identifying the table.
> I need to dump data from all the mySQL tables into one DBF table, and also
> have that added field & identifier value added to each table¹s data.
> 
> Here¹s my code to export from 1 mySQL table into 1 DBF table.
> ($tChoice & $bx are Table Name & Identifier Value that are passed on from a
> previous page):
> 
> // database "definition"
> $def = array(
>   array("phone","C", 10),
>   array("comments","C", 254),
>   array("starttime","C", 10),
>   array("endtime","C", 10),
>   array("dispo","C", 80),
>   array("lo", "C", 50),
>   array("verifier", "C", 50),
>   array("branch","C",3)
> );
> 
> // creation
> if (!dbase_create('dbf/'.$bx.'.dbf', $def)) {
>   echo "Error, can't create the database";
> }
>  
> // open in read-write mode
> $db2 = dbase_open("dbf/".$bx.".dbf", 2);
> if ($db2) {
>   
> mysql_select_db($database_imslead_transfer, $imslead_transfer);
> $query_loDispo = "SELECT * FROM $tChoice";
> $loDispo = mysql_query($query_loDispo, $imslead_transfer) or
> die(mysql_error());
> $row_loDispo = mysql_fetch_assoc($loDispo);
> $totalRows_loDispo = mysql_num_rows($loDispo);
> 
>   // write mySql data to Dbf
>   do {
>  dbase_add_record($db2, array(
>  $row_loDispo['phone'],
>  $row_loDispo['comments'],
>  $row_loDispo['starttime'],
>  $row_loDispo['endtime'],
>  $row_loDispo['dispo'],
>  $row_loDispo['loanofficer'],
>  $row_loDispo['verifier'],
>  $bx));
>  } while ($row_loDispo = mysql_fetch_assoc($loDispo));
>  
> dbase_close($db2);
> }
> 
> mysql_free_result($loDispo);
> header("Location: dbf/$bx.dbf");
> 
> Now I just need to write a code which takes data from each table, one by
> one, and appends it to the DBF, alongwith adding a field that carries the
> Identifier Value for each table.
> 
> Any suggestions?
> 
> Rahul S. Johari
> Supervisor, Internet & Administration
> Informed Marketing Services Inc.
> 500 Federal Street, Suite 201
> Troy NY 12180
> 
> Tel: (518) 687-6700 x154
> Fax: (518) 687-6799
> Email: [EMAIL PROTECTED]
> http://www.i

[PHP] Exporting from Multiple mySQL Tables into One

[Rahul S. Johari]

Ave,

I¹m not sure how to execute this. I can easily write a long, tedious code to
handle each table separately, but I¹m sure there is an Array/For Each way to
do this. 

I¹ve got 6 mySQL tables. I¹ve written a code that dumps data from each table
into one corresponding DBF table. While writing to the DBF, I also add a
field & a value to it identifying the table.
I need to dump data from all the mySQL tables into one DBF table, and also
have that added field & identifier value added to each table¹s data.

Here¹s my code to export from 1 mySQL table into 1 DBF table.
($tChoice & $bx are Table Name & Identifier Value that are passed on from a
previous page):

// database "definition"
$def = array(
  array("phone","C", 10),
  array("comments","C", 254),
  array("starttime","C", 10),
  array("endtime","C", 10),
  array("dispo","C", 80),
  array("lo", "C", 50),
  array("verifier", "C", 50),
  array("branch","C",3)
);

// creation
if (!dbase_create('dbf/'.$bx.'.dbf', $def)) {
  echo "Error, can't create the database";
}
   
// open in read-write mode
$db2 = dbase_open("dbf/".$bx.".dbf", 2);
if ($db2) {
  
mysql_select_db($database_imslead_transfer, $imslead_transfer);
$query_loDispo = "SELECT * FROM $tChoice";
$loDispo = mysql_query($query_loDispo, $imslead_transfer) or
die(mysql_error());
$row_loDispo = mysql_fetch_assoc($loDispo);
$totalRows_loDispo = mysql_num_rows($loDispo);

  // write mySql data to Dbf
  do {
 dbase_add_record($db2, array(
 $row_loDispo['phone'],
 $row_loDispo['comments'],
 $row_loDispo['starttime'],
 $row_loDispo['endtime'],
 $row_loDispo['dispo'],
 $row_loDispo['loanofficer'],
 $row_loDispo['verifier'],
 $bx));
 } while ($row_loDispo = mysql_fetch_assoc($loDispo));
 
dbase_close($db2);
}

mysql_free_result($loDispo);
header("Location: dbf/$bx.dbf");

Now I just need to write a code which takes data from each table, one by
one, and appends it to the DBF, alongwith adding a field that carries the
Identifier Value for each table.

Any suggestions?

Rahul S. Johari
Supervisor, Internet & Administration
Informed Marketing Services Inc.
500 Federal Street, Suite 201
Troy NY 12180

Tel: (518) 687-6700 x154
Fax: (518) 687-6799
Email: [EMAIL PROTECTED]
http://www.informed-sources.com

Re: [PHP] Exporting to MS Word or Excel

[Rory Browne]

On 6/3/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> To export it exactly as displayed (like when you print to a virtual printer 
> to generate a PDF) might be tricky, but you can definitely create Excel and I 
> believe Word files without even having Excel or Word installed.   If you DO 
> have Excel or Word installed on your server, then you can always use a COM 
> connection to handle this.
He's using LAMP, in which case the 'L' stands for Linux. Therefore he
can't have Word or Excel installed, nor can he use OLE or COM.

You might be able to rig up something with Uno/OOo(and if you do I'd
like to see your code), but I've absolutely no experience with Uno,
and very little with OOo.

Alternatively if  you can find a way to create RTF's, you can AFAIK
pass them off as word docs, by passing the correct header. you can
IIRC do something similar by creating csv's and passing an Excel
Header file.


> 
> First, maybe check out the Excel Writer PEAR package.  Here's a link to a 
> post I made about it a while ago with instructions on how to install it:
> 
> http://marc.theaimsgroup.com/?l=php-general&m=111409575703230&w=2
> 
> Excel Writer uses another PEAR package called OLE which is used to generate 
> older format MS Office files in general.  Both can be found at 
> http://pear.php.net
> 
> 
> Also, try searching the archives, I know this question has been asked many 
> times (even recently) and I'm sure you'll find some good stuff in there.
> 
> PHP-General archives:
> http://marc.theaimsgroup.com/?l=php-general
> 
> PHP-Windows archives:
> http://marc.theaimsgroup.com/?l=php-windows
> 
> PHP-Databases archives (never know eh?):
> http://marc.theaimsgroup.com/?l=php-db
> 
> And of course, the links to all these can be found on the PHP mailing lists 
> page:
> http://www.php.net/mailing-lists.php
> 
> 
> Good luck!
> 
> -TG
> 
> 
> = = = Original message = = =
> 
> Hi!!!
> 
> Are there any chances that I could export a dynamic created web page into MS
> Word or Excel?
> I know this can be done with PDF!!
> 
> I'm using LAMP!!
> 
> ---
> Miguel Guirao Aguilera
> Logistica R8 TELCEL
> Tel. (999) 960.7994
> Cel. 9931 600.
> 
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 
> ___
> Sent by ePrompter, the premier email notification software.
> Free download at http://www.ePrompter.com.
> 
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting to MS Word or Excel

[tg-php]

To export it exactly as displayed (like when you print to a virtual printer to 
generate a PDF) might be tricky, but you can definitely create Excel and I 
believe Word files without even having Excel or Word installed.   If you DO 
have Excel or Word installed on your server, then you can always use a COM 
connection to handle this.

First, maybe check out the Excel Writer PEAR package.  Here's a link to a post 
I made about it a while ago with instructions on how to install it:

http://marc.theaimsgroup.com/?l=php-general&m=111409575703230&w=2

Excel Writer uses another PEAR package called OLE which is used to generate 
older format MS Office files in general.  Both can be found at 
http://pear.php.net


Also, try searching the archives, I know this question has been asked many 
times (even recently) and I'm sure you'll find some good stuff in there.

PHP-General archives:
http://marc.theaimsgroup.com/?l=php-general

PHP-Windows archives:
http://marc.theaimsgroup.com/?l=php-windows

PHP-Databases archives (never know eh?):
http://marc.theaimsgroup.com/?l=php-db

And of course, the links to all these can be found on the PHP mailing lists 
page:
http://www.php.net/mailing-lists.php


Good luck!

-TG


= = = Original message = = =

Hi!!!

Are there any chances that I could export a dynamic created web page into MS
Word or Excel?
I know this can be done with PDF!!

I'm using LAMP!!

---
Miguel Guirao Aguilera
Logistica R8 TELCEL
Tel. (999) 960.7994
Cel. 9931 600.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting to MS Word or Excel

[Miguel Guirao]

Hi!!!

Are there any chances that I could export a dynamic created web page into MS
Word or Excel?
I know this can be done with PDF!!

I'm using LAMP!!

---
Miguel Guirao Aguilera
Logistica R8 TELCEL
Tel. (999) 960.7994
Cel. 9931 600.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting HTML to Excel

[Vail, Warren]

> header('Content-Type: application/ms-excel');
> header('Content-Disposition: attachment; filename="project.htm"'); ?>
>AAA used by
excel to determine translation, I believe

I believe the Content-Type is referred to as a mime header (8-}), I often
see something similar in email.

HTH,

Warren Vail
(415) 667-0240
SF211-07-434
 


-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 13, 2004 6:18 AM
To: Vail, Warren
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP] Exporting HTML to Excel


How exactly would I change the mime headers?

~Philip

On Oct 12, 2004, at 5:07 PM, Vail, Warren wrote:

> Have you tried changing your file name to project.htm but continue
> issuing
> the mime headers for excel?
>
> Warren Vail
>
> -Original Message-
> From: Philip Thompson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 12, 2004 2:21 PM
> To: [EMAIL PROTECTED]
> Subject: [PHP] Exporting HTML to Excel
>
>
> Hi all.
>
> This may not be completely a PHP question, but hopefully you will be 
> able to provide some insight. I have a table in HTML that I want to 
> export to an excel spreadsheet. Using PHP, I can create an excel 
> document - however, it's empty/blank. I think I am just doing it 
> incorrectly. Anybody done this before and/or have any ideas? I would 
> prefer to NOT use a third-party program.
>
> I've searched the web and php.net, but have been somewhat unsuccessful 
> in finding anything. Here's what I have so far:
>
> -
>  header('Content-Type: application/ms-excel');
> header('Content-Disposition: attachment; filename="project.xls"'); ?>
>
> 
>   
>   Something
>   Something else
>   
> 
> -
>
> I just want it to have that content in the spreadsheet. It's actually 
> more involved than that, but that's the gist of it.
>
> Thanks in advance.
> ~Philip
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting HTML to Excel

[Chris Dowell]

In an attempt to make amends, have you tried PEAR's 
Spreadsheet_Excel_Writer?

http://pear.php.net/package/Spreadsheet_Excel_Writer
It certainly works, we used it last week (although I have no *personal* 
experience of it)

Cheers
Chris
Chris Dowell wrote:

sorry
[skulks off into dark corner to cry]
Philip Thompson wrote:
Chris, as you may have (or may have not) noticed, I did that. Look at 
my original email. I wasn't sure if there was more to it than that.

~Philip
On Oct 13, 2004, at 8:34 AM, Chris Dowell wrote:
Header("Content-type: application/ms-excel");
or whatever the content type should be (on IE you can probably get 
away with application/octet-stream as it bases its decisions of file 
extensions for a lot of things).

HTH
Chris
Philip Thompson wrote:
How exactly would I change the mime headers?
~Philip
On Oct 12, 2004, at 5:07 PM, Vail, Warren wrote:
Have you tried changing your file name to project.htm but continue 
issuing
the mime headers for excel?

Warren Vail
-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 12, 2004 2:21 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Exporting HTML to Excel
Hi all.
This may not be completely a PHP question, but hopefully you will be
able to provide some insight. I have a table in HTML that I want to
export to an excel spreadsheet. Using PHP, I can create an excel
document - however, it's empty/blank. I think I am just doing it
incorrectly. Anybody done this before and/or have any ideas? I would
prefer to NOT use a third-party program.
I've searched the web and php.net, but have been somewhat unsuccessful
in finding anything. Here's what I have so far:
-



Something
Something else


-
I just want it to have that content in the spreadsheet. It's actually
more involved than that, but that's the gist of it.
Thanks in advance.
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting HTML to Excel

[Chris Dowell]

sorry
[skulks off into dark corner to cry]
Philip Thompson wrote:
Chris, as you may have (or may have not) noticed, I did that. Look at my 
original email. I wasn't sure if there was more to it than that.

~Philip
On Oct 13, 2004, at 8:34 AM, Chris Dowell wrote:
Header("Content-type: application/ms-excel");
or whatever the content type should be (on IE you can probably get 
away with application/octet-stream as it bases its decisions of file 
extensions for a lot of things).

HTH
Chris
Philip Thompson wrote:
How exactly would I change the mime headers?
~Philip
On Oct 12, 2004, at 5:07 PM, Vail, Warren wrote:
Have you tried changing your file name to project.htm but continue 
issuing
the mime headers for excel?

Warren Vail
-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 12, 2004 2:21 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Exporting HTML to Excel
Hi all.
This may not be completely a PHP question, but hopefully you will be
able to provide some insight. I have a table in HTML that I want to
export to an excel spreadsheet. Using PHP, I can create an excel
document - however, it's empty/blank. I think I am just doing it
incorrectly. Anybody done this before and/or have any ideas? I would
prefer to NOT use a third-party program.
I've searched the web and php.net, but have been somewhat unsuccessful
in finding anything. Here's what I have so far:
-



Something
Something else


-
I just want it to have that content in the spreadsheet. It's actually
more involved than that, but that's the gist of it.
Thanks in advance.
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting HTML to Excel

[Philip Thompson]

Chris, as you may have (or may have not) noticed, I did that. Look at 
my original email. I wasn't sure if there was more to it than that.

~Philip
On Oct 13, 2004, at 8:34 AM, Chris Dowell wrote:
Header("Content-type: application/ms-excel");
or whatever the content type should be (on IE you can probably get 
away with application/octet-stream as it bases its decisions of file 
extensions for a lot of things).

HTH
Chris
Philip Thompson wrote:
How exactly would I change the mime headers?
~Philip
On Oct 12, 2004, at 5:07 PM, Vail, Warren wrote:
Have you tried changing your file name to project.htm but continue 
issuing
the mime headers for excel?

Warren Vail
-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 12, 2004 2:21 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Exporting HTML to Excel
Hi all.
This may not be completely a PHP question, but hopefully you will be
able to provide some insight. I have a table in HTML that I want to
export to an excel spreadsheet. Using PHP, I can create an excel
document - however, it's empty/blank. I think I am just doing it
incorrectly. Anybody done this before and/or have any ideas? I would
prefer to NOT use a third-party program.
I've searched the web and php.net, but have been somewhat 
unsuccessful
in finding anything. Here's what I have so far:

-



Something
Something else


-
I just want it to have that content in the spreadsheet. It's actually
more involved than that, but that's the gist of it.
Thanks in advance.
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting HTML to Excel

[Chris Dowell]

Header("Content-type: application/ms-excel");
or whatever the content type should be (on IE you can probably get away 
with application/octet-stream as it bases its decisions of file 
extensions for a lot of things).

HTH
Chris
Philip Thompson wrote:
How exactly would I change the mime headers?
~Philip
On Oct 12, 2004, at 5:07 PM, Vail, Warren wrote:
Have you tried changing your file name to project.htm but continue 
issuing
the mime headers for excel?

Warren Vail
-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 12, 2004 2:21 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Exporting HTML to Excel
Hi all.
This may not be completely a PHP question, but hopefully you will be
able to provide some insight. I have a table in HTML that I want to
export to an excel spreadsheet. Using PHP, I can create an excel
document - however, it's empty/blank. I think I am just doing it
incorrectly. Anybody done this before and/or have any ideas? I would
prefer to NOT use a third-party program.
I've searched the web and php.net, but have been somewhat unsuccessful
in finding anything. Here's what I have so far:
-



Something
Something else


-
I just want it to have that content in the spreadsheet. It's actually
more involved than that, but that's the gist of it.
Thanks in advance.
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting HTML to Excel

[Philip Thompson]

How exactly would I change the mime headers?
~Philip
On Oct 12, 2004, at 5:07 PM, Vail, Warren wrote:
Have you tried changing your file name to project.htm but continue 
issuing
the mime headers for excel?

Warren Vail
-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 12, 2004 2:21 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Exporting HTML to Excel
Hi all.
This may not be completely a PHP question, but hopefully you will be
able to provide some insight. I have a table in HTML that I want to
export to an excel spreadsheet. Using PHP, I can create an excel
document - however, it's empty/blank. I think I am just doing it
incorrectly. Anybody done this before and/or have any ideas? I would
prefer to NOT use a third-party program.
I've searched the web and php.net, but have been somewhat unsuccessful
in finding anything. Here's what I have so far:
-



Something
Something else


-
I just want it to have that content in the spreadsheet. It's actually
more involved than that, but that's the gist of it.
Thanks in advance.
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting HTML to Excel

[Vail, Warren]

Have you tried changing your file name to project.htm but continue issuing
the mime headers for excel?

Warren Vail

-Original Message-
From: Philip Thompson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 12, 2004 2:21 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Exporting HTML to Excel


Hi all.

This may not be completely a PHP question, but hopefully you will be 
able to provide some insight. I have a table in HTML that I want to 
export to an excel spreadsheet. Using PHP, I can create an excel 
document - however, it's empty/blank. I think I am just doing it 
incorrectly. Anybody done this before and/or have any ideas? I would 
prefer to NOT use a third-party program.

I've searched the web and php.net, but have been somewhat unsuccessful 
in finding anything. Here's what I have so far:

-




Something
Something else


-

I just want it to have that content in the spreadsheet. It's actually 
more involved than that, but that's the gist of it.

Thanks in advance.
~Philip

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting HTML to Excel

[Graham Cossey]

Philip, have you checked the php freaks website? I do recall seeing a script
in their tutorials library that claimed to create Excel files.

http://www.phpfreaks.com

Just had a search and came up with:



Cell 1
Cell 2

";
?>

HTH
Graham

> -Original Message-
> From: Philip Thompson [mailto:[EMAIL PROTECTED]
> Sent: 12 October 2004 22:21
> To: [EMAIL PROTECTED]
> Subject: [PHP] Exporting HTML to Excel
>
>
> Hi all.
>
> This may not be completely a PHP question, but hopefully you will be
> able to provide some insight. I have a table in HTML that I want to
> export to an excel spreadsheet. Using PHP, I can create an excel
> document - however, it's empty/blank. I think I am just doing it
> incorrectly. Anybody done this before and/or have any ideas? I would
> prefer to NOT use a third-party program.
>
> I've searched the web and php.net, but have been somewhat unsuccessful
> in finding anything. Here's what I have so far:
>
> -
>  header('Content-Type: application/ms-excel');
> header('Content-Disposition: attachment; filename="project.xls"');
> ?>
>
> 
>   
>   Something
>   Something else
>   
> 
> -
>
> I just want it to have that content in the spreadsheet. It's actually
> more involved than that, but that's the gist of it.
>
> Thanks in advance.
> ~Philip
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting HTML to Excel

[Philip Thompson]

Hi all.
This may not be completely a PHP question, but hopefully you will be 
able to provide some insight. I have a table in HTML that I want to 
export to an excel spreadsheet. Using PHP, I can create an excel 
document - however, it's empty/blank. I think I am just doing it 
incorrectly. Anybody done this before and/or have any ideas? I would 
prefer to NOT use a third-party program.

I've searched the web and php.net, but have been somewhat unsuccessful 
in finding anything. Here's what I have so far:

-



Something
Something else


-
I just want it to have that content in the spreadsheet. It's actually 
more involved than that, but that's the gist of it.

Thanks in advance.
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting Dbase to CSV

[Jay Blanchard]

[snip]
"Jay Blanchard" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>
> What, exactly, do you mean? A CSV file that resembles a spreadsheet? A
> doc with CSV?

The term "CSV" is very common; it means "Comma Separated Values". It is
the 
format that originally was (and is) used by the Basic language. When
someone 
says "CSV format" or "CSV file" I understand quite well what they mean
or 
should mean. It is not a standard format, but it is nearly standard.

I have no idea what a "CSV file that resembles a spreadsheet" is and I 
especially have no idea what a "doc with CSV" is. The definition of
those 
however are probably not important here, except they are much less
likely to 
be understood than the term "CSV format".
[/snip]

Sam,

Thanks for bringing this back up. I know what a CSV file is, but the
original poster asked a fairly ambiguous question, so I was attempting
to get him to clarify what he was looking for.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting Dbase to CSV

[Sam Hobbs]

"Jay Blanchard" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>
> What, exactly, do you mean? A CSV file that resembles a spreadsheet? A
> doc with CSV?

The term "CSV" is very common; it means "Comma Separated Values". It is the 
format that originally was (and is) used by the Basic language. When someone 
says "CSV format" or "CSV file" I understand quite well what they mean or 
should mean. It is not a standard format, but it is nearly standard.

I have no idea what a "CSV file that resembles a spreadsheet" is and I 
especially have no idea what a "doc with CSV" is. The definition of those 
however are probably not important here, except they are much less likely to 
be understood than the term "CSV format".

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting Dbase to CSV

[Torsten Roehr]

"Harlequin" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I'm using MySQL Jay
>
> I can present the results in a table easy enough and if I replace the TD
> TAGs with commas etc. I get a screen output that resembles a CSV file but
> need to go that one step further and don't know how...

Please specify what you mean with "one step further"? You can assign the
contents to a variable and write it to a file.

Regards, Torsten Roehr


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting Dbase to CSV

[Jay Blanchard]

[snip]
I'm using MySQL Jay

I can present the results in a table easy enough and if I replace the TD
TAGs with commas etc. I get a screen output that resembles a CSV file
but
need to go that one step further and don't know how...
[/snip]

Use CSV (without the table thing) and place Excel header definitions in
the script

header("Content-Type:  application/vnd.ms-excel");
header("Content-Disposition: inline; filename=\"excel.xls\"");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");


and then an example from one of our spreadsheet outputs

print($rowage->TmsNoLines . ",");
print($rowage->TmsStatus . ",");
print($rowage->TmsPaidDate . ",");
print(number_format($rowage->Current, 2, '.', '') . ",");
print(number_format($rowage->Thirty, 2, '.', '') . ",");
print(number_format($rowage->Sixty, 2, '.', '') . ",");
print(number_format($rowage->Ninety, 2, '.', '') . ",");
print(number_format($rowage->OneTwenty, 2, '.', '') . ",\n");

We usually make the link open in a new window when outputting sheets
like this. All the user needs to do is save it locally at that poiint.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting Dbase to CSV

[Harlequin]

I'm using MySQL Dan

I can present the results in a table easy enough and if I replace the TD
TAGs with commas etc. I get a screen output that resembles a CSV file but
need to go that one step further and don't know how...

-- 
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-
"Dan Joseph" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> Depending on which database you're using, there is the UNLOAD
> function.  Do a google for it, should find the answers.
>
> > I've seen many different posts on this subject and try as I might I
can't
> > get my data to output to CSV for a user to save.
> >
> > I can output to a table, I can output to screen in CSV format but that
> > just
> > means massing about with copy and paste.
> >
> > Has anyone got any suggestions...?
>
> -Dan Joseph

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting Dbase to CSV

[Harlequin]

I'm using MySQL Jay

I can present the results in a table easy enough and if I replace the TD
TAGs with commas etc. I get a screen output that resembles a CSV file but
need to go that one step further and don't know how...

-- 
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-
"Jay Blanchard" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
[snip]
I've seen many different posts on this subject and try as I might I
can't
get my data to output to CSV for a user to save.

I can output to a table, I can output to screen in CSV format but that
just
means massing about with copy and paste.

Has anyone got any suggestions...?
[/snip]

What, exactly, do you mean? A CSV file that resembles a spreadsheet? A
doc with CSV?

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting Dbase to CSV

[Dan Joseph]

Hi,

Depending on which database you're using, there is the UNLOAD
function.  Do a google for it, should find the answers.

> I've seen many different posts on this subject and try as I might I can't
> get my data to output to CSV for a user to save.
> 
> I can output to a table, I can output to screen in CSV format but that
> just
> means massing about with copy and paste.
> 
> Has anyone got any suggestions...?

-Dan Joseph

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting Dbase to CSV

[Jay Blanchard]

[snip]
I've seen many different posts on this subject and try as I might I
can't
get my data to output to CSV for a user to save.

I can output to a table, I can output to screen in CSV format but that
just
means massing about with copy and paste.

Has anyone got any suggestions...?
[/snip]

What, exactly, do you mean? A CSV file that resembles a spreadsheet? A
doc with CSV?

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting Dbase to CSV

[Harlequin]

I've seen many different posts on this subject and try as I might I can't
get my data to output to CSV for a user to save.

I can output to a table, I can output to screen in CSV format but that just
means massing about with copy and paste.

Has anyone got any suggestions...?

-- 
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[Vern]

I knew that ;)

Some times it takes another set of eyes...

Thanks

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[Vern]

I did. ;)

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[Jason Wong]

On Saturday 14 August 2004 00:31, Vern wrote:
> I got it. In case anyone is interested. I changed the code so that the
> field value was a became the variable $name and it worked.
>
>  $name = $rsITEMS->Fields('item_id');
>  fwrite($fp, "$name\n");

But did you take the fopen() out of the while-loop? It makes no sense for it 
to be in there.

-- 
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-general
--
/*
The only thing necessary for the triumph of evil is for good men to do 
nothing.
- Edmund Burke
*/

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[riese]

Hi Vern,
Vern wrote:
Sorry the previous post got sent prematurely.
this one, too. #
It does work, however, the problem is in the
fwrite($fp, "$rsITEMS->Fields('item_id')\n");
I KNOW, this was the 2nd change in my code-sniplet ;o)
what gets written to the file is exactly "$rsITEMS->Fields('item_id')" for
that's exactly, what you ask php to write.
You can do nearly anything in php in strings. but not call functions.
$rsITEMS->Fields('item_id') call a Method in a Class. It's not an array 
or something. It's a FUNCTION-CALL. You can't call funtions from within 
strings.

echo "Hello ucfirst('world')";
results in: Hello ucfirst('world')
echo "Hello ".ucfist('world');
results in
Hello World
() != [] != {} ;o)
[...]
Here's the code again:
No, thanks.
Have a nice weekend
Ste'smar-ass'phan
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[Vern]

I got it. In case anyone is interested. I changed the code so that the field
value was a became the variable $name and it worked.

 $name = $rsITEMS->Fields('item_id');
 fwrite($fp, "$name\n");

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[Vern]

Sorry the previous post got sent prematurely.

It does work, however, the problem is in the
fwrite($fp, "$rsITEMS->Fields('item_id')\n");

what gets written to the file is exactly "$rsITEMS->Fields('item_id')" for
as many records are return when I want the value to be inserted in the file.
I've also tried echo $rsITEMS->Fields('item_id'), but then that is returned
exactly that was as well.

Here's the code again:

EOF) {

//CREATE CSV
//OPEN DUMP FILE
   $fp = fopen("./dump.sql", "w");
//WRITE DATA TO DUMP
   fwrite($fp, "$rsITEMS->Fields('item_id')\n");
//LOOP
$rsITEMS->MoveNext();
  }
//CLOSE FILE
   fclose($fp);
 ?>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting data from database using fopen

[riese]

Hi Vern,
Vern wrote:
I'm trying to write data to a file from a Postgres database using the
following code
while (!$rsITEMS->EOF) {
   $fp = fopen("./dump.sql", "w");
   fwrite($fp, "$rsITEMS->Fields('item_id')\n");
$rsITEMS->MoveNext();
> [shortened]
I wouldn't do, if I were you.
It doesn't work, right?
try:
$fp = fopen("dump.sql", "w"); /* make shure, you have the w-bit set on 
dir for the user who runs php */
if($fp)
{
	while(!$rsITEMS->EOF)
	{
		fwrite($fp, $rsITEMS->Fields('item_id')."\n");
		$rsITEMS->MoveNext();
	}
	fclose($fp);
}else
die("EXCPETION fopen(w)");

does it help?
riese
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting data from database using fopen

[Vern]

I'm trying to write data to a file from a Postgres database using the
following code

EOF) {

//CREATE CSV
//OPEN DUMP FILE
   $fp = fopen("./dump.sql", "w");
//WRITE DATA TO DUMP
   fwrite($fp, "$rsITEMS->Fields('item_id')\n");
//LOOP
$rsITEMS->MoveNext();
  }
//CLOSE FILE
   fclose($fp);
 ?>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Exporting Data as CSV - IE6/HTTPS Problem?

[Geoffrey R. Thompson]

Actually, no.

The 'mangled' version is not an encrypted version of the filename - it
is a non-encrypted fragment of my page url.
 
My filename is "report.csv"

My page URL is "https://www.mysite.com/report.php?key=29&sendto=Export"; 

The code works fine via either https or http in Mozilla (file
'report.csv' downloads successfully).  

In IE6 via https, the filename somehow gets replaced with the url (or a
portion of it), and shows up in the 'save' dialog as -
"...port.php?key=29&sendto=Export".

So, I don't think it is an encryption problem.

> -Original Message-
> From: Anas Mughal [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 04, 2003 4:27 PM
> To: Geoffrey Thompson
> Subject: Re: [PHP] Exporting Data as CSV - IE6/HTTPS Problem?
> 
> HTTPS is encrypting the URL -- including your file name.
> 
> 
> 
> Geoffrey Thompson wrote:
> 
> > All:
> >
> > I have the following php code for downloading a file to the user via
the
> > browser:
> >
> >   // Open csv file.
> >   $fp = fopen($fileName, "r");
> >
> >   // Set file name.
> >   $dwnldName = "report";
> >
> >   // Set headers for csv download.
> >   header("Content-Type:application/csv");
> >   header("Content-Disposition:attachment; filename=$dwnldName.csv");
> >   header("Content-Transfer-Encoding:binary");
> >
> >   // Put it to the browser.
> >   fpassthru($fp);
> >
> > Works great in the Mozilla browser over both http and https.
> >
> > Works great in IE 6 over http, but for some reason over https, my
> filename
> > mysteriously turns into a mangled version of my page url, and the
file
> > cannot (obviously) be found to be downloaded.
> >
> > Is it something I'm doing, or is this an IE problem?  And if so, are
> there
> > any work-arounds?
> >
> > Thanks in advance,
> >
> > Geoff Thompson
> >
> >
> 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting Data as CSV - IE6/HTTPS Problem?

[Geoffrey Thompson]

All:

I have the following php code for downloading a file to the user via the
browser:

  // Open csv file.
  $fp = fopen($fileName, "r");

  // Set file name.
  $dwnldName = "report";

  // Set headers for csv download.
  header("Content-Type:application/csv");
  header("Content-Disposition:attachment; filename=$dwnldName.csv");
  header("Content-Transfer-Encoding:binary");

  // Put it to the browser.
  fpassthru($fp);

Works great in the Mozilla browser over both http and https.

Works great in IE 6 over http, but for some reason over https, my filename
mysteriously turns into a mangled version of my page url, and the file
cannot (obviously) be found to be downloaded.

Is it something I'm doing, or is this an IE problem?  And if so, are there
any work-arounds?

Thanks in advance,

Geoff Thompson

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting Data to Excel

[Marek Kilimajer]

Damn exploder, use session_cache_limiter('private_no_expire'); before 
session_start()

Ben C. wrote:
I am using the code below to export my data into an excel file.  The code is
located in a password protected area which is checked against saved session
variables.  However when I put session_start(); the download errors out.
Does any one have any suggestions?  Please help.

$connection = @mysql_connect("$host", "$uname", "$pass") or die("Couldn't
connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");
/* Specify the filename, which includes a datestamp for archival purposes...
*/
$date_stamp = date("Ymd");
$file_name = "ExportedData" . "_" . "$date_stamp" . ".xls";
/* Specify MIME type for tab-separated-values... */
//header("Content-type: text/tab-separated-values");
/* To open file directly into Excel, use this MIME type instead... */
header("Content-type: application/x-msexcel");
/* To force file download... */
header("Content-Disposition: attachment; filename=$file_name");
/* List the spreadsheet column labels (optional)... */
$column_labels = array("Label 1", "Label 2 ", "Label 3");   /* ...and so on
*/
foreach($column_labels as $value) {
  echo("$value\t");
}
echo("\n");
/* ...end column labels row */
/* Specify the SELECT query (you will need to change this)... */
$query = "SELECT * FROM buyers";
/* Execute the query... */
$result = mysql_query($query);
/* Format data as tab-separated values... */
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  while (list($key, $value) = each($row)) {
echo ("$value" . "\t");
  }
  echo ("\n");
}
?>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting Data to Excel

[Ben C.]

I am using the code below to export my data into an excel file.  The code is
located in a password protected area which is checked against saved session
variables.  However when I put session_start(); the download errors out.
Does any one have any suggestions?  Please help.



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Exporting mySQL Table Data to .csv

[Richard Lynch]

>I use phpMyAdmin which enables me to take dump of
>mySQL Table Data into Comma Seperated Values file
>(.csv)
>
>Now, i have to create such a program that accomplishes
>this, without using phpMyAdmin. Can someone guide me
>to this procedure..

Since PHP has a fget_csv function (or something like that) odds are pretty
good it can write CSV as well.

If not, and *IF* you can do tab-delimited instead (*MUCH* easier) and just
as easy to suck in on the other end, you can do:



If you *MUST* use CSV, not tab-delimited, I *THINK* this will work:



-- 
Like Music?  http://l-i-e.com/artists.htm
Off-Topic:  What is the moral equivalent of 'cat' in Windows?

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Exporting mySQL Table Data to .csv

[Thomas Edison Jr.]

Glory,

I use phpMyAdmin which enables me to take dump of
mySQL Table Data into Comma Seperated Values file
(.csv)

Now, i have to create such a program that accomplishes
this, without using phpMyAdmin. Can someone guide me
to this procedure..

Thanks,
T. Edison Jr.



__
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] exporting

[Jon Farmer]

> hey guys..
>
> does anyone have any ideas on how to export information from a mysql
> database to microsoft word, excel, access, note pad or any other such
> application?
>

There is a class on http://phpclasses.upperdesign.com/

That allows you to create a excel file from php. Should be fairly
straightforward to pull the info using php and right to Excel file using the
class

Regards

Jon
--
Jon Farmer
Systems Programmer, Entanet www.enta.net
Tel 01952 428969 Mob 07763 620378
PGP Key available, send email with subject: Send PGP Key



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

RE: [PHP] exporting

[Matthew Loff]

I always stick with PHP for web apps, but I am in a similar situation, I
have to write a script that generates statistics and item reports from a
database... The client wanted them in Excel format... I could have done
CSV, but for the sake of aesthetics, I decided to go with a native Excel
export... The best way of doing it is writing it in Perl with the
SpreadSheet::WriteExcel module.

Take a look at it...  I was quite impressed.


-Original Message-
From: Kunal Jhunjhunwala [mailto:[EMAIL PROTECTED]] 
Sent: Monday, November 19, 2001 5:01 PM
To: [EMAIL PROTECTED]
Subject: [PHP] exporting


hey guys..

does anyone have any ideas on how to export information from a mysql
database to microsoft word, excel, access, note pad or any other such
application?


Regards,
Kunal Jhunjhunwala


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

RE: [PHP] exporting

[Jason Murray]

> I need something that will do a direct transfer to word or 
> excel or the others.. not something via a cvs method..

Excel loads comma separated (CSV - CVS is something else entirely)
files happily.

Jason

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] exporting

[Kunal Jhunjhunwala]

I need something that will do a direct transfer to word or excel or the
others.. not something via a cvs method..
Regards,
Kunal Jhunjhunwala
- Original Message -
From: "Joshua Hoover" <[EMAIL PROTECTED]>
To: "Kunal Jhunjhunwala" <[EMAIL PROTECTED]>
Sent: Tuesday, November 20, 2001 3:39 AM
Subject: Re: [PHP] exporting


> You can use something like this from MySQL:
>
> SELECT * INTO OUTFILE 'data.txt'
> FIELDS TERMINATED BY ','
> FROM ...;
>
> This will give you a comma delimited file that you can then import into
> Excel, Access, etc.
>
> You can find more out on this at:
> http://www.mysql.com/doc/L/O/LOAD_DATA.html
>
> Hope that helps.
>
> Joshua Hoover
>
> > hey guys..
> >
> > does anyone have any ideas on how to export information from a mysql
> > database to microsoft word, excel, access, note pad or any other such
> > application?
> >
> >
> > Regards,
> > Kunal Jhunjhunwala
>
>
>


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP] exporting

[Kunal Jhunjhunwala]

hey guys..

does anyone have any ideas on how to export information from a mysql
database to microsoft word, excel, access, note pad or any other such
application?


Regards,
Kunal Jhunjhunwala


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]