Solved

Using LIMIT without separating similar data

Posted on 2008-10-22
3
213 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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