Title: dbDelta alternative?? a sql query with a semicolon problem Last modified: August 20, 2016 --- # dbDelta alternative?? a sql query with a semicolon problem * Resolved [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/) * 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. Viewing 15 replies - 1 through 15 (of 16 total) 1 [2](https://wordpress.org/support/topic/dbdelta-alternative/page/2/?output_format=md) [→](https://wordpress.org/support/topic/dbdelta-alternative/page/2/?output_format=md) * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309004) * 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). * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309006) * 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. * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309009) * Generally speaking you’d use $wpdb->query() to perform queries: * [http://codex.wordpress.org/Class_Reference/wpdb#Run_Any_Query_on_the_Database](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. * Moderator [Ipstenu (Mika Epstein)](https://wordpress.org/support/users/ipstenu/) * (@ipstenu) * 🏳️‍🌈 Advisor and Activist * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309011) * This isn’t actually MultiSite specific and I’m moving it to ‘Advanced’ where the uber brains live. * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309022) * [@pbaylies](https://wordpress.org/support/users/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? * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309035) * 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. * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309038) * ``` 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? * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309044) * 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. * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309221) * Thank you sir for your help! I appreciate it a lot! ^_^v * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309223) * 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. * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309256) * 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/](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. * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309361) * 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. * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309363) * 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). * Thread Starter [chibib0](https://wordpress.org/support/users/chibib0/) * (@chibib0) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309366) * 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”. * [Peter Baylies](https://wordpress.org/support/users/pbaylies/) * (@pbaylies) * [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/#post-2309370) * 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](http://php.net/manual/en/language.types.string.php) Viewing 15 replies - 1 through 15 (of 16 total) 1 [2](https://wordpress.org/support/topic/dbdelta-alternative/page/2/?output_format=md) [→](https://wordpress.org/support/topic/dbdelta-alternative/page/2/?output_format=md) The topic ‘dbDelta alternative?? a sql query with a semicolon problem’ is closed to new replies. ## Tags * [database](https://wordpress.org/support/topic-tag/database/) * [dbDelta()](https://wordpress.org/support/topic-tag/dbdelta/) * [sql](https://wordpress.org/support/topic-tag/sql/) * In: [Developing with WordPress](https://wordpress.org/support/forum/wp-advanced/) * 16 replies * 3 participants * Last reply from: [chibib0](https://wordpress.org/support/users/chibib0/) * Last activity: [14 years, 6 months ago](https://wordpress.org/support/topic/dbdelta-alternative/page/2/#post-2309374) * Status: resolved ## Topics ### Topics with no replies ### Non-support topics ### Resolved topics ### Unresolved topics ### All topics