[wp-hackers] help with custom query results

[Mike Schinkel]

Hi Brian,

> The item that returns as "resultsrow" needs to be exploded into an array
> with a comma delimiter but I'm having difficulties doing so.


Yes, you would have difficulties.  wp_rg_lead_detail.value is free-form user-entered text and while I generally applauding using GROUP_CONCAT() it is not appropriate for free form text where the user many have entered a comma into the field.

What's more, the table design used by Gravity Forms is not relationally correct and is essentially impossible to process with only SQL as might be nice.  To get what (I think) you wanted you'd need to write a PHP script that handles all the details. I thought it would be a fun challenge to spend 15 minutes doing it for you; 3 hours later I now have a solution. Your luck is that at times I can be persistent in solving a problem.

Take the code you find here, copy to a file called something like "test.php" into the root of your website and then call it from your browser (i.e. go to http://yoursite.com/test.php):

http://pastebin.com/FSTGNz2i

From this example you should be able to get what you need.  

Hope this helps.

-Mike
P.S. In general I think the wp-hackers list is not really for this type of question even though I answered it.  If I'm wrong on this others will explain.


On Jun 2, 2010, at 8:41 PM, Brian Fidler wrote:

> I'm working with the following custom query:
> 
> $querystr = "
> SELECT wp_rg_lead.date_created,
> wp_rg_lead_detail.lead_id,
> wp_rg_lead_detail.form_id,
> wp_rg_lead_detail.field_number,
> group_concat(wp_rg_lead_detail.value ORDER BY
> wp_rg_lead_detail.field_number) AS resultsrow,
> wp_rg_lead.post_id,
> wp_rg_lead.source_url,
> wp_rg_lead.user_agent
> FROM wp_rg_lead_detail INNER JOIN wp_rg_lead ON wp_rg_lead_detail.lead_id =
> wp_rg_lead.id
> WHERE ( wp_rg_lead_detail.form_id = '3' )
> GROUP BY wp_rg_lead_detail.lead_id
> ";
> 
> $results = $wpdb->get_results($querystr, OBJECT);
> $resultscount = count($results);
> 
> ______________________________________________
> 
> The item that returns as "resultsrow" needs to be exploded into an array
> with a comma delimiter but I'm having difficulties doing so. If
> $resultscount = 4, what is the best way to iterate through "resultsrow" so
> that I end up with the following that I can then place in my layout?:
> 
> $result1[0]
> $result1[1]
> $result1[2]
> $result1[3]
> 
> $result2[0]
> $result2[1]
> $result2[2]
> $result2[3]
> 
> $result3[0]
> $result3[1]
> $result3[2]
> $result3[3]
> 
> $result4[0]
> $result4[1]
> $result4[2]
> $result4[3]
> 
> My final layout needs to be a number of rows with the fields pulled from
> "resultsrow".
> 
> thanks,
> brian
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers