Jump to content

steve_large

Members
  • Posts

    19
  • Joined

  • Last visited

Everything posted by steve_large

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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' );
  11. 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' );
  12. 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
  13. 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
  14. Hi Barand, Wow! Thank you very much for your efforts. Really didn't expect to get such elegant coding back from anyone. Usually when you ask for answers on a lot of sites you are still taking a stab in the dark for the most part. Have spent at least a week over the Christmas holidays working on this one. Your coding is just what I am looking for. I tried your code on the live database and it worked great! I noticed when I cut and pasted that some odd characters came over from the blog window which caused some strange errors. I am using NetBeans IDE as my editor and it says everything looks good now. Just added the connection string and away it went - awesome! Sort of like having a late Christmas present. This really needs to be cited as a good approach for this sort of thing because you would think that it is a common issue for many people and the examples on the Net are very obscure, especially to someone like myself that has a limited knowledge of MySQL and PHP. Please check out the application of your code at this address: http://taekwondobc.com/winterfest/ Will now work on trying to get rid of those annoying extra commas that the plugin keeps appending at the end of the events on every row. All the best and a very Happy New Year! Steve
  15. Hi Barand, Thanks for the quick reply. There are two tables. The answer table and the attendee table. The answer table is quite simple with only a few columns - "registration_id", "question_id", and answer. The attendee table has many columns but the one that binds it to the answer table is "id" as it has the same values as answer.registration_id. Here is the schema for both tables: CREATE TABLE `wp_lvsz_evr_answer` ( `registration_id` int(11) NOT NULL DEFAULT '0', `question_id` int(11) NOT NULL DEFAULT '0', `answer` text NOT NULL, PRIMARY KEY (`registration_id`,`question_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `wp_lvsz_evr_attendee` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `registration_id` varchar(23) DEFAULT '0', `is_primary` tinyint(1) DEFAULT '0', `lname` varchar(45) DEFAULT NULL, `fname` varchar(45) DEFAULT NULL, `address` varchar(45) DEFAULT NULL, `address2` varchar(45) DEFAULT NULL, `city` varchar(45) DEFAULT NULL, `state` varchar(45) DEFAULT NULL, `zip` varchar(45) DEFAULT NULL, `reg_type` varchar(45) DEFAULT NULL, `country_id` varchar(128) DEFAULT NULL, `organization_name` varchar(50) DEFAULT NULL, `vat_number` varchar(20) DEFAULT NULL, `email` varchar(85) DEFAULT NULL, `phone` varchar(45) DEFAULT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `price_option` varchar(100) DEFAULT NULL, `orig_price` decimal(20,2) DEFAULT '0.00', `final_price` decimal(20,2) DEFAULT '0.00', `quantity` varchar(45) DEFAULT NULL, `attendees` mediumtext, `tickets` mediumtext, `total_cost` decimal(20,2) DEFAULT '0.00', `amount_pd` varchar(45) DEFAULT NULL, `coupon_code` varchar(45) DEFAULT NULL, `payment` varchar(45) DEFAULT NULL, `payment_status` varchar(45) DEFAULT NULL, `txn_type` varchar(45) DEFAULT NULL, `txn_id` varchar(45) DEFAULT NULL, `payment_date` varchar(30) DEFAULT NULL, `event_id` varchar(45) DEFAULT NULL, `event_time` varchar(15) DEFAULT NULL, `end_time` varchar(15) DEFAULT NULL, `start_date` varchar(45) DEFAULT NULL, `end_date` varchar(45) DEFAULT NULL, `attendee_session` varchar(250) DEFAULT NULL, `transaction_details` text, `pre_approve` int(11) DEFAULT '1', `checked_in` int(1) DEFAULT '0', `checked_in_quantity` int(11) DEFAULT '0', `hashSalt` varchar(250) DEFAULT NULL, `num_people` varchar(45) DEFAULT NULL, `tax` varchar(45) DEFAULT NULL, `company` varchar(45) DEFAULT NULL, `co_address` varchar(45) DEFAULT NULL, `co_city` varchar(45) DEFAULT NULL, `co_state` varchar(45) DEFAULT NULL, `co_zip` varchar(45) DEFAULT NULL, `token` varchar(32) NOT NULL DEFAULT '0', `coupon` varchar(45) DEFAULT NULL, `waiver_agree` varchar(4) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `registration_id` (`registration_id`), KEY `event_id` (`event_id`) ) ENGINE=InnoDB AUTO_INCREMENT=459 DEFAULT CHARSET=utf8 AUTO_INCREMENT=459 ; Hope this helps to clear things up. Not my table design by the way. A freeware plugin from Wordpress. Finished output should look like -> fullname | belt | events | school . All output for "belt" , "event", and "school" is derived from the "answer" table. "fullname" is derived from the 'attendee' table. Thanks! Steve
  16. Greetings Everyone, This is my first post on this site and I am a bit rusty when it comes to MySQL. Just got a project going for a website where there is a list required for participants once they have registered. The information I require is on two separate tables in the database. The main problem I have is that the information is all in one column and needs to be split into several different columns. Sounds familiar right? Just can't seem to get the code right. When I create the layout in PHP the output is stepped because it handles my aggregate query (created in PHP) as four different queries. I have tested each individual query and they work fine for that particular item. To make a long story short, how can I create a single query that will work with this? Tried a failed attempt at sub queries. Please see below: SELECT (SELECT concat( fname, ' ', left( lname, 1 ) , '.' ) AS fullname FROM wp_lvsz_evr_attendee WHERE event_id =9 ), ( SELECT answer AS belt FROM wp_lvsz_evr_answer, wp_lvsz_evr_attendee WHERE wp_lvsz_evr_attendee.id = wp_lvsz_evr_answer.registration_id AND wp_lvsz_evr_attendee.event_id =9 AND question_id =42 ), ( SELECT answer AS event FROM wp_lvsz_evr_answer, wp_lvsz_evr_attendee WHERE wp_lvsz_evr_attendee.id = wp_lvsz_evr_answer.registration_id AND wp_lvsz_evr_attendee.event_id =9 AND question_id =43 ), ( SELECT answer AS school FROM wp_lvsz_evr_answer, wp_lvsz_evr_attendee WHERE wp_lvsz_evr_attendee.id = wp_lvsz_evr_answer.registration_id AND wp_lvsz_evr_attendee.event_id =9 AND question_id =41 Keeping the data as a reference but as you can see my criteria is based on event_id and question_id. They all work individually but not collectively. Any help you could offer on this would be greatly appreciated as it is the last step in being able to finish my project. Thanks in advance, Steve
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.