steve_large

Hi Barry, OOPs! My bad. Must have been a cut and paste error. Everything seems to be working now. There are no middle names though as I guess you can't really include that as it is a variable thing. Tried using Stephen William Large and got Stephen L. Great stuff anyway as I could never seem to get both things working at the same time and my names would come out as Stephen W. Instead. Thanks, Steve

Hi Barry, Just getting back at it now. The quad replace was to cut all of the prices off of the end of the events. The tournament organizers didn't want people to publicly see what others had paid for events especially when we had different prices for early bird applications. Also, there needed to be last initial only on the names because of wanting to have privacy for the applicants. I tried your code and it worked fine for 18 but 5 remained unaffected. Could be because I have that join in there somehow. This seemed to be the problem that I had been having before. I need the join in order to restrict people on the list to only the people who's PayPal transactions have completed. Here is the whole thing SELECT e.entry_id, field_id, CASE field_id WHEN 5 THEN CONCAT(SUBSTRING_INDEX(value, ' ',1), ' ', LEFT(SUBSTRING_INDEX(value,' ', -1), 1)) WHEN 18 THEN TRIM(SUBSTRING_INDEX(value, '-', 1)) ELSE value END as value FROM wp_wpforms_entry_fields f JOIN wp_wpforms_entries e ON f.entry_id = e.entry_id WHERE field_id IN (5,16,13,18) AND e.status='completed' ORDER BY e.entry_id DESC; as I have inserted your code below. Did I make a mistake or typo somewhere? Steve

Hi Barry, Still struggling with this member list code. Managed to get the prices to strip off but it was a bit of a shotgun cure as it effects the whole table. Not too elegant but it worked. Lucky for me the "Events" column is the only one that has the characters that I wanted gone. I was wondering if you had any ideas about how to edit the names for first name last initial in the MySQL. It seems to me that it may be the best route to pre-process them before sending them off to the PHP segment of the code. Had tried several alternatives but could not get Gizmodo's code to work in the PHP or get the MySQL that I tried to integrate into what was already working. It needs to have the last element in the string as a single character with a period no matter how long the string is. Please see what I have so far below: <?php header("Content-Type: text/html; charset=ISO-8859-1"); ?> <?php $db = mysqli_connect("localhost", "root", "", "bcwtf_tournaments"); $res = $db->query("SELECT e.entry_id, field_id, REPLACE(REPLACE(REPLACE(REPLACE(value, '- &#36;', ''), '75.00', ''),'90.00',''),'100.00','') AS value FROM wp_wpforms_entry_fields f JOIN wp_wpforms_entries e ON f.entry_id = e.entry_id WHERE field_id IN (5,16,13,18) AND e.status='completed' ORDER BY e.entry_id DESC; "); $headings= [ 5 => 'Name', 16 => 'Belt Rank' , 13 => 'TKD School', 18 => 'Events' ]; $temp_array = array_fill_keys(array_keys($headings), ''); // array for each attendee to be filled in from query results // process query results and place in array with attendee as the key $data = []; foreach ($res as $r) { if ( !isset($data[$r['entry_id']])) { $data[$r['entry_id']] = $temp_array ; } $data[$r['entry_id']][$r['field_id']] = $r['value'] ; // store answer in its array position } $theads = "<tr><th>" . join('</th><th>', $headings) . "</th></tr>\n" ; $tdata = ''; foreach ($data as $d) { $tdata .= "<tr><td>" . join('</td><td>', $d) . "</td></tr>\n"; } ?> <table border='1' style='border-collapse: collapse; width: 950px;'> <?=$theads?> <?=$tdata?> </table><br> <?php echo 'Total Competitors: ' . count($data);?> As always thanks very much for your help. Hope you are staying safe and well in these strange times, Steve

Hi Gizmola, Thanks for your input. Can this be done using a MySQL statement and an ID number. All values are in the same column named "value" and have different ID numbers. ID #5 contains both first and last name. The other problem is ID #18 which contains all of the event information. I need to have the names come out something like "Steve L." instead of say " Steve Large" and the events to come out as "Sparring" not "Sparring - $75.00" as the string is in the table. Then I will be done. Being a newbie and not so great at code I have tried several things but have not gotten it to work for me. Any help you could render would be appreciated, Steve

