0

My code for ALTER TABLE doesn't make use of the prepare() method of $wpdb and so the code needs to be updated properly. There are plenty of examples of query SELECT, etc but couldn't find something for ALTER TABLE with several options included in the SQL.

This is the old code.

$post_qry = $wpdb->get_row( "SELECT * FROM $wpdb->posts" );

if ( ! isset( $post_qry->forum_id ) ) {
    $wpdb->query( "ALTER TABLE {$wpdb->posts} ADD `forum_id` INT( 10 )
        UNSIGNED NOT NULL DEFAULT '0' AFTER `ID` " );
}

This is my first attempt, but something doesn't look right for the ADD.

if ( ! isset( $post_qry->forum_id ) ) {
    $wpdb->query( $wpdb->prepare( "ALTER TABLE {$wpdb->posts} ADD col= %s INT( 10 )
        UNSIGNED NOT NULL DEFAULT '0' AFTER col = %s ", 'forum_id', 'ID' ) );
}
  • First, is it proper to use ADD col = or is it ADD COLUMN=%s ?

  • Second, is the same true for stating after col=%s?

  • Third, should INT and UNSIGNED be used as %s also?

Thank you for your time to explain and maybe point to a good resource.

  • 1
    Modifying the posts table to add a forum_id is a bad idea, you should use post meta to store values, or use a custom taxonomy if you're trying to organise posts – Tom J Nowell Dec 21 '15 at 23:16
  • I understand but at this point the plugin was written 2.5 years ago and I'm slowly moving it to OOP as well as fix mistakes such as modifying the post table as well as the comments table. For now, I simply need to know a better way to use ALTER TABLE until I can get the code working for post meta. – LPH Dec 23 '15 at 5:03
  • Nevermind OOP, you can replace all your SQL queries with WP_Query loops and calls to get_post_meta, eliminate your table creation and update code entirely, and as a bonus you get a free GUI – Tom J Nowell Dec 23 '15 at 12:06
  • Thank you @TomJNowell -- your comments pushed me to stop altering the table. The key being update_post_meta ( $post->ID , 'forum_id', $thread_id ); instead of all the mysql code. Retrieving the meta data was just as easy. – LPH Dec 27 '15 at 15:28
1

Answer to this question actually comes from @TomJNowell. Instead of using MySQL calls, use the native functions included with WordPress:

update_post_meta ( $post->ID , 'forum_id', $thread_id );

where the forum_id is the key and the $thread_id is the value. I tried add_post_meta but that created duplicates.

To retrieve the meta data

$thread_id = get_post_custom_values( 'thread_id', $post->ID);

Now I'm looking at ways to add to the comment meta. Hopefully this helps someone else.

  • This sounds like a much better plan than modifying core tables. – birgire Dec 27 '15 at 15:52

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.