[SOLVED] Need help with larger query

[skyer2000]

Here's my current query, I can't figure this out:

 

SELECT   conf_review_fields.fieldid    AS fieldid,
         conf_review_fields.fieldtype  AS fieldtype,
         conf_review_fields.parentid   AS parentid,
         conf_review_fields.fieldvalue AS fieldvalue
FROM     conf_review_fields,
         conf_review_form
WHERE    conf_review_form.topid = ?
         AND conf_review_form.fieldid IN (conf_review_fields.fieldid,conf_review_fields.parentid)
ORDER BY conf_review_form.fieldorder ASC

 

The relevant database structure for conf_review_fields and conf_review_form

 

CONF_REVIEW_FIELDS

fieldid  	mediumint(    	   	 
fieldtype 	tinyint(3)	  	  	 
parentid 	mediumint(  	  	  	 
fieldvalue 	varchar(150)

 

CONF_REVIEW_FORM

topid  	mediumint(  	    	   	 
fieldid 	mediumint(	  	  	 
fieldorder 	smallint(5)

 

 

Running the above query with ? = 1, I get the following results:

 

fieldid 	fieldtype 	parentid 	fieldvalue
1 	1 	0 	Clarity of Objectives
2 	1 	0 	Conceptual/Theoretical Rigor
13 	3 	10 	Publish with major revisions
12 	3 	10 	Publish with minor revisions
11 	3 	10 	Publish as is
10 	3 	0 	Recommendation
15 	3 	10 	Not suitable
14 	3 	10 	Revise and resubmit

 

 

What needs to happen though, is that when the fieldtype = 3, with the parentid = 0, it then has other fields that reference that parentid (for example, all of the 10s).

 

Is there a way to modify the query, using Group_concat or something else, that can pull those values into something I can loop through whenever a fieldtype = 3 and parentid = 0 is found?

[JonnoTheDev]

Why not order by parentid

[skyer2000]

I can order by that, but then there is no guarantee the fieldtype = 3 and the associated parentids will fall in the same place

[JonnoTheDev]

Its difficult for me to picture what you want the result set to look like. I'm guessing

 

pk  parent

1  0

2  1

3  1

4  0

5  4

6  4

7  0

8  7

[skyer2000]

fieldid    fieldtype    parentid    fieldvalue
1    1    0    Clarity of Objectives
2    1    0    Conceptual/Theoretical Rigor
10    3    11,12,13,14,15    Recommendation

 

So this way, when the fieldtype = 3 (a dropdown menu), I can then build out the option values.

 

[JonnoTheDev]

impossible with a single query. build an array using your programming logic

[skyer2000]

Alright that I can do! Just had to make sure it was the only way...

 

Thanks!