Support » Developing with WordPress » Trying to use custom PHP code in WordPress Page

  • I have modified a page on my Word Press site to display a custom table using php/MySQL.

    I must point out my skill level is poor and most code has been found from the net and changed by trial and error.

    I can get the page to work using a simple single column table but ideally I would like to be able to display in multiple columns.

    Using the details from this site –Webhostingtalk

    I have come up with the following code but I am having issues with it not displaying data in a table.

    
            <?php
            global $wpdb;
            $i = 1;
            $sql= "SELECT <code>value</code>, COUNT(*) as tot_year FROM <code>wp_bp_xprofile_data</code> WHERE <code>field_id</code> =45 GROUP BY value;";
            $result = $wpdb->get_results($sql);
    
            // SHOW ON SCREEN THE RESULTS OF THE SRATCH - TO BE REMOVED AFTER TESTING
            print_r ($result);
            echo '<P><P>';
    
            $num_years = $wpdb->num_rows;
            $num_columns = 4;
            $idx = 1;
            $num_rows = $num_years / $num_columns;
    
            //SHOW ON SCREEN THE NUMBER OF COLUMNS NEEDED - TO BE REMOVED AFTER TESTING
            echo 'number of rows: '.$num_rows;
    
    	// CODE WORKS UP TO THIS POINT
    	
            // Place the data in the proper columns / rows.
            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");
                    }
            echo ("</table>");
            ?>

    Could some kind soul please point out what I have done wrong please.
    Cheers 🙂

    • This topic was modified 5 years, 5 months ago by mdrabble.
Viewing 8 replies - 1 through 8 (of 8 total)
  • Bunty

    (@bhargavbhandari90)

    What you are getting by doing print_r ($result); ?

    Thread Starter mdrabble

    (@mdrabble)

    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 5 years, 5 months ago by mdrabble.
    • This reply was modified 5 years, 5 months ago by mdrabble.
    Moderator bcworkz

    (@bcworkz)

    For one thing, I don’t see where $rows (plural) is assigned any value, so the foreach does nothing.

    I think you’re missing one level of looping. You’ve collected the cell contents into the $row array, but no where are those cells compiled into a $rows element. You may need a foreach loop on each row to build each $rows element.

    I think you are overwriting each cell element in $row[n] instead of concatenating to it, thus you end up with a single column instead of a grid. If this was your intent, my suggestion of a foreach on each row would not apply, but you still need to assign values to $rows elements.

    Bunty

    (@bhargavbhandari90)

    Do this:

    
    foreach( $result as $row ) {
        // do your stuff here
        
    }
    
    Thread Starter mdrabble

    (@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>';
            }
    ?>
    Bunty

    (@bhargavbhandari90)

    @mdrabble
    Yes you are right. Anything you want to do in WP, use only WP functions and standards as much as possible.

    • This reply was modified 5 years, 5 months ago by Bunty. Reason: forgot to mention name
    • This reply was modified 5 years, 5 months ago by Bunty.
    Thread Starter mdrabble

    (@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 5 years, 5 months ago by mdrabble.
    • This reply was modified 5 years, 5 months ago by mdrabble.
    Bunty

    (@bhargavbhandari90)

    You can achieve this by using posts_where filter. It’s a simple filter and you can easily use in your code.

    Reference link:
    https://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Trying to use custom PHP code in WordPress Page’ is closed to new replies.