Hi Barry, Made it home OK from Victoria and tried your code out in WordPress. Works great! So much less code than the original stuff I was working with from WPForms. Thanks a bunch! Last but not least must now try to work on some PHP that will trim the last name down to an initial plus a period like "Steve L.". Also had to take off the prices for the events in the events column (both were privacy issues that the tournament organizers had requested). Will try to adapt some of the code from the WPForms PHP and hope I can get it to work with the code you gave me which I like a lot more. Here is what I had for the WPForms code. Not too elegant but it worked: if ($entry_field['id'] == 5) { $names = explode(' ', $data); $lastName = array_pop($names); $data = implode(' ', $names).' '.$lastName[0].'.'; } else if ($entry_field['id'] == 18) { $data = preg_replace("/[^a-zA-Z' ']/", "", $data); $data = rtrim($data, " "); if ($data == "Sparring Poomsae and Freestyle Poomsae") { $data = "Sparring, Poomsae and Freestyle Poomsae"; } } Cheers! Steve

Hi Barry, Thanks again? Had tried the Select entry_id before but got an "ambiguous" error. Must go for cancer treatments in Victoria B.C. now and it takes about 2 hours to get there. Not ignoring you. Thank you for all your help so far. Still need to work on getting the output to show up in WordPress when I get back if I am not too tired. Will get back to you when I return. Steve

Hi Barry, Very nice job! Can I still use my modification in the MySQL statement to restrict the entries to only those with a "completed" status. In order to do this I would still need to initiate a join somehow with the "wp_wpforms_entries" table based on the output from the "status" column? Steve

Hi Barry, Thanks for the quick response. I know that the MySQL is correct just can't seem to get the PHP to work. It doesn't seem to error out or cause any problems when I run the script there is just no output. Perhaps it has something to do with the joins where it calls "name" as it does not exist in a separate table anymore but is now in the "value" column of wp_wpforms_entry_fields with it's own ID. Have tried to get rid of the old joins in some of the statements but can't quite get the PHP right as they always seem to error out. Steve

