0

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.

1
  • If the code is just printing ARRAY then that means your variable is returning an array from the database. You can use foreach to print the contents of the array: tutorialrepublic.com/faq/…, then decide what content you want from the array. – Pixelsmith Aug 15 '20 at 18:35
0

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!

5
  • I tried the code & it didn't work. You're right - I've missed mentioning a few things. $subject = $_GET['subject']; fetches subject from the URL & then the page dynamically generates information from the MYSQL table. Like if the user clicks on history, domain.com/subject=history will fetch information like attendance, viva marks in history, etc. So I cannot assign 'subject' => 'biology', because then it will only fetch information for Biology. Clicking on subject will dynamically generate information only for that particular subject as passed in /?subject='' – Mohit Singh Aug 15 '20 at 20:28
  • name, sub, attendance, prev & current are columns in table grade. By fetching value of $subject = $_GET['subject']; and passing it to mysql query $wpdb->get_results( "SELECT prev FROM grade WHERE sub='" .$subject. "'") will fetch values for name, attendance, prev & current from the rows & display dynamic data on frontend when used with shortcode argument detail. So when I used shortcode like [marks_col detail="attendance"] it fetches attendance in that particular subject from column attendance & row of subject. – Mohit Singh Aug 15 '20 at 20:59
  • My current shortcode add_shortcode( 'get_marks', function ($atts) { $subject = $_GET['subject']; $detail = $atts['detail']; global $wpdb; $results = $wpdb->get_results( "SELECT * FROM grade WHERE sub='" .$subject."'"); foreach($results as $res) { return $res->$detail; } } ); displays dynamic values via shortcode [get_marks detail="attendance"], [get_marks detail="name"] & even for [get_marks detail="current"]. I wish to apply the conditions mentioned in the question on [get_marks detail="current"] shortcode. – Mohit Singh Aug 15 '20 at 21:04
  • Can you describe more examples of what you want to achieve? Could it be that you want a single function to retrieve data based on $_GET[ 'subject' ] to return values form a single column e.g. name, attendance, prev or current? – Mathias De Beyser Aug 17 '20 at 19:23
  • Yes, that's exactly I wish to achieve. Look at my last comment wherein I have described my shortcode and how the shortcode is able to fetch [get_marks detail="current"] and like I said, I want to apply the conditions on it. In other words, I want exactly what you have said in your last comment. – Mohit Singh Aug 18 '20 at 10:23
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.