mdrabble

I managed to sort it using this method

global $wpdb;
$winningyear = $wpdb->get_results("SELECT value<, COUNT(*) as win_year FROM wp_bp_xprofile_data WHERE field_id =6 GROUP BY value ORDER BY COUNT(*) DESC LIMIT 1", ARRAY_A);

foreach ($winningyear as $winners)
{
echo "<h3><center>The Alumni year with the most registered users is:<B><font color=red> $winners[value]</Font> </B> with $winners[win_year] members.</h3></center>";
}

• This reply was modified 8 years ago by mdrabble.

Ok next question.

Using a similar sql query – I want to display anyone who had a leavers year of 1987.

So I thought the following query

SELECT * wp_bp_xprofile_data, wp_users.displayname WHERE wp_bp_xprofile_data.field_id=45 AND wp_bp_xprofile_data.value=1987

would return a single user as only 1 user have a leaving year of 1987 – however, the above query returns the same filed_id and value for all users in wp_users

|————–|———-|——-|
|Display Name | Field_ID | value |
|————–|———-|——-|
| User1 | 45 | 1987 |
| User2 | 45 | 1987 |
| User3 | 45 | 1987 |
| User4 | 45 | 1987 |
|————–|———-|——-|

User 2 & 3 should have a value of 1988 and user 4 should have a value of 1999

What is the best way to pull in details from different tables and display on a single page?

Thanks ??

• This reply was modified 8 years, 1 month ago by mdrabble.
• This reply was modified 8 years, 1 month ago by mdrabble.

Thanks for the reply – after you pointed me in the right direct I went back and started from scratch.

Here is the working code – would this open up my site up to SQL Injection attacks? I read somewhere that using $wpdb is the correct method as helps prevent such attacks.

        <?php
        //Code to display Alumni Years
        global $wpdb;
        $alumniyears = $wpdb->get_results("SELECT <code>value</code>, COUNT(*) as tot_year FROM <code>wp_bp_xprofile_data</code> WHERE <code>field_id</code> =45 GROUP BY value", ARRAY_A);

        $rows = $wpdb->num_rows;    // Find total rows returned by database
        if($rows > 0) {
                $cols = 3;    // Define number of columns
                $counter = 1;     // Counter used to identify if we need to start or end a row
                $nbsp = $cols - ($rows % $cols);    // Calculate the number of blank columns

                echo '<table width="100%" align="center" cellpadding="4" cellspacing="1">';
                foreach ($alumniyears as $row) 
                //while ($row = $result->fetch_array()) {
                {
                        if(($counter % $cols) == 1) {    // Check if it's new row
                                echo '<tr>';
                        }
                        echo '<td>'.$row[value].'</td>';

                        if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero
                                echo '</tr>';
                        }
                        $counter++;    // Increase the counter
                }
                $result->free();
                if($nbsp > 0) { // Add unused column in last row
                        for ($i = 0; $i < $nbsp; $i++)  {
                                echo '<td>&nbsp;</td>';
                        }
                        echo '</tr>';
                }
                echo '</table>';
        }
?>

Thanks for the reply.

For ‘print_r ($result);’ I am getting the following

Array ( [0] => stdClass Object ( [value] => 2000 [tot_year] => 3 ) [1] => stdClass Object ( [value] => 2002 [tot_year] => 8 ) [2] => stdClass Object ( [value] => 2004 [tot_year] => 5 ) [3] => stdClass Object ( [value] => 2006 [tot_year] => 4 ) [4] => stdClass Object ( [value] => 2008 [tot_year] => 1 ) [5] => stdClass Object ( [value] => 2010 [tot_year] => 3 ) [6] => stdClass Object ( [value] => 2012 [tot_year] => 3 ) [7] => stdClass Object ( [value] => 2014 [tot_year] => 1 ) )

I have noticed that the theme sidebar and footer disappear unless i comment out

   
        while ($row = mysql_fetch_array($result))
                {
                $row_id = $idx % $num_rows;
                $row[row_id] = "<td>$row[value]</td>";
                $idx++;
                }
        // Print the whole thing now.
        echo ("<table>");
                foreach ($rows as $cur_row)
                {
                echo ("<tr>" . $cur_row . "</tr>\n");
                }

could it be something to do with this line
while ($row = mysql_fetch_array($result))

Thanks

• This reply was modified 8 years, 1 month ago by mdrabble.
• This reply was modified 8 years, 1 month ago by mdrabble.

I was looking a way to populate a table dynamically by querying a wordpress table and then displaying the details based on that query.

I’m using BuddyPress extended Profiles and have a field called LeavingYear.

I wanted a create a dynamic table by by filtering and grouping results together

Leaving Year | Number Associated
2000 | 3
2001 | 2
2004 | 4

I think this isn’t possible without manually coding a page to do this so I have made a start creating a custom page.

Thank you for replying and trying to help ??

Ok, slight change of plan…..

If I make a copy page.php from my selected theme and change it to display the data I need using the following example, would this be the correct way to connect and pull data from the database or would I be better off avoiding the method below?

<?php
global $wpdb;
$alumniyear = $wpdb->get_results("sql query;");

** php code to display data **

?>

I contacted the author of TablePress and unfortunately the plugin cannot query sql directly but he did suggest some alternatives with Tablepress extensions.

After spending some time looking at this I am still struggling to achieve.

I’ll keep plodding on in the hope of finding something or until someone can offer any other suggestions/plugins.

Thanks replying with info on how I can do possibly do this.

Having downloaded the extensions I am still struggling to understand how to pull in the data other than manually entering the data or manually exporting and importing.

Not to worry, I shall keep trying.

Many thanks

hi @jdembowski

Thanks for the reply and no problem about removing the posts – wasn’t too sure about posting similar posts which is why I mentioned about deleting if needed.

I have the basic site up and running and populated with BuddyPress Default Data so I have some data to play with.

I’ve installed the plugin but I cannot see any way of getting it to look at the wp_bp_xprofile_data table.

I’ll contact the author and see what he can advise.

Thanks

@sterndata many thanks for the reply and the pointer i’ll take a look the plugin you mentioned ??