Hi Barry, Long time no talk to. Hope you are keeping safe in your part of the world what with all the bad stuff going on everywhere. I have had to change to a different tournament registration program (WPForms) due to the other program being dumped as regards upgrades and support. I was hoping that I could adapt the code that you had done for me last time but have gotten a bit stuck with the PHP. I have had some success with the MySQL as regards getting the output I need. The difference in the two programs is that you have the name field contained in the same table and table column as all the other answers with it's own ID. In our last example it was concatenated to the other answers using a join because the name fields were in a separate table. I don't think that it should be as complex with this program as everything is all in the one column and just needs to be organized into a table somehow. I wanted a way that I could stop people from being added to the main list if they bailed out of the PayPal transaction. The trouble is that in the WPForms list creation code once you have pushed the submit button it adds you to the list even if the PayPal transaction doesn't go through. The list creator PHP is free to the public but is not supported by WPForms even though it is on their website as being the one to use. I was able to modify the code I got from WPForms somewhat but it is way over my head for the most part. I thought of your code right away as I knew I could probably filter the entries with MySQL. Below is the modified WPForms table: /** * Custom shortcode to display WPForms form entries. * * Basic usage: [wpforms_entries_table id="FORMID"]. * * Possible shortcode attributes: * id (required) Form ID of which to show entries. * user User ID, or "current" to default to current logged in user. * fields Comma seperated list of form field IDs. * * @link https://wpforms.com/developers/how-to-display-form-entries/ * * @param array $atts Shortcode attributes. * * @return string */ <?php function wpf_entries_table( $atts ) { // Pull ID shortcode attributes. $atts = shortcode_atts( [ 'id' => '', 'user' => '', 'fields' => '', ], $atts ); // Check for an ID attribute (required) and that WPForms is in fact // installed and activated. if ( empty( $atts['id'] ) || ! function_exists( 'wpforms' ) ) { return; } // Get the form, from the ID provided in the shortcode. $form = wpforms()->form->get( absint( $atts['id'] ) ); // If the form doesn't exists, abort. if ( empty( $form ) ) { return; } // Pull and format the form data out of the form object. $form_data = ! empty( $form->post_content ) ? wpforms_decode( $form->post_content ) : ''; // Check to see if we are showing all allowed fields, or only specific ones. $form_field_ids = ! empty( $atts['fields'] ) ? explode( ',', str_replace( ' ', '', $atts['fields'] ) ) : []; // Setup the form fields. if ( empty( $form_field_ids ) ) { $form_fields = $form_data['fields']; } else { $form_fields = []; foreach ( $form_field_ids as $field_id ) { if ( isset( $form_data['fields'][ $field_id ] ) ) { $form_fields[ $field_id ] = $form_data['fields'][ $field_id ]; } } } if ( empty( $form_fields ) ) { return; } // Here we define what the types of form fields we do NOT want to include, // instead they should be ignored entirely. $form_fields_disallow = apply_filters( 'wpforms_frontend_entries_table_disallow', [ 'divider', 'html', 'pagebreak', 'captcha' ] ); // Loop through all form fields and remove any field types not allowed. foreach ( $form_fields as $field_id => $form_field ) { if ( in_array( $form_field['type'], $form_fields_disallow, true ) ) { unset( $form_fields[ $field_id ] ); } } $entries_args = [ 'form_id' => absint( $atts['id'] ), ]; // Narrow entries by user if user_id shortcode attribute was used. if ( ! empty( $atts['user'] ) ) { if ( $atts['user'] === 'current' && is_user_logged_in() ) { $entries_args['user_id'] = get_current_user_id(); } else { $entries_args['user_id'] = absint( $atts['user'] ); } } // Get all entries for the form, according to arguments defined. $entries = wpforms()->entry->get_entries( $entries_args ); if ( empty( $entries ) ) { return '<p>No entries found.</p>'; } ob_start(); echo '<table class="wpforms-frontend-entries">'; echo '<thead><tr>'; // Loop through the form data so we can output form field names in // the table header. foreach ( $form_fields as $form_field ) { // Output the form field name/label. echo '<th>'; echo esc_html( sanitize_text_field( $form_field['label'] ) ); echo '</th>'; } echo '</tr></thead>'; echo '<tbody>'; // Now, loop through all the form entries. foreach ( $entries as $entry ) { echo '<tr>'; // Entry field values are in JSON, so we need to decode. $entry_fields = json_decode( $entry->fields, true ); foreach ( $form_fields as $form_field ) { echo '<td>'; foreach ( $entry_fields as $entry_field ) { if ( absint( $entry_field['id'] ) === absint( $form_field['id'] ) ) { $data = apply_filters( 'wpforms_html_field_value', wp_strip_all_tags( $entry_field['value'] ), $entry_field, $form_data, 'entry-frontend-table' ); if ($entry_field['id'] == 5) { $names = explode(' ', $data); $lastName = array_pop($names); $data = implode(' ', $names).' '.$lastName[0].'.'; } else if ($entry_field['id'] == 18) { $data = preg_replace("/[^a-zA-Z' ']/", "", $data); $data = rtrim($data, " "); if ($data == "Sparring Poomsae and Freestyle Poomsae") { $data = "Sparring, Poomsae and Freestyle Poomsae"; } } echo $data; break; } } echo '</td>'; } echo '</tr>'; } echo '</tbody>'; echo '</table>'; echo 'Total Competitors: ' . count($entries); $output = ob_get_clean(); return $output; } add_shortcode( 'wpforms_entries_table', 'wpf_entries_table' ); I found a table which contained the PayPal status and I am using that to determine which entries should show on the table. I have tried the MySQL in PHP admin and it gives me the output that I want. This is what I have so far. I know the php is incorrect as "name" is not in another table anymore but the MySQL statement should work. <?php header("Content-Type: text/html; charset=ISO-8859-1"); ?> <?php $db = mysqli_connect("mydomaincommysql.com", "steve", "password", "tournaments"); $res = $db->query("SELECT field_id, value FROM wp_wpforms_entry_fields JOIN wp_wpforms_entries ON wp_wpforms_entry_fields.entry_id = wp_wpforms_entries.entry_id WHERE field_value IN (5, 16, 13, 18) AND wp_wpforms_entries.status='completed' "); $headings = [ 5 => 'Name' , 16 => 'Belt' , 13 => 'School', 18 => 'Events' ]; $temp_array = array_fill_keys(array_keys($headings), ''); // array for each attendee to be filled in from query results // process query results and place in array with attendee as the key $data = []; foreach ($res as $r) { if ( !isset($data[$r['field_id']])) { $data[$r['field_id']] = [ 'value' => $temp_array ] ; } $data[$r['field_id']]['value'][$r['field_id']] = $r['value'] ; // store answer in its array position } echo $data; // now we can easily output the array into an html table $theads = "<tr><th>Name</th><th>" . join('</th><th>', $headings) . "</th></tr>\n"; $tdata = ''; foreach ($data as $att) { $tdata = "<tr><td>{$att['name']}</td><td>" . join('</td><td>', $att['value']) . "</td></tr>\n"; } ?> <table border='1' style='border-collapse: collapse; width: 900px;'> <?=$theads?> <?=$tdata?> </table> <?php echo 'Total Competitors:' . count($data); Here is the schema for the two tables: INSERT INTO `wp_wpforms_entry_fields` (`id`, `entry_id`, `form_id`, `field_id`, `value`, `date`) VALUES INSERT INTO `wp_wpforms_entries` (`entry_id`, `form_id`, `post_id`, `user_id`, `status`, `type`, `viewed`, `starred`, `fields`, `meta`, `date`, `date_modified`, `ip_address`, `user_agent`, `user_uuid`) VALUES Sorry, kind of a long message was just hoping that maybe you had some ideas on how I could solve this problem either with our old code or by some modification of the WPForms code. Any help you could offer would certainly be appreciated. All the best and stay safe and healthy, Steve

