sorting/displaying results

I sorted the second part of it by adding a req_num to the comments and just ordering by req_num

don't you hate it when you know what you want but can't get it to work properly

[edit]figured out the counter for it, thanks to php.net

$req_q = mysql_result(mysql_query("select count(req_num) from a_questions where req_type='q' and req_owner='$owner'"),0);
$req_a = mysql_result(mysql_query("select count(req_num) from a_questions where req_type='a' and req_owner='$owner'"),0);

$unanswered = ($req_q - $req_a);

not sure how correct it is but it works

now just have to figure out how to compare the results and display the numbers of items with unanswered questions, having the same number for q and a is messing me up ... *must think must think ...*
[/edit]

well i finally got the results I need, probably not the best way and am able to scrap the double db query above for the counter, problem now is I can't do much with these results, these results and what I have to compare them with are both in while statements so it looks like i'll have to add another while statement to compare them all. aghhhhhhhhhhhh

$i=0;
while ($row = mysql_fetch_array($result1))
{
$whole_result[$i] = $row;
$i++;
}
$a=0; $b=1;

do{
if(($whole_result[$a][req_item] == $whole_result[$b][req_item]) && ($whole_result[$a][req_num]== $whole_result[$b][req_num]))
{
$a = $a + 2;
$b = $b + 2;
}else{
echo $whole_result[$a][req_item]."";
$a = $b;
$b = $b+1;
}// echo "\$a = $a";
//echo "\$b =$b";
}while($a < $i);
//echo $i;

*maybe this is all a bad dream, maybe when I wake up the last month or so hasn't been horrible code but really under a palm tree soaking up the rays with drink and hand and zzzzzzzzzzz*

I wish I could have put brain time to helping with this, Busy, I'm really glad you're getting it sorted. If you don't finish in the next few days I may have some freed up brain space.

I've got a little time right now, so I'm gonna start the marching the gray cells in circles.

Can you post a dump of the table structure? (a create table query)

I tried but I'll have to do it in the morning, PC run out of memory again and causing errors and I need some sleep.

It wont make much sense with just one query, the second while statement where I need to put the results of the above is made up of two different db queries and one of those tables is big

the .php page is about 150 lines of retrieve and display with 4 db queries (plus the 2 i made for the counter which I havent removed yet) and are all different tables.

so what info you want ?

Thanks

might be easier if you just came over, bring your suntan lotion, hot and muggy here

Do you have both the questions for the form and the answers the user supplied in the same table?

Such as: "req_type='a', req_num=1" is an answer for "req_type='q', req_num=1" ?

yep, but also have req_type='c', req_num='2'

q = question
a = answer
c = comment (which has no answer)

here is the table with some data, hopefully you'll understand how it works.

#
# Dumping data for table 'item_request'
#

INSERT INTO item_request VALUES ('100002',2,4,'does this work? or you shaking my chain',20031222125941,1,'q');
INSERT INTO item_request VALUES ('100002',2,3,'does it have a hammer or is the hammer included?',20031222125954,2,'q');
INSERT INTO item_request VALUES ('100002',2,2,'could it work?',20031222130055,1,'a');
INSERT INTO item_request VALUES ('100002',2,2,'ask or answer when logged in?',20031222130047,3,'c');
INSERT INTO item_request VALUES ('100002',2,2,'answer or question',20031222134751,4,'c');
INSERT INTO item_request VALUES ('100002',2,1,'busy asked a question',20031222130031,5,'q');
INSERT INTO item_request VALUES ('100002',2,2,'which one?',20031222130022,5,'a');
INSERT INTO item_request VALUES ('100002',2,3,'who put the cat in the bag?',20031222130012,6,'q');
INSERT INTO item_request VALUES ('100002',2,2,'dont you mean cat in the hat you dummy',20031222130004,6,'a');
INSERT INTO item_request VALUES ('100037',2,4,'would you except candy',20031222141000,1,'q');
INSERT INTO item_request VALUES ('100037',2,2,'probably',20031222173525,2,'q');

I have some more questions. I'm still unsure how this table is used.

Can a question have more than one answer?
What is the difference bewteen req_owner and req_user?
What is req_item?
Is there no primary key or auto_number?

question can only have one answer
req_owner is the owner of the item (req_item) and req_user is the person asking
req_item is the items number
no primary or auto_number as there could be thousands of req_items and thousands of req_user(s) and have no control over what is asked when.

