dbDelta alternative?? a sql query with a semicolon problem

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).

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.

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

https://codex.www.ads-software.com/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.

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

@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?

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.

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?

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.

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

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.

I’d suggest just rewriting your query a bit; you can insert multiple records into MySQL with a single query: https://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.

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.

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).

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”.

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.

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