Sorry about that. <?php /** * Custom shortcode to display WPForms form entries. * * Basic usage: [wpforms_entries_table id="FORMID"]. * * Possible shortcode attributes: * id (required) Form ID of which to show entries. * user User ID, or "current" to default to current logged in user. * fields Comma seperated list of form field IDs. * * @link https://wpforms.com/developers/how-to-display-form-entries/ * * @param array $atts Shortcode attributes. * * @return string */ function wpf_entries_table( $atts ) { // Pull ID shortcode attributes. $atts = shortcode_atts( [ 'id' => '', 'user' => '', 'fields' => '', ], $atts ); // Check for an ID attribute (required) and that WPForms is in fact // installed and activated. if ( empty( $atts['id'] ) || ! function_exists( 'wpforms' ) ) { return; } // Get the form, from the ID provided in the shortcode. $form = wpforms()->form->get( absint( $atts['id'] ) ); // If the form doesn't exists, abort. if ( empty( $form ) ) { return; } // Pull and format the form data out of the form object. $form_data = ! empty( $form->post_content ) ? wpforms_decode( $form->post_content ) : ''; // Check to see if we are showing all allowed fields, or only specific ones. $form_field_ids = ! empty( $atts['fields'] ) ? explode( ',', str_replace( ' ', '', $atts['fields'] ) ) : []; // Setup the form fields. if ( empty( $form_field_ids ) ) { $form_fields = $form_data['fields']; } else { $form_fields = []; foreach ( $form_field_ids as $field_id ) { if ( isset( $form_data['fields'][ $field_id ] ) ) { $form_fields[ $field_id ] = $form_data['fields'][ $field_id ]; } } } if ( empty( $form_fields ) ) { return; } // Here we define what the types of form fields we do NOT want to include, // instead they should be ignored entirely. $form_fields_disallow = apply_filters( 'wpforms_frontend_entries_table_disallow', [ 'divider', 'html', 'pagebreak', 'captcha' ] ); // Loop through all form fields and remove any field types not allowed. foreach ( $form_fields as $field_id => $form_field ) { if ( in_array( $form_field['type'], $form_fields_disallow, true ) ) { unset( $form_fields[ $field_id ] ); } } $entries_args = [ 'form_id' => absint( $atts['id'] ), ]; // Narrow entries by user if user_id shortcode attribute was used. if ( ! empty( $atts['user'] ) ) { if ( $atts['user'] === 'current' && is_user_logged_in() ) { $entries_args['user_id'] = get_current_user_id(); } else { $entries_args['user_id'] = absint( $atts['user'] ); } } // Get all entries for the form, according to arguments defined. $entries = wpforms()->entry->get_entries( $entries_args ); if ( empty( $entries ) ) { return '<p>No entries found.</p>'; } ob_start(); echo '<table class="wpforms-frontend-entries">'; echo '<thead><tr>'; // Loop through the form data so we can output form field names in // the table header. foreach ( $form_fields as $form_field ) { // Output the form field name/label. echo '<th>'; echo esc_html( sanitize_text_field( $form_field['label'] ) ); echo '</th>'; } echo '</tr></thead>'; echo '<tbody>'; // Now, loop through all the form entries. foreach ( $entries as $entry ) { echo '<tr>'; // Entry field values are in JSON, so we need to decode. $entry_fields = json_decode( $entry->fields, true ); foreach ( $form_fields as $form_field ) { echo '<td>'; foreach ( $entry_fields as $entry_field ) { if ( absint( $entry_field['id'] ) === absint( $form_field['id'] ) ) { $data = apply_filters( 'wpforms_html_field_value', wp_strip_all_tags( $entry_field['value'] ), $entry_field, $form_data, 'entry-frontend-table' ); if ($entry_field['id'] == 5) { //code to break apart first and last name, then add a period $names = explode(' ', $data); $lastName = array_pop($names); $data = implode(' ', $names).' '.$lastName[0].'.'; //output the name formatted as "Steve L." } else if ($entry_field['id'] == 18) { $data = preg_replace("/[^a-zA-Z' ']/", "", $data); $data = rtrim($data, " "); if ($data == "Sparring Poomsae and Freestyle Poomsae") { $data = "Sparring, Poomsae and Freestyle Poomsae"; } echo $data; } break; } } echo '</td>'; } echo '</tr>'; } echo '</tbody>'; echo '</table>'; $output = ob_get_clean(); return $output; } add_shortcode( 'wpforms_entries_table', 'wpf_entries_table' );

Oops! Forgot one table.

Hello, I would like show entries on a list based on their PayPal status. If it is "pending" I don't want them to show on the list. only if their status is "complete". I just recently switched over to WPForms for WordPress. They offer only a basic PHP file that creates a list but they do not support any modifications to that file. What would I need to do to this code to produce the results I need? Please see attached files and code pasted below. Thanks for your help, Steve <?php /** * Custom shortcode to display WPForms form entries. * * Basic usage: [wpforms_entries_table id="FORMID"]. * * Possible shortcode attributes: * id (required) Form ID of which to show entries. * user User ID, or "current" to default to current logged in user. * fields Comma seperated list of form field IDs. * * @link https://wpforms.com/developers/how-to-display-form-entries/ * * @param array $atts Shortcode attributes. * * @return string */ function wpf_entries_table( $atts ) { // Pull ID shortcode attributes. $atts = shortcode_atts( [ 'id' => '', 'user' => '', 'fields' => '', ], $atts ); // Check for an ID attribute (required) and that WPForms is in fact // installed and activated. if ( empty( $atts['id'] ) || ! function_exists( 'wpforms' ) ) { return; } // Get the form, from the ID provided in the shortcode. $form = wpforms()->form->get( absint( $atts['id'] ) ); // If the form doesn't exists, abort. if ( empty( $form ) ) { return; } // Pull and format the form data out of the form object. $form_data = ! empty( $form->post_content ) ? wpforms_decode( $form->post_content ) : ''; // Check to see if we are showing all allowed fields, or only specific ones. $form_field_ids = ! empty( $atts['fields'] ) ? explode( ',', str_replace( ' ', '', $atts['fields'] ) ) : []; // Setup the form fields. if ( empty( $form_field_ids ) ) { $form_fields = $form_data['fields']; } else { $form_fields = []; foreach ( $form_field_ids as $field_id ) { if ( isset( $form_data['fields'][ $field_id ] ) ) { $form_fields[ $field_id ] = $form_data['fields'][ $field_id ]; } } } if ( empty( $form_fields ) ) { return; } // Here we define what the types of form fields we do NOT want to include, // instead they should be ignored entirely. $form_fields_disallow = apply_filters( 'wpforms_frontend_entries_table_disallow', [ 'divider', 'html', 'pagebreak', 'captcha' ] ); // Loop through all form fields and remove any field types not allowed. foreach ( $form_fields as $field_id => $form_field ) { if ( in_array( $form_field['type'], $form_fields_disallow, true ) ) { unset( $form_fields[ $field_id ] ); } } $entries_args = [ 'form_id' => absint( $atts['id'] ), ]; // Narrow entries by user if user_id shortcode attribute was used. if ( ! empty( $atts['user'] ) ) { if ( $atts['user'] === 'current' && is_user_logged_in() ) { $entries_args['user_id'] = get_current_user_id(); } else { $entries_args['user_id'] = absint( $atts['user'] ); } } // Get all entries for the form, according to arguments defined. $entries = wpforms()->entry->get_entries( $entries_args ); if ( empty( $entries ) ) { return '<p>No entries found.</p>'; } ob_start(); echo '<table class="wpforms-frontend-entries">'; echo '<thead><tr>'; // Loop through the form data so we can output form field names in // the table header. foreach ( $form_fields as $form_field ) { // Output the form field name/label. echo '<th>'; echo esc_html( sanitize_text_field( $form_field['label'] ) ); echo '</th>'; } echo '</tr></thead>'; echo '<tbody>'; // Now, loop through all the form entries. foreach ( $entries as $entry ) { echo '<tr>'; // Entry field values are in JSON, so we need to decode. $entry_fields = json_decode( $entry->fields, true ); foreach ( $form_fields as $form_field ) { echo '<td>'; foreach ( $entry_fields as $entry_field ) { if ( absint( $entry_field['id'] ) === absint( $form_field['id'] ) ) { $data = apply_filters( 'wpforms_html_field_value', wp_strip_all_tags( $entry_field['value'] ), $entry_field, $form_data, 'entry-frontend-table' ); if ($entry_field['id'] == 5) { //code to break apart first and last name, then add a period $names = explode(' ', $data); $lastName = array_pop($names); $data = implode(' ', $names).' '.$lastName[0].'.'; //output the name formatted as "Steve L." } else if ($entry_field['id'] == 18) { $data = preg_replace("/[^a-zA-Z' ']/", "", $data); $data = rtrim($data, " "); if ($data == "Sparring Poomsae and Freestyle Poomsae") { $data = "Sparring, Poomsae and Freestyle Poomsae"; } echo $data; } break; } } echo '</td>'; } echo '</tr>'; } echo '</tbody>'; echo '</table>'; $output = ob_get_clean(); return $output; } add_shortcode( 'wpforms_entries_table', 'wpf_entries_table' );

Hi Barry, Thanks that worked well! Not what I would have thought that I should have used. Nice, now I can use this as a template for any of the other tournament jobs that usually arise three or four times a year. Cheers! Steve

Hi Barry, I was just thinking today what if there were numbers inside the question_id range that I didn't want to include in my list? If you use "BETWEEN" in the query it includes everything between the two numbers and at the end of the PHP code will print everything out. What if I have a range of say 46-51 but I only want to print out 46,47, and 51? Steve