think of it this way, you manage a section of this forum called 'PHP', your the only one that can post items in that section but anyone can ask about them.
the items you post are listed by id num - req_item
you are the owner (req_owner) but the database table is also used by other people with other sections. - different req_owner number.
if someone asks a question, the question is numbered (req_num) and type (req_type) set to 'q' (for question) and their id number (req_user) to idenify them.
if you add a comment (say you forgot to mention something in last answer) the comment is numbered (req_num) and type (req_type) is set to 'c' (c for comment).

req_item //item number
req_owner // person whose item it is
req_user //user id of person asking question (or your own if comment)
req_text // the actual text of the question, answer or comment
req_date //date posted
req_num //question/answer/comment number
req_type // type: q = question, a = answer, c = comment

does this help?

i think sorting would be faster in the db... how are you calling them? maybe the call to the db could be three fold, once for those that are questions, once for those that are answers, and once for comments.
then just test the next thing in the array while going to display and if it's right for that display it.

calling them to sort (unanswered) is just select everything from table where != c (c=comment) order by req_num

Biggest problem I have now, using the code I already have is sorting two totally different arrays and outputting as seperate variables instead of another array. the final output where these variables have to go are in a while statement so I'm going to have to rewrite that or work out a way to compare them in the while loop. I'm getting to many loops, have to combine the results somehow.

I should be totally mad by the end of this whahahaha <-evil laugh

unfortunately the hd crash i had has had me recovering from that for the better part of a month, so i'm not quite back to form

can a question have more than one comment?

This is what I'm thinking right now:

one answer per question and the comments are just thrown in there, not directly related (via code or numbers) to any questions or answers.

Q. what colour is the sky?
A. the sky is blue
C. there are also clouds in the sky
Q. are there clouds in the sky?
A. I just said that, read the comment
Q. Is it raining
...

can't answer if no question is there, so use comments instead.

I'm having a hard time wrapping my head around this.

I'm also wondering if this project may be easier with multiple tables.

i'm in agreement with mark. i was hoping it was me faling outta touch since i had been recovering for a little more than two weeks and did no scripting for about 3 and a half while diagnosing and restoring the comp to a point i could recover data.

if i have it correct you have an easy way to break up the data to two tables and then to display what you want
table1: q&a
table2: comments

each q has a id number. using the id number you look up the comments.

two calls.

call one gets the informationon the q&a table. call two is part of the display fuformating function, it queries for associated comments and then adds that to the question and possible answer.

then again, i might be completely off in what i think you're asking for.

I looked at 2 tables, slightly differently thou, table1 questions and table 2 answers and comments.
But the same problem exists.
It's hard to explain it seems, but anyway I try this I'm always left with an array being printed (echo'd) to the screen in some sort of loop, to which I can't compare to the results of the 2 arrays loop.

this has been driving me crazy so if it makes no sense its just me going nuts

what I need is impossible I am beginning to think.
my latest angle is I need the results of the above mentioned (first few posts) loop sorted into seperate variables (not in an array) and compared to the array in the second loop but then it would entail a whole heap of checks.

if a value in loop 2 equals a value in loop 1 variable equals true - sounds pretty easy, BUT then if there are more than one for each value it should only display one but count the amount - confused? I am.

for pagination, how about these tables:

table q&a

qid
question
answer
qauth
aauth
qpostdate
apostdate

table comments
cid
qid
comment
commauth
commpostdate

and the script has this basic outline:

connect to sql
find the start point for the page
while you get the questions for the page
split qid to a variable
format question and possible answer
start new table cell that scroll the overflow for the comments
using the qid, find all comments with the qid
while retrieveing the comments (in order by cid)
put the comments in the comment cell
end inner for loop
close out question table
end outer for loop
display data

only possible issue is that each question is a table in that senario, possibly with an inner table for the comments.

Semi-similarly, I was thinking about joining them....

Using m3rajk's tables:

I think I'm missing something here, either that or I am still in last year

Give me a couple of days (have to recover from 2003 and have gallery display tomorrow), I'll try upload a few things, a working, or semi working example might help.

Thanks all, happy new year

[edit extra] I got it working, used a combination of stuff I'd posted (in different order) and it works, probably not the best way to do it or the cleaniest but it works

Thanks to all that helped[/edit]

[edit] Tomorrow was hot, so hot i got a case of sunstroke, we live and learn [/edit]