MySQL Monster Query Issue

I'd think an outer join between form_save and field_save is what you're after, but I suspect I may be missing something...

What pompompom siad... you should be able to write a left outer join that'll do this.

(can you paste in, using pre tags, the, schema for these two tables?)

Thanks for any ideas!CREATE TABLE `form_save` ( `form_save_id` int(10) unsigned NOT NULL auto_increment, `form_save_form` int(10) NOT NULL default '0', `form_save_ip` varchar(20) NOT NULL default '', `form_save_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`form_save_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;Key: 'form_save_form': pointer to 'form' tableCREATE TABLE `form_save_field` ( `form_save_field_id` int(10) unsigned NOT NULL auto_increment, `form_save_field_save` int(10) NOT NULL default '0', `form_save_field_data` text NOT NULL, `form_save_field_field` int(10) NOT NULL default '0', `form_save_field_parent` int(10) NOT NULL default '0', `form_save_field_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`form_save_field_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1Key: 'form_save_field_save': pointer to 'form_save' 'form_save_field_data': saved data 'form_save_field_field': pointer to 'field' 'form_save_field_parent': pointer to another row in this table; if the field type is "checkbox" with multiple possible answers, the various answers are added as additional rows while the original row has "checkbox" in the data field. In this way, if you parse the row and find "checkbox" you know to look for additional rows with a parent that matches your existing row. I do this to allow faster searching for specific answers of a multi-choice question.

Quick and dirty...SELECT fsf.*, ff.*FROM form_save_field AS fsfhttp://dev.mysql.com/doc/refman/5.0/en/join.html form_field AS ff ON (ff.field_id = fsf.form_save_field_field)WHERE fsf.form_save_field_save = $form_save_id;

Or... suppose you want all the answers from a particular date range [$date1, $date2). You could do it like this:SELECT fsf.*, ff.*FROM form_save_field AS fsfLEFT JOIN form_field AS ff ON (ff.field_id = fsf.form_save_field_field)LEFT JOIN form_save AS fs ON (fs.form_save_id = fsf.form_save_field_save)WHERE (fs.form_save_stamp >= $date1) AND (fs.form_save_stamp ORDER BY fs.form_save_id, fsf.form_save_field_id;>The point is, JOINs are good for more than adding columns to be returned! Really, in this case that second LEFT JOIN becomes an INNER JOIN because of the WHERE clause (NULL would always exclude the rows anyway). IIRC, all versions of MySQL >= 4 perform this optimization for you... but it's something to be aware of.

You're effectively using your database to store a database schema for your forms, as well as the data defined by that schema. That's probably going to make it hard to get query results with one form per row. But if you just do the queries you're already doing and bung in an ORDER BY to sort them according to the ID's the records are associated with, you should be able to generate your TSV output lines just by making one pass through each query, rather than looping and looking for matches.

On lack of preview: what sbutler said.

That is: if you use ORDER BY properly, you can make all the saved fields for a particular instance of a particular kind of form cluster together in your query result, and occur in a predictable order. Then all your TSV-generating code has to do is walk the query, spitting out fields in the order they occur in it, and adding an end-of-line every time the form instance ID changes. It would probably pay to add an extra numeric column that explicitly specifies the column number that the field concerned should occupy in a TSV report, and include that in the ORDER BY as well. That would make it super-easy to figure out how many tabs to emit between fields.