Solved

Using LIMIT without separating similar data

Posted on 2008-10-22
3
211 Views
Last Modified: 2012-05-05
Hi, I'm using MySQL with PHP and I have a query similar to this:

SELECT i.ID, i.total, c.custname FROM tblinvoices i
LEFT JOIN tblcustomers c ON c.ID=i.custid
ORDER BY c.ID
LIMIT 0,1000

Is there a way to write this query to make it take less or more invoices depending on how many similar c.custnames there are at the end of the limit?  Basically, I don't want to set the limit stop at a point between a couple similar custnames, so I don't have to go to the next set of invoices (1000,2000) to get the rest of the similar custnames that were cut off at 1000.  I can't send the whole table and work with it in php because it exceeds the memory limits of php (there's a lot more than just 3 fields I'm getting from the real query).
0
Comment
Question by:kendalltech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Expert Comment

by:nick_bal
ID: 22782395
Try to increase the limit in your php.ini file. The default is set to 8mb but you can change it to 32 or 64
memory_limit = 8M --->> memory_limit = 32M. See if it works.

0
 
LVL 23

Accepted Solution

by:
Tony McCreath earned 500 total points
ID: 22806984
You could do a query to get the number of invoices for each of the customers, then do some maths to determine what limit values to use to ensure clean customer pages.

e.g. if it returns

customername, invoices
1, 400
2, 500
3, 200
4, 700
5, 350

you may choose your first page to be 0, 1100   (i.e. 400+500+200)

and the second page cound be 1100, 1050  


SELECT c.custname as customername, count(c.ID) as invoices  FROM tblcustomer c
LEFT JOIN tblinvoices i ON c.ID=i.custid
GROUP BY c.ID
ORDER BY c.ID

Open in new window

0
 
LVL 2

Author Closing Comment

by:kendalltech
ID: 31509022
great idea, thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question