WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] dbDelta alternative?? a sql query with a semicolon problem (17 posts)

  1. chibib0
    Member
    Posted 2 years ago #

    I have this php script that generate an sql query. However, it needs a semicolon inside the sql statement and because of that, the sql query doesn't work.

    The problem is that, the value bm_shopping_cart.php;bm_categories.php doesn't stored in the database that suppose to be inserted by that query. When I removed ;bm_categories.php the value bm_shopping_cart.php is inserted on the database. So the problem resides on having the semicolon. I also tried using \ but the problem still there. I really need the semicolon included on the value. Any ideas?

    VALUES ('Installed Modules', 'MODULE_BOXES_INSTALLED', 'bm_shopping_cart.php;bm_categories.php', 'This is automatically updated. No need to edit.', '6', '0', now())

    It is included in this function.

    function insert_configuration6_table($table_name9, $type) {
         global $wpdb;
    
         if (!empty ($wpdb->charset))
         $charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
         if (!empty ($wpdb->collate))
         $charset_collate .= " COLLATE {$wpdb->collate}";
    
        $sql = "INSERT INTO {$table_name9} (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Installed Modules', 'MODULE_BOXES_INSTALLED', 'bm_shopping_cart.php;bm_categories.php', 'This is automatically updated. No need to edit.', '6', '0', now()),('Installed Template Block Groups', 'TEMPLATE_BLOCK_GROUPS', 'boxes', 'This is automatically updated. No need to edit.', '6', '0', now());";
    
         require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
         dbDelta($sql);
        }

    I have searched a lot for this however, I can't find a solution that work.

    How can I make an sql query that includes a semicolon in the values? or do you have any solution to this or a dbdelta alternative??

    Thanks.

  2. Peter Baylies
    Member
    Posted 2 years ago #

    The dbDelta() function is designed to update table structures, do you need to use it in this query? If so, could you update the inserted value later? The first thing the dbDelta() function does is explode on semicolons, so escaping the semicolon will not work. You could also try escaping the semicolon as \x3b (the hexadecimal value for it) but that might just get passed into the database like that unaltered, at which point you'd have to do a query to update it anyhow (unless the code that pulls it back is happy with that as a value).

  3. chibib0
    Member
    Posted 2 years ago #

    How to that sir?

    Well, basically im using dbdelta in this query since this is what I know. Do you recommend any alternative to successfully make the query work?

    Thanks.

  4. Peter Baylies
    Member
    Posted 2 years ago #

    Generally speaking you'd use $wpdb->query() to perform queries:

    http://codex.wordpress.org/Class_Reference/wpdb#Run_Any_Query_on_the_Database

    It's already global in your function, so try doing a

    $wpdb->query($sql);

    in place of dbDelta().

    If that works, you also shouldn't need to pull in upgrade.php.

  5. This isn't actually MultiSite specific and I'm moving it to 'Advanced' where the uber brains live.

  6. chibib0
    Member
    Posted 2 years ago #

    @pbaylies

    Thank you sir! it solves my problem!

    1 another thing.. i have lots of insert into queries and each uses that function that i created.. do you have any suggestions/recommendations to make it simple?

  7. Peter Baylies
    Member
    Posted 2 years ago #

    I don't see where $type or $charset_collate are being used in that function either; if all you're doing are sql queries, you might be able to just use $wpdb->query() for it directly, or write a greatly simplified function.

  8. chibib0
    Member
    Posted 2 years ago #

    if (!empty ($wpdb->charset))
         $charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
         if (!empty ($wpdb->collate))
         $charset_collate .= " COLLATE {$wpdb->collate}";

    The code above is what you mean? I just got it from other sources and i actually don't know their exact function.. so should i remove the above code?

  9. Peter Baylies
    Member
    Posted 2 years ago #

    I think you can; I don't see the $charset_collate variable actually being used in your function, so there's no point in setting it.

  10. chibib0
    Member
    Posted 2 years ago #

    Thank you sir for your help! I appreciate it a lot! ^_^v

  11. chibib0
    Member
    Posted 2 years ago #

    By the way sir.. I have multiple INSERT INTOs

    Can I do this,

    function insert_configuration_table($table_name9, $type) {
    	global $wpdb;
    
    $sql = "INSERT INTO {$table_name9} (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, date_added) VALUES ('Free Shipping For Orders Over', 'MODULE_ORDER_TOTAL_SHIPPING_FREE_SHIPPING_OVER', '50', 'Provide free shipping for orders over the set amount.', '6', '4', 'currencies->format', now());
    INSERT INTO {$table_name9} (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, use_function, date_added) VALUES ('Set Order Status', 'MODULE_PAYMENT_COD_ORDER_STATUS_ID', '0', 'Set the status of orders made with this payment module to this value', '6', '0', 'tep_cfg_pull_down_order_statuses(', 'tep_get_order_status_name', now());"; 
    
    	$wpdb->query($sql);
    }

    Thanks.

  12. Peter Baylies
    Member
    Posted 2 years ago #

    I'd suggest just rewriting your query a bit; you can insert multiple records into MySQL with a single query: http://www.electrictoolbox.com/mysql-insert-multiple-records/

    function insert_configuration_table($table_name9, $type) {
    	global $wpdb;
    
    $sql = "INSERT INTO {$table_name9} (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, use_function, date_added) VALUES ('Free Shipping For Orders Over', 'MODULE_ORDER_TOTAL_SHIPPING_FREE_SHIPPING_OVER', '50', 'Provide free shipping for orders over the set amount.', '6', '4', null, 'currencies->format', now()), ('Set Order Status', 'MODULE_PAYMENT_COD_ORDER_STATUS_ID', '0', 'Set the status of orders made with this payment module to this value', '6', '0', 'tep_cfg_pull_down_order_statuses(', 'tep_get_order_status_name', now());"; 
    
    	$wpdb->query($sql);
    }

    Also note that the parameters have to match; I guessed here, using null as a default argument for set_function in one of the queries, but if it's something else, you'd want to fix that.

  13. chibib0
    Member
    Posted 2 years ago #

    Thank you sir..

    I have another problem inserting another value.

    // Address format data
    function insert_addressformat_table($table_name3, $type) {
    	global $wpdb;
    
    $sql = "INSERT INTO {$table_name3} VALUES (1, '$firstname $lastname$cr$streets$cr$city, $postcode$cr$statecomma$country','$city / $country'), (2, '$firstname $lastname$cr$streets$cr$city, $state    $postcode$cr$country','$city, $state / $country'), (3, '$firstname $lastname$cr$streets$cr$city$cr$postcode - $statecomma$country','$state / $country'), (4, '$firstname $lastname$cr$streets$cr$city ($postcode)$cr$country', '$postcode / $country'), (5, '$firstname $lastname$cr$streets$cr$postcode $city$cr$country','$city / $country');";
    
    	$wpdb->query($sql);
    }

    values with "$" is not inserted in the database.

  14. Peter Baylies
    Member
    Posted 2 years ago #

    That is correct, those other variables don't have scope inside your function. You'd either not want to do it in that function, or pass those variables into your function (as individual parameters as you do with $table_name3 and $type or into an array and then back out) or make them global in the script and within the function (as you do with $wpdb).

  15. chibib0
    Member
    Posted 2 years ago #

    values with "$" is needed to be stored in my database.. as it will appear just like a plane "$" just with added text and not a function nor variable. Like storing "$100".

  16. Peter Baylies
    Member
    Posted 2 years ago #

    That's right, in PHP, those are interpreted as variables when they're in double quotes. You can escape them by using \$ instead of $ when in double quotes in a PHP string, or by using single quotes instead.

    http://php.net/manual/en/language.types.string.php

  17. chibib0
    Member
    Posted 2 years ago #

    Thank you sir! It works!

Topic Closed

This topic has been closed to new replies.

About this Topic