Conditional formatting on data fetched from MYSQL

Question

I'm not an expert in PHP and I'm trying to apply conditional formatting on data fetched from wpdb. To be fair, I'm trying to create shortcode for this. My motto is to fetch values for $previous_marks & $current_marks from the database and change the color of $current_marks according to if, else condition. According to condition:

• If $current_marks > $previous_marks, then value of $current_marks should be displayed in green color along with pass.png image next to the value.
• Else value of $current_marks should be displayed in red color along with fail.png image next to the value.

I also need help in displaying images next to $current_marks value & I have no code for it:

Here's how my code looks like:

    add_shortcode( 'marks_col', function () {
    $subject = $_GET['subject'];
    
    global $wpdb;
    $previous_marks = $wpdb->get_results( "SELECT prev FROM grade WHERE sub='" .$subject. "'");
    $current_marks = $wpdb->get_results( "SELECT current FROM grade WHERE sub='" .$subject. "'");
    if ($previous_marks < $current_marks) {
       echo "<p style="color:green">" .$current_marks. "</p>"; ##display pass.png next to $current_marks value
    } else {
       echo "<p style="color:red">" .$current_marks. "</p>"; ##display fail.png next to $current_marks value
    }
    } );

$subject is fetched from the website's URL: www.domain.com/results?subject=biology. Currently, on using the shortcode [marks_col] on WP installation, it's just printing 'Array' in red color & nothing else. Any help would be greatly appreciated.

Answer 1

First a few notes:

• Shortcodes do accept arguments, so you could write [marks subject="Biology"] (@see https://codex.wordpress.org/Shortcode_API)
• You should never ever pass raw data directly into a SQL statement, Wordpress uses PDO, this means you can prepare statements. (@see https://developer.wordpress.org/reference/classes/wpdb/prepare/)
• Using the get_results will return an Array, object or null (@see https://developer.wordpress.org/reference/classes/wpdb/get_results/). There are many approaches here. You could go with $wpdb->get_var(), to get a single value. Or $wpdb->get_results() to fetch both in one query. I think we are missing some data, if you want a specific row just for one user, you could also use $wpdb->get_row(). Think that the database is a Excel sheet. If you want multiple rows and columns, you want to use $wpdb->get_results(). IF you want just one specific row you could use $wpdb->get_row(). IF you want just one column of data $wpdb->get_column(). If you just want one specific cell $wpdb->get_var().
• I would go with the icon approach instead of the image approach if you just want to use a "checkmark" or some "exclamation" mark based on failing or passing a specific score. A nice and free icon library (@see https://fontawesome.com/)

---

The code based on your given question, although I think you're missing a WHERE statement in your query to fetch a specific persons 'marks' based on own projects.

add_shortcode( "marks", function( $atts ) {

    //= defaults
    $args = shortcode_atts( array(
        'subject' => 'biology',
    ), $atts );

    //= database query
    global $wpdb;
    $query  = $wpdb->prepare( "SELECT prev, current FROM grade WHERE sub = %s", $args['subject'] );
    $result = $wpdb->get_row( $query );

    //= bail when no result
    if( is_null( $result ) ) return;

    //= conditional
    if( $result[ 'prev' ] < $result[ 'current' ] ) {
        echo "<p color=\"color: green;\">{$result['current']}<i class=\"far fa-check\"></i></p>";
        
    } else {
        echo "<p color=\"color: red;\">{$result['current']}<i class=\"far fa-times\"></i></p>";
        
    }
    
} );

---

If needed you can easily debug objects and arrays on screen with the following code:

echo var_export( $array, true );

Hope this helps out!

Answer 2

I don't completely understand but you want to access data based on a "subject" which I suppose is the "User_ID" or something in that order. And also pass a "column" parameter. So you could request one of the following things with one shortcode.

• Fetch name based on subject
• Fetch attendance based on subject
• Fetch prev based on subject
• Fetch current based on subject

If so we indeed need to alter the code.