Solved

Using LIMIT without separating similar data

Posted on 2008-10-22
3
208 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
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:
Tiggerito 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now