RE: [PHP] in a logic loop!
sweet solution.
:)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
t]On Behalf Of Marek Kilimajer
Sent: Tuesday, September 24, 2002 1:44 PM
To: PHP
Subject: Re: [PHP] in a logic loop!
Do it after the loop finishes
while( ){
if( something ) {
print out;
reinicialize
}
}
print out
Naintara Jain wrote:
>I have a logic problem:
>
>I have a complicated query. The main thing is that I have an ID, and for a
>particular ID, I may have between 1 to 3 rows returned. The rows returned
>are variable, that is for 1 particular ID there might just be 1 row and at
a
>later time it may have 3 rows.
>
>But what I need to do is print only one row for a particular ID. Please do
>not suggest I do it through SQL itself, because it's after using various
>aggregate functions that I have arrived at the min. result set. I need the
>logic clarified, because it has me stumped. It will be simple for a
>clear-thinking individual.
>
>What I am doing is:
>I maintain a set of details in say $prev_row (previous row) and another in
>cur_row (current row).
>The minute my cur_row detail (one unique id) doesn't match the prev_row
>detail, I print out all the previous row details, and reinitialize the
>various variables (all in a loop).
>
>This works fine for all cases but fails for the last ID, because of the
>logic used. Do I need to create a special case for the last one, or can
>anyone suggest a better way?
>
>-Thanks
>Naintara
>
>
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] in a logic loop!
SELECT DISTINCT * FROM table WHERE . Check out the MySQL manual for more help, or post yout existing query for me to gawk at :) HTH Justin on 24/09/02 11:24 PM, Naintara Jain ([EMAIL PROTECTED]) wrote: > Thank you Chris. > > I agree with you wholeheartedly that the SQL should be optimized. > Now, I don't want to freeload on your time, but I have a very complex query, > which I have been pondering over for the last many days. > If anyone can tell me what query will return just one row for the problem I > have, s/he will be SQL Guru to me. > > My problem is: > I have 3 tables (given below). A member of the staff takes courses and for > each course he has requirement id, these details get stored in the > staff_courses table. Each course taken will have a requirement specification > which could be any of 4 values given below (after the reqs table). I need to > get totals for each requirement for each member of the staff. I am using > MySQL (and cannot use sub-queries). I have been able to get the requirement > totals for each member using Group By reqid, but I get a separate row for > each requirement against the member of staff. So if a member has taken 3 > courses, and one course has a reqid of 0 and the other two courses have a > reqid of 1, I get 2 rows in the result set for that staffid, one will > contain the reqid 0 and total as 1, while the second row will contain the > reqid as 1 and the total as 2. > > Can I get the totals for each requirement in one row and can I do this in > one query? > > 1)"staff" table > staffid > branchid > > 2)"staff_courses" table > courseid > staffid > reqid > > 3)"reqs" table > reqid > requirement > > contains the > reqid requirement > 0 No action > 1 Primary requirement > 2 Further requirement > 3 Completed > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > t]On Behalf Of Chris Hewitt > Sent: Tuesday, September 24, 2002 1:49 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP] in a logic loop! > > > Naintara Jain wrote: > >> What I am doing is: >> I maintain a set of details in say $prev_row (previous row) and another in >> cur_row (current row). >> The minute my cur_row detail (one unique id) doesn't match the prev_row >> detail, I print out all the previous row details, and reinitialize the >> various variables (all in a loop). >> >> This works fine for all cases but fails for the last ID, because of the >> logic used. Do I need to create a special case for the last one, or can >> anyone suggest a better way? >> > When you come out of your loop, either there will be one row left to > print, or there were no matching rows. Suggestion one is to test for > this and if there were matching rows, do a special case for the last row. > > Alternatively, you are printing a row when the ID changes, that is, the > last row of the 1 to 3 matching rows. Why not print the first one, not > last one? You change the logic > to print the first row then ignore others with the same ID. It could be > that (because of an Order By clause) the last row is the correct one and > the data may differ between rows with the same ID. > > You say you do not want to do it in the SQL statement itself, but this > really is the right place. If MySQL use a LIMIT clause to only get one > row, if you cannot refine the SQL statement to only retrieve one row. > > HTH > Chris > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] in a logic loop!
Thank you Chris. I agree with you wholeheartedly that the SQL should be optimized. Now, I don't want to freeload on your time, but I have a very complex query, which I have been pondering over for the last many days. If anyone can tell me what query will return just one row for the problem I have, s/he will be SQL Guru to me. My problem is: I have 3 tables (given below). A member of the staff takes courses and for each course he has requirement id, these details get stored in the staff_courses table. Each course taken will have a requirement specification which could be any of 4 values given below (after the reqs table). I need to get totals for each requirement for each member of the staff. I am using MySQL (and cannot use sub-queries). I have been able to get the requirement totals for each member using Group By reqid, but I get a separate row for each requirement against the member of staff. So if a member has taken 3 courses, and one course has a reqid of 0 and the other two courses have a reqid of 1, I get 2 rows in the result set for that staffid, one will contain the reqid 0 and total as 1, while the second row will contain the reqid as 1 and the total as 2. Can I get the totals for each requirement in one row and can I do this in one query? 1)"staff" table staffid branchid 2)"staff_courses" table courseid staffid reqid 3)"reqs" table reqid requirement contains the reqid requirement 0 No action 1 Primary requirement 2 Further requirement 3 Completed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] t]On Behalf Of Chris Hewitt Sent: Tuesday, September 24, 2002 1:49 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PHP] in a logic loop! Naintara Jain wrote: >What I am doing is: >I maintain a set of details in say $prev_row (previous row) and another in >cur_row (current row). >The minute my cur_row detail (one unique id) doesn't match the prev_row >detail, I print out all the previous row details, and reinitialize the >various variables (all in a loop). > >This works fine for all cases but fails for the last ID, because of the >logic used. Do I need to create a special case for the last one, or can >anyone suggest a better way? > When you come out of your loop, either there will be one row left to print, or there were no matching rows. Suggestion one is to test for this and if there were matching rows, do a special case for the last row. Alternatively, you are printing a row when the ID changes, that is, the last row of the 1 to 3 matching rows. Why not print the first one, not last one? You change the logic to print the first row then ignore others with the same ID. It could be that (because of an Order By clause) the last row is the correct one and the data may differ between rows with the same ID. You say you do not want to do it in the SQL statement itself, but this really is the right place. If MySQL use a LIMIT clause to only get one row, if you cannot refine the SQL statement to only retrieve one row. HTH Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] in a logic loop!
Naintara Jain wrote: >What I am doing is: >I maintain a set of details in say $prev_row (previous row) and another in >cur_row (current row). >The minute my cur_row detail (one unique id) doesn't match the prev_row >detail, I print out all the previous row details, and reinitialize the >various variables (all in a loop). > >This works fine for all cases but fails for the last ID, because of the >logic used. Do I need to create a special case for the last one, or can >anyone suggest a better way? > When you come out of your loop, either there will be one row left to print, or there were no matching rows. Suggestion one is to test for this and if there were matching rows, do a special case for the last row. Alternatively, you are printing a row when the ID changes, that is, the last row of the 1 to 3 matching rows. Why not print the first one, not last one? You change the logic to print the first row then ignore others with the same ID. It could be that (because of an Order By clause) the last row is the correct one and the data may differ between rows with the same ID. You say you do not want to do it in the SQL statement itself, but this really is the right place. If MySQL use a LIMIT clause to only get one row, if you cannot refine the SQL statement to only retrieve one row. HTH Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] in a logic loop!
Do it after the loop finishes
while( ){
if( something ) {
print out;
reinicialize
}
}
print out
Naintara Jain wrote:
>I have a logic problem:
>
>I have a complicated query. The main thing is that I have an ID, and for a
>particular ID, I may have between 1 to 3 rows returned. The rows returned
>are variable, that is for 1 particular ID there might just be 1 row and at a
>later time it may have 3 rows.
>
>But what I need to do is print only one row for a particular ID. Please do
>not suggest I do it through SQL itself, because it's after using various
>aggregate functions that I have arrived at the min. result set. I need the
>logic clarified, because it has me stumped. It will be simple for a
>clear-thinking individual.
>
>What I am doing is:
>I maintain a set of details in say $prev_row (previous row) and another in
>cur_row (current row).
>The minute my cur_row detail (one unique id) doesn't match the prev_row
>detail, I print out all the previous row details, and reinitialize the
>various variables (all in a loop).
>
>This works fine for all cases but fails for the last ID, because of the
>logic used. Do I need to create a special case for the last one, or can
>anyone suggest a better way?
>
>-Thanks
>Naintara
>
>
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
