0

I have this MySQL query, which do work in PHPMyAdmin:

set @ROW = 0;UPDATE `wp_wpsstm_subtracks` SET `subtrack_order` = @ROW := @ROW+1 WHERE tracklist_id='176226' ORDER BY `subtrack_time` ASC

I would like to use it within Wordpress:

    $querystr = $wpdb->prepare("set @ROW = 0;UPDATE `$subtracks_table` SET `subtrack_order` = @ROW := @ROW+1 WHERE tracklist_id='%d' ORDER BY `subtrack_time` ASC", $this->post_id );
    return $wpdb->query($querystr);

But it fires a syntax error:

WordPress database error Erreur de syntaxe près de 'UPDATE wp_wpsstm_subtracks SET subtrack_order = @ROW := @ROW+1 WHERE trackli' à la ligne 1 for query set @ROW = 0;UPDATE wp_wpsstm_subtracks SET subtrack_order = @ROW := @ROW+1 WHERE tracklist_id='176226' ORDER BY subtrack_time ASC

How can I make this work ?

Thanks !

0

If I correctly understood, this is not possible (two queries instead of one). I ended up with this sad and time-consuming loop... :/

    //get subtracks
    $querystr = $wpdb->prepare("SELECT subtrack_id FROM `$subtracks_table` WHERE tracklist_id='%d' ORDER BY `subtrack_time` ASC",$this->post_id);
    if ( !$ids = $wpdb->get_col($querystr) ) return;

    //update order
    $i = 0;
    foreach($ids as $id){

        $i++;

        $wpdb->update( 
            $subtracks_table, //table
            array('subtrack_order'=>$i), //data
            array('subtrack_id'=>$id) //where
        );
    }
0

I have just created this and it is working like a charm:

$wpdb->query(
    "UPDATE " . $table . " SET your_column_name = (
        SELECT (@rownum := @rownum + 1) FROM (SELECT @rownum := 0) r
    ) 
    WHERE some_column = " . $id . " AND some_other_column = " . $some_value
);

You can remove additional term AND if you don't need it.

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.