Combining queries

steve_large · January 6, 2019

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

Barand · January 6, 2019

What are the table structures? I can't tell from your query which table all the relevant columns belong to.

steve_large · January 7, 2019

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

Edited January 7, 2019 by steve_large

Barand · January 7, 2019

I find the easiest way to do these is to get the data you need then store in an array that is structured to match the required output. Then create your output from the array.

DATA

SELECT * FROM test.wp_lvsz_evr_attendee;                               SELECT * FROM test.wp_lvsz_evr_answer;
+-----+-----------------+-------+-------+----------+                   +-----------------+-------------+--------+
| id  | registration_id | lname | fname | event_id |                   | registration_id | question_id | answer |
+-----+-----------------+-------+-------+----------+                   +-----------------+-------------+--------+
| 459 | 1               | Doe   | John  | 8        |                   |               1 |          40 | aaa    |
| 460 | 2               | Evans | Jane  | 9        |                   |               1 |          41 | bbb    |
| 461 | 3               | Smith | Dave  | 8        |                   |               1 |          42 | ccc    |
| 462 | 4               | Jones | Alec  | 9        |                   |               1 |          43 | ddd    |
+-----+-----------------+-------+-------+----------+                   |               2 |          40 | eee    |
                                                                       |               2 |          41 | fff    |
                                                                       |               2 |          42 | ggg    |
                                                                       |               2 |          43 | hhh    |
                                                                       |               3 |          40 | kkk    |
                                                                       |               3 |          41 | mmm    |
                                                                       |               3 |          42 | nnn    |
                                                                       |               3 |          43 | ooo    |
                                                                       |               4 |          40 | ppp    |
                                                                       |               4 |          41 | qqq    |
                                                                       |               4 |          42 | rrr    |
                                                                       |               4 |          43 | sss    |
                                                                       +-----------------+-------------+--------+

QUERY

SELECT  att.id
     , concat( lname, ' ', left( fname, 1 ) ) AS fullname
     , question_id
     , answer
FROM wp_lvsz_evr_answer ans 
     JOIN wp_lvsz_evr_attendee att 
                ON att.registration_id = ans.registration_id
                AND question_id BETWEEN 41 AND 43;
+-----+----------+-------------+--------+
| id  | fullname | question_id | answer |
+-----+----------+-------------+--------+
| 459 | Doe J    |          41 | bbb    |
| 459 | Doe J    |          42 | ccc    |
| 459 | Doe J    |          43 | ddd    |
| 460 | Evans J  |          41 | fff    |
| 460 | Evans J  |          42 | ggg    |
| 460 | Evans J  |          43 | hhh    |
| 461 | Smith D  |          41 | mmm    |
| 461 | Smith D  |          42 | nnn    |
| 461 | Smith D  |          43 | ooo    |
| 462 | Jones A  |          41 | qqq    |
| 462 | Jones A  |          42 | rrr    |
| 462 | Jones A  |          43 | sss    |
+-----+----------+-------------+--------+

CODE

<?php
$res = $db->query("SELECT  att.id
                         , concat( lname, ' ', left( fname, 1 ) ) AS fullname
                         , question_id
                         , answer
                    FROM wp_lvsz_evr_answer ans 
                         JOIN wp_lvsz_evr_attendee att 
                                    ON att.registration_id = ans.registration_id
                                    AND question_id BETWEEN 41 AND 43
                    ");

$headings = [  42 => 'Belt' ,
               43 => 'Event' , 
               41 => 'School' 
            ];
            
$temp_array = array_fill_keys(array_keys($headings), '');    // array for each attendee to be filled in fro query results 

// process query results and place in array  with attendee as the key 
$data = [];
foreach ($res as $r) {
    if ( !isset($data[$r['id']])) {
        $data[$r['id']] = [ 'name' => $r['fullname'], 'answers' => $temp_array ] ;
    }
    $data[$r['id']]['answers'][$r['question_id']] = $r['answer'] ;   // store answer in its array position
}

// 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['answers']) . "</td></tr>\n";
}                
?>

<table border='1' style='border-collapse: collapse; width: 600px'>
    <?=$theads?>
    <?=$tdata?>
<?table>

RESULT

Edited January 7, 2019 by Barand

steve_large · January 8, 2019

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

Edited January 8, 2019 by steve_large

steve_large · January 10, 2019

On ‎1‎/‎6‎/‎2019 at 2:28 PM, Barand said:

What are the table structures? I can't tell from your query which table all the relevant columns belong to.

On ‎1‎/‎8‎/‎2019 at 12:37 AM, steve_large said:

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

Edited January 10, 2019 by steve_large

steve_large · January 10, 2019

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

steve_large · January 10, 2019

Just now, steve_large said:

Barand · January 10, 2019

You can use

WHERE question_id IN (46, 47, 51)

steve_large · January 10, 2019

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

steve_large · March 24, 2020

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

Barand · March 24, 2020

Looks like your approach to process name just like you do with school, belts, events is correct.

steve_large · March 24, 2020

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

Barand · March 24, 2020

7 minutes ago, steve_large said:

I know that the MySQL is correct

I disagree, it ignores the entry_id. That is the field which you need to group the other values.

Barand · March 24, 2020

DATA

