Show data from another table.

I made a new plugin and it works in cfdb-html with shortcode.

require_once(ABSPATH . 'wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php');
$exp2 = new CFDBFormIterator();
$exp2->export('Table2', $atts);
$exp = new CFDBFormIterator();
$exp->export('Table1', $atts);
while ($row = $exp->nextRow()) {
    while ($row2 = $exp2->nextRow()) {
        if ($row2['id'] === $row['id']){
            printf('%s', $row2['quantity']);
        }
    }
}

It prints a quantity but it’s the same in every row. How to get the right quantity for each row?

I think you might need to code it this way:

require_once(ABSPATH . 'wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php');

$exp = new CFDBFormIterator();
$exp->export('Table1', $atts);
while ($row = $exp->nextRow()) {
    $exp2 = new CFDBFormIterator();
    $exp2->export('Table2', $atts);
    while ($row2 = $exp2->nextRow()) {
        if ($row2['id'] === $row['id']){
            printf('%s', $row2['quantity']);
        }
    }
}

Thanks for a quick answer, but with that code it prints all the quantities in every row where id’s match.

Wow Michael, you are OK! ??

I thought something happened to you.

Glad you are back. Hope all is good with life.

Tested more and actually it prints all the quantities in every row that has the shortcode. Even if the id’s don’t match.

Tode just doesn’t read table2. Second while is too much.

Tried to do a join on MySQL but that doesn’t work either. How does Cfdb unpivot its table? This is what I tried.

With this I get #1054 – Unknown column Table1.id’ in ‘on clause’

USE <code>my_database</code>;
SELECT <code>submit_time</code> AS 'Submitted',
max(if(<code>field_name</code>='id', <code>field_value</code>, null )) AS 'id',
max(if(<code>field_name</code>='name', <code>field_value</code>, null )) AS 'name',
GROUP_CONCAT(if(<code>file</code> is null or length(<code>file</code>) = 0, null, <code>field_name</code>)) AS 'fields_with_file'
FROM <code>wp_6_cf7dbplugin_submits</code>
LEFT JOIN
(SELECT <code>submit_time</code> AS 'Submitted',
max(if(<code>field_name</code>='id', <code>field_value</code>, null )) AS 'id',
max(if(<code>field_name</code>='quantity', <code>field_value</code>, null )) AS 'quantity',
GROUP_CONCAT(if(<code>file</code> is null or length(<code>file</code>) = 0, null, <code>field_name</code>)) AS 'fields_with_file'
FROM <code>wp_6_cf7dbplugin_submits</code> 
WHERE <code>form_name</code> = 'Table2' ?
GROUP BY <code>submit_time</code> 
ORDER BY <code>submit_time</code> DESC
LIMIT 0,100) AS Table2
ON Table1.id = Table2.id
WHERE <code>form_name</code> = 'Table1'
GROUP BY <code>submit_time</code> 
ORDER BY <code>submit_time</code> DESC
LIMIT 0,100;

Here is a working join code:

SELECT
?*
FROM
?(
? ?SELECT
? ? ?<code>submit_time</code> AS 'Submitted',
? ? ?max(
? ? ? ?if(<code>field_name</code> = 'quantity', <code>field_value</code>, null)
? ? ?) AS 'quantity',
? ? ?max(
? ? ? ?if(<code>field_name</code> = 'id', <code>field_value</code>, null)
? ? ?) AS 'id',
? ? ?GROUP_CONCAT(
? ? ? ?if(
? ? ? ? ?<code>file</code> is null
? ? ? ? ?or length(<code>file</code>) = 0,
? ? ? ? ?null,
? ? ? ? ?<code>field_name</code>
? ? ? ?)
? ? ?) AS 'fields_with_file'
? ?FROM
? ? ?<code>wp_6_cf7dbplugin_submits</code>
? ?WHERE
? ? ?<code>form_name</code> = 'Table2'
? ?GROUP BY
? ? ?<code>submit_time</code>
?) AS Table2
?LEFT JOIN (
? ?SELECT
? ? ?<code>submit_time</code> AS 'Submitted',
? ? ?max(
? ? ? ?if(<code>field_name</code> = 'id', <code>field_value</code>, null)
? ? ?) AS 'id',
? ? ?max(
? ? ? ?if(
? ? ? ? ?<code>field_name</code> = 'name',
? ? ? ? ?<code>field_value</code>,
? ? ? ? ?null
? ? ? ?)
? ? ?) AS 'name',
? ? ?GROUP_CONCAT(
? ? ? ?if(
? ? ? ? ?<code>file</code> is null
? ? ? ? ?or length(<code>file</code>) = 0,
? ? ? ? ?null,
? ? ? ? ?<code>field_name</code>
? ? ? ?)
? ? ?) AS 'fields_with_file'
? ?FROM
? ? ?<code>wp_6_cf7dbplugin_submits</code>
? ?WHERE
? ? ?<code>form_name</code> = 'Table1'
? ?GROUP BY
? ? ?<code>submit_time</code>
?) AS Table1 ON Table1.id = Table2.id;

Now going to wrap it on PHP and in WordPress.

Why doesn’t this work?
? ?

global $wpdb;
$table_name = $wpdb->prefix . 'cf7dbplugin_submits';
$retrieve_data = $wpdb->get_results( "SELECT
?*
FROM
?(
? ?SELECT....

It would be great if Cfdb could do joins or if it would be possible to use custom MySQL-queries in Cfdb, so that it would be possible to use cfdb’s table views and filters etc with them. Now my query is working the way I needed but it’s hard to make it appear in a dynamic table on WordPress. Can’t get column names to display.