Help needed with Php - MySQL App

o2designs

Hi there, i am writting a PHP - MySql app the basic idea of which is to maintain a database of employees and details about them (eg. which dept & group they are in). The when the person who is ment to be lecturing them for staff training is not in all of the people affected by it are sent an SMS message (via http to clickatell.com). So far all of it is working and i can pull out all of the affected people using this SQL query

"$query="SELECT employee.firstname, employee.lastname, employee.id, employee.group, depthead.course, lectures.starttime, lectures.option, lectures.module, lectures.endtime, employee.mobile FROM employees, lectures WHERE emplyee.course = lectures.course AND employee.group = lectures.group AND lectures.lecturer = '$staffName' AND lectures.day = '$dayDate' ORDER BY employee.firstname ASC";"

Dont worry if that seems confusing becuase it is not that important. The problem comes when the SMS messages need sending. Only 100 SMS messages can be sent per http call to clickatell.com. So if i need to send more than that then i need to split up the response in groups. I know that this can be done using the LIMIT command from MySQL but also the other issue is that different messages need to be send to different groups so.......... the question that i am asking is i guess how is it possible to pull out the mobile numbers of the people from the different groups that they are in without writing a load of different SQL queries. I already have the whole list of affected people using the above query but somehow i need to split this up again so that different SMS messages can be sent to the different groups and with no more that 100 people in each split section. I was told on another forum that this can be done using the GROUP BY command in MySQL and then formulating some kind of array. Please Please can sombody help me out. If the above is kind confusing i would be happy to clear any points up. It is a kinda complex problem and i didnt really want to be writting a whole essay about it.

Thanks for your time

Regards

Alex Shaw

barand

Can you just add the group to the order by clause

"... ORDER BY employee.group, employee.firstname"

then when you process the query, check for change of group and maintain count of records in each group?

hth

o2designs

Have just about solved this by using a query loop inside another query loop.. having problems with the GROUP by function now

check out this link if you think you might be able to help me out with a GROUP By MySQL isssue
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10223415

Regards

barand

Not only can you group by by more than one item but if you select more than one field you have to!

eg " SELECT a,b, count(*) from table GROUP BY a " gives error

should be

" SELECT a,b, count(*) from table GROUP BY a,b "

is OK and gives a count of records for each a,b combination.

Tbull

You might want to consider a different column name then GROUP in your employee table. You may find the information at the link below beneficial in understanding ANSI SQL92 and MySQL reserved words. Using a reserved word, can really wreck havoc in queries and coding. I don't know that this is your problem, but would start there.

http://www.mysql.com/doc/en/Reserved_words.html

Best,
Tbull