+----------+----------+-------+
| entry_id | field_id | value |
+----------+----------+-------+
| 1        | 5        | Curly |
| 1        | 13       | bbb   |
| 1        | 16       | ccc   |
| 1        | 18       | ddd   |
| 2        | 5        | Larry |
| 2        | 13       | eee   |
| 2        | 16       | fff   |
| 2        | 18       | ggg   |
| 2        | 43       | hhh   |
| 3        | 5        | Mo    |
| 3        | 13       | kkk   |
| 3        | 16       | mmm   |
| 3        | 18       | nnn   |
| 3        | 43       | ooo   |
| 4        | 5        | Tom   |
| 4        | 13       | ppp   |
| 4        | 16       | qqq   |
| 4        | 18       | rrr   |
| 4        | 43       | sss   |
+----------+----------+-------+

CODE

$res = $conn->query("SELECT entry_id
                          , field_id
                          , value
                     FROM wp_wpforms_entry_fields
                     WHERE field_id IN (5, 13, 16, 18)
                     ORDER BY entry_id     
                    ");

$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['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";
}

OUTPUT

image.png.3a58459b691f3dbb554ed56528e56a55.png

steve_large · March 24, 2020

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

Barand · March 24, 2020

Yes

SELECT e.entry_id
     , field_id
     , value
FROM wp_wpforms_entry_fields f
     JOIN 
     wp_wpforms_entries e ON f.entry_id = e.entry_id
WHERE field_id IN (5, 13, 16, 18)
      AND e.status='completed'
ORDER BY e.entry_id

steve_large · March 24, 2020

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

Barand · March 24, 2020

3 hours ago, steve_large said:

but got an "ambiguous" error.

That's because there is an "entry_id" column in both tables. You need to specify which one (as I did)

SELECT e.entry_id
       ^

steve_large · March 25, 2020

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

gizmola · March 25, 2020

I don't know whether you care or not about People with names like "Martin St. Louis" but obviously your code will not work correctly in that case. Of course you also can't tell if it's someone with a name like 'Sue Ann Smith'. Your code is biased towards Sue Ann Smith, but gets Martin St. Louis wrong.

Without a separation of first name and lastname, there is no way to really do it reliably.

With that said, you can boil this down to a one liner that doesn't use arrays. I'm fairly sure that your code would throw a runtime error if there was a single name in the string, but didn't test it. This could/should be coded around, which I do with a ternary. There is a lot of extra trimming I do, which could be avoided if the original string was simply trimmed prior to using this one liner, but I provided something that works with a variety of extraneous spaces, per the examples.

Here's a one liner as a function for the purposes of illustration, that you might consider:

$tests = array('Bob Jones', 'Sue Ann Smith', 'Martin St. Louis', 'Prince', '  Adam    West', '   Fred R. Murray   ', ' Skipper   Van   Dammage');

foreach ($tests as $test) {
    echo makeName($test) . PHP_EOL;
}

function makeName($name) {
    return false !== strpos(trim($name), ' ') ? preg_replace('/\s\s+/', ' ', rtrim(substr(trim($name), 0, strrpos(trim($name), ' ')))) . ' ' . substr(strrchr(rtrim($name), ' '), 1, 1) : trim($name);
}

Returns:

Bob J
Sue Ann S
Martin St. L
Prince
Adam W
Fred R. M
Skipper Van D

steve_large · March 31, 2020

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

steve_large · April 10, 2020

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

Barand · April 10, 2020

What is this doing ...

REPLACE(REPLACE(REPLACE(REPLACE(value, '- &#36;', ''), '75.00', ''),'90.00',''),'100.00','')

What's the data that requires a quad-replace?

Barand · April 10, 2020

Does this do it for you?

wp_wpforms_entry_fields:
+----------+----------+--------------------+
| entry_id | field_id | value              |
+----------+----------+--------------------+
|        1 |        5 | Laura Norder       |
|        1 |       13 | School A           |
|        1 |       16 | Belt 1             |
|        1 |       18 | Sparring - $75.00  |
|        2 |        5 | Peter Dowt         |
|        2 |       13 | School B           |
|        2 |       16 | Belt 2             |
|        2 |       18 | Sparring - $90.00  |
|        2 |       43 | Belt 3             |
|        3 |        5 | Tom Dicanari       |
|        3 |       13 | School C           |
|        3 |       16 | mmm                |
|        3 |       18 | Soarring - $100.00 |
|        3 |       43 | kkk                |
|        4 |        5 | Joe King           |
|        4 |       13 | School D           |
|        4 |       16 | Belt 4             |
|        4 |       18 | Sparring - $150.00 |
|        4 |       43 | sss                |
+----------+----------+--------------------+


mysql> SELECT  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
    -> WHERE field_id IN (5, 13, 16, 18)
    -> ORDER BY entry_id;
    
Query results:    
+----------+----------+----------+
| entry_id | field_id | value    |
+----------+----------+----------+
|        1 |        5 | Laura N  |
|        1 |       13 | School A |
|        1 |       16 | Belt 1   |
|        1 |       18 | Sparring |
|        2 |        5 | Peter D  |
|        2 |       13 | School B |
|        2 |       16 | Belt 2   |
|        2 |       18 | Sparring |
|        3 |        5 | Tom D    |
|        3 |       13 | School C |
|        3 |       16 | mmm      |
|        3 |       18 | Soarring |
|        4 |        5 | Joe K    |
|        4 |       13 | School D |
|        4 |       16 | Belt 4   |
|        4 |       18 | Sparring |
+----------+----------+----------+

Sign In

Combining queries

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information