Retrieve and display data from table using Shortcode EXEC PHP WordPress
- SOLVED
I have a form using Gravity Forms that collects first name, last name, city and state on my wordpress site. I want to then display that data in a table on a wordpress page, sorted by last name.
I started using the Shortcode EXEC PHP plugin to create the code to retrieve/display the data, but I just can't seem to get it to work.
I need this completed by Wed July 7.
Update: Here is the code that I was trying to get to work:
global $wpdb;
$results = $wpdb->get_results("SELECT f.id, f1.value as first_name, f2.value as last_name, f3.value as city, f4.value as state, FROM ".$wpdb->wp_."rg_lead as f INNER JOIN ".$wpdb->wp_."rg_lead_detail as f1 ON (f.id = f1.lead_id AND f1.field_number = '1') INNER JOIN ".$wpdb->wp_."rg_lead_detail as f2 ON (f.id = f2.lead_id AND f2.field_number = '2') INNER JOIN ".$wpdb->wp_."rg_lead_detail as f3 ON (f.id = f3.lead_id AND f3.field_number = '3') INNER JOIN ".$wpdb->wp_."rg_lead_detail as f4 ON (f.id = f4.lead_id AND f4.field_number = '4') WHERE f.form_id = '4');
foreach ($results as $result){
return .$result['first_name']." ".$result['last_name']."";
return .$result['city']." ".$result['state']."------------";
}
Notice all four fields have the same lead_id, so you'll need to filter by that, plus only grab those fields that are form_id=4.
The last part is what was failing - when it was trying to output the results. Although I need to the results in an html table, so that part needs to be changed anyway.
And perhaps this is not the most efficient SQL query, so if you can improve it, that would be great too.
Since I'm really out of my league here, I'll need all the code provided for me.
Attached is a screenshot of the db table screenshot and dump
Answers (1)
Oleg Butuzov answers:
no need to use external plugin for this...
simple add your shortcode and return all records in shortcode
add_shortcode( 'shortcodename' , 'shortcode_callback' );
function shortcode_callback($atts, $content){
gloabl $wpdb;
// in array your shortcodes attributes
extract(shortcode_atts(array('show' => 'form'), $atts));
switch($atts['attname']){
case 'some':
$results = $wpdb->get_results("SQL SELECT STATMENT GOSE HERE");
$content = '<table>';
foreach($results as $k=>$i){
$content .= 'some html code + database values gose here';
}
$content .= '</table>';
break;
}
return "{$content}";
}
Oleg Butuzov comments:
I can explain code and answer on your questions.
Cheers.
Oleg Butuzov comments:
if you can provide scheme or dump of gravity forms table, i can help with sql you need.
so left only html part.
Oleg Butuzov comments:
first amendment...
f4.value as state<strong>,</strong> FROM
remove comma before FROM
Oleg Butuzov comments:
better example
$sql ="SELECT
f.id,
f1.value as first_name,
f2.value as last_name,
f3.value as city,
f4.value as state
FROM {$wpdb->prefix}rg_lead as f
INNER JOIN {$wpdb->prefix}rg_lead_detail as f1 ON (f.id = f1.lead_id AND f1.field_number = '1')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f2 ON (f.id = f2.lead_id AND f2.field_number = '2')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f3 ON (f.id = f3.lead_id AND f3.field_number = '3')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f4 ON (f.id = f4.lead_id AND f4.field_number = '4')
WHERE f.form_id = '4'";
but still... i am not sure you need so much inner joins... with dump imagination can work better...
Oleg Butuzov comments:
SELECT
f.id,
f1.value as first_name,
f2.value as last_name,
f3.value as city,
f4.value as state
FROM {$wpdb->prefix}rg_lead as f
INNER JOIN {$wpdb->prefix}rg_lead_detail as f1 ON (f.id = f1.lead_id AND f1.field_number = '1')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f2 ON (f.id = f2.lead_id AND f2.field_number = '2')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f3 ON (f.id = f3.lead_id AND f3.field_number = '3')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f4 ON (f.id = f4.lead_id AND f4.field_number = '4')
WHERE f.form_id = '4'
ORDER BY f2.value DESC
Oleg Butuzov comments:
/*
to check this code. inser tit to the functions.php in your theme. and in a page where you whant to see insert [shortcodename] shortcode
*/
add_shortcode( 'shortcodename' , 'shortcode_callback' );
function shortcode_callback($atts, $content){
gloabl $wpdb;
$results = $wpdb->get_results("SELECT
f.id,
f1.value as first_name,
f2.value as last_name,
f3.value as city,
f4.value as state
FROM {$wpdb->prefix}rg_lead as f
INNER JOIN {$wpdb->prefix}rg_lead_detail as f1 ON (f.id = f1.lead_id AND f1.field_number = '1')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f2 ON (f.id = f2.lead_id AND f2.field_number = '2')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f3 ON (f.id = f3.lead_id AND f3.field_number = '3')
INNER JOIN {$wpdb->prefix}rg_lead_detail as f4 ON (f.id = f4.lead_id AND f4.field_number = '4')
WHERE f.form_id = '4'
ORDER BY f2.value DESC");
$content = '<table width="100%">';
foreach($results as $k=>$i){
$content .= "<tr><td>{$i->first_name}</td><td>{$i->last_name}</td><td>{$i->city}</td><td>{$i->state}</td></tr>";
}
$content .= '</table>';
}
return "{$content}";
}