0

I have created a shortcode with only ONE attribute, but the SQL request needs TWO attributes for %s, because of the subquery. The following SQL does not work, because the second %s does not have a value.

When I create a workaround with a second attribute, the second %s in SQL subquery works as expected, but I don't want to use two identical attributes.

How can I duplicate the single attribute for using it twice in SQL request?

 <?php
 // Shortcode: [my_shortcode market="USA"]
 function showSomething( $country ){
    $country = shortcode_atts( array(
        'market' => '',
        ), $country);  

     global $wpdb;

     $sql = $wpdb->get_results( $wpdb->prepare( 
        "SELECT `Date`, `Field1`, `Field2`, `Field3`
         FROM   `table` 
         WHERE  `country` = %s
         AND    `Date` = (SELECT DISTINCT `Date`
                          FROM `table` 
                          WHERE `country` = %s
                          ORDER BY `Date` DESC
                          LIMIT 1)", $country ) );

   ....
   return ....
 }
 add_shortcode('my_shortcode', 'showSomething');
  • 2
    You could use %1$s instead, to use the same attribute several times. – Jack Johansson Mar 18 '18 at 18:13
  • Replacing both %s with %1$s results in an empty SQL result, but it should provide one row (cross-validated manually with phpMyAdmin). – Peter Mar 18 '18 at 18:32
  • 1
    LIMIT 1)", $sql ) ); --- $sql isn't defined. – Nathan Johnson Mar 18 '18 at 22:26
  • Oh, yes, you are right. I've replaced $sql with $country but %1$s in query and subquery is not providing database result. Maybe it is not possible to use %1$s twice times in one request? – Peter Mar 19 '18 at 7:23
  • As far as I understand, wordpress 4.8.2 specifically restricts the convenient usage of numbered placeholders like %1$s, %1$d, %1%f in WPDB::prepare. (core.trac.wordpress.org/ticket/41925) – Peter Apr 10 '18 at 15:36
0

Wordpress 4.8.2 specifically restricts the convenient usage of numbered placeholders like %1$s, %1$d, %1%f in WPDB::prepare. (core.trac.wordpress.org/ticket/41925).

To avoid usage of numbered placeholders, SQL query has to be rearranged to just use only one %s.

SELECT DISTINCT t.`Date`, y.*
FROM `table` t
LEFT JOIN  `table` y ON (t.`Date` = y.`Date`) 
WHERE t.`country` = %s
ORDER BY t.`Date` DESC
LIMIT 1

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.