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
Solved

Using LIMIT without separating similar data

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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