0

Possible Duplicate:
How can i get more then 50 results to display in excel using mysql?

I have a problem: I have a MySQL script getting results and allowing customers to see their results through an Excel export. However, the spreadsheet is getting limited to only 50 results when the website shows lots more, sometimes up to 1500.

I've searched and it seems I have to edit /etc/my.cnf. I try to edit it and paste more code in but when I exit out of insert mode half of the code gets cut off - am I missing something?

This is all that is in my current /etc/my.cnf -- I realize its the default. I'm new to editing in vi so please bear with me.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Community
  • 1
  • 1
Crystal Leahy
  • 43
  • 6
  • What is the code you are running against the database? – Alex Hadley Jul 26 '12 at 16:16
  • I'm using Php/mysql to select different tables in my database and using csv to export as excel. unfortunately i can't post it in here it wont let me. it's a series of FROM, LEFT JOIN on LEFT JOIN on LEFT JOIN on LEFT JOIN on WHERE LIMIT 0, 50 – Crystal Leahy Jul 26 '12 at 20:12

1 Answers1

2

This is likely not due to anything in my.cnf. Your query skeleton from the comments does LIMIT 0,50, so getting 50 rows back is the exact behavior you should expect. Remove the LIMIT 0, 50 clause from your query to get it to return all rows.

Update after comments:

Upon removing the LIMIT clause, the OP reported the script was timing out. The solution to this in PHP is to increase the max_execution_time via:

set_time_limit(300);
// OR
ini_set('max_execution_time', 300);
Community
  • 1
  • 1
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • I've tried that it just times out and I get website cannot display the page error. This is my dilemma I'm stuck and not really knowing my way around mysql isn't helping – Crystal Leahy Jul 27 '12 at 12:54
  • @CrystalLeahy What is the site running? PHP? If so, increase the [max execution time](http://stackoverflow.com/search?q=%5Bphp%5D+max+execution+time) ... [PHP docs](http://us3.php.net/manual/en/info.configuration.php) – Michael Berkowski Jul 27 '12 at 13:07
  • 1
    I put ini_set('max_execution_time', 300); //300 seconds = 5 minutes in the top of my script it works! thank you so much and thank you for baring with me and for your knowledge. – Crystal Leahy Jul 27 '12 at 14:01
  • @CrystalLeahy Glad it worked out for you. I added the execution time to the answer – Michael Berkowski Jul 27 '12 at 14:05