PHP Function to use an array to create a db table, fields, and values

We would need to see this array. Can you post the output of a print_r($thisarray)?

Is this viewable enough rather than pasting a long comment here:

Test Area

That array should be an object, and it should correspond to a series of tables in a database, not a single table.

If this is what you're talking about, it is a strange-looking data structure.  
http://dev.sc51.info/seo-review/

Can you please post the PHP code that generates your test data set?  With that, I can show you how to get a single-dimensional array into a CREATE TABLE statement, and how to load some data into the data base.  But with what you have at the sc51.info site, there is no general-purpose solution -- that is a bespoke data structure.  You could serialize it and use fulltext indexing, but that would seem to be painting with a pretty broad brush.

If you want to store it in a db, then that array should be a single record in a document database such as MongoDB. That way, you can store the entire thing as json-encoded data and read it back just as easily into an array. This is a very poor situation for using a relational database structure such as MySQL.

Here's a reference for how to use MongoDB in your code: http://www.php.net/manual/en/book.mongo.php

Assuming you've installed Mongo on your server, as well as the PECL package in PHP, all you need to do is this:

//create the collection in the db
$m = new Mongo();
$db = $m->selectDB('repo');
$collection = $db->createCollection('arrays');
$collection->insert($array);
echo $array['_id']; //outputs the id you'll need in order to retrieve the record later

//retrieve the array from the collection
$m = new Mongo();
$collection = $m->selectDB('repo')->selectCollection('arrays');
$array = $collection->findOne(array('_id'=>$id)); //$id is the output of the snippet above

Select allOpen in new window

I don't know if explaining how and why on this will help. This is a wordpress plugin that uses an encrypted xml file when first activated to fill in a large section of text boxes. But the original programming just imported that full xml file into one field in wp-options, yet only 5 sections are being used. We now want to upgrade to use all the text boxes, but the if the user has edited those first 5, we don't want to overwrite their changes.

When the upgrade happens, the new xml file just completely overwrites everything, where we want it to skip those 5 from before and then enter only the new data.

That's why I think the only solution would be to use that prior imported data that's in one field only and put it in it's own table with a row for each set of text boxes.

Example of what it should be in each row:
id,specification-name,valueofbox1,valueofbox2,valueofbox3,enabled

Wordpress? I love the Wordpress framework! But boy did you post this question in the wrong category. I'm at work right now, so I can't devote the time to work out a solution to your approach in detail, but in general terms it's going to involve naming each of your textboxes with a unique identifier and using the set_ and get_ option functions built into WP, and combine that with a basic migration script to help preserve old data and port it into the new way of storing it. Don't go through the hassle of creating a new database table. It's unnecessary and will create far too much fragility for your plugin, especially as you try to keep it compatible with future versions of WP.

Ask an admin to move this question over to the Wordpress section, and I'll check back in after work to see if you've gotten an answer. If not, I'll do my best to write up some simple code for you when I have a chance to think about it for a bit.

Alright, I went back over your array in more detail. I think you're going to need to write your upgraded plugin to check for the existence of your original option record, and if it finds it, use the 5 values in there to create 5 new records in the wp_options table. Then go ahead and let the new XML file overwrite everything, but in your script, whenever you need one of the 5 editable options, use some code like:

$option1 = get_option('yourprefix_option_name1', $default_value);

What'll happen is your default option, from the XML config file, will get assigned to $option1 if no option with that name exists yet. But if that option does exist, it'll pull it's value from the database. Then just use $option1 everywhere you need to use it or pre-populate a text input.

Remember, when you put in your migration script at the start of your plugin code, the last thing it needs to do is delete that record from the options table to prevent the code from being run again:

delete_option('yourprefix_original_option_name');

Hope that does it for you! Good luck.

Dzynit, please either let me know that your problem is solved and close this question (and hopefully my input was helpful to you in reaching that solution), or else tell me what I can do to help you get there. I came here because I got an email that this question had been abandoned. I will be very disappointed if YOU abandon it after I attempted to assist.

Sorry, I had to set it aside for a couple other projects. It's going to still be another day or so before I can get back to it. It's kind of a pain and complicated thing. Originally it was built by someone else in smarty and for some reason they chose an encrypted xml file to initially add the text for the fields, but instead of separating it, it's all in one wp-options field. To break it up, I need it to be individual rows with a name and 4 values, so using the wp-options would not only make it worse by still piling separate info into each option but also make the wp-options terribly large. And I'd have to rewrite pretty much the whole program to use the wp calls. As soon as I can get back to it, I'm going to focus on a function that separates, inputs only the new data for upgrade, then just goes back to using the same method to make it quicker to get through the project.

Thanks for the help. I don't usually forget and leave these questions open so long. I'll get it closed.

I understand that the format of your data isn't ideal for the wp-options table, but I would worry more about the fragility introduced from having to create a new database table than about the size of wp-options. Having a custom table means you're going to have to not only create that table with custom SQL, but also query that table directly through the $wpdb->query() method whenever you need to use the options stored there. An unexpected change to future Wordpress versions could break your plugin. And sadly, I'm talking from personal experience. My first WP plugin used a custom database table.

But, since you have decided it's worth the tradeoff, and since this question did ask how to do that very thing (and so people viewing this question in the future might also want to be able to do this), I'll lay out the Wordpress approach for doing it when I get home from work this evening and have some time to work out a proper solution.

I understand getting distracted, by the way, and I know it can take time to do this sort of work. I just want some acknowledgment that you're still interested in the question you asked.

MasonWolf,

If you're willing to keep trying on this - I'll keep the question open and I can definitely get back to the project no later than Monday afternoon. We kinda miss each other for instant communication. I leave about the time you get home.

If you can help me get started, I'm sure I could run with it. My biggest problem is getting the code going ;)

Thanks much!

Hey, I'm totally willing to help. And I really wanted to get to this last night after work. Unfortunately, last night I didn't get home until 9pm my time (not normal, believe me). And I still wasn't done with everything I needed to do. I'm going to make an effort to cobble together some sample code for you tonight though. Sorry our schedules are so out of sync for this sort of thing.

No problem at all. I'm just grateful to have some help. That's always where I get stuck when I have a lot going on - the start of something.

I don't want to leave this question hang. I'm going to use your code to get things going tomorrow afternoon. I'll comment back on how things go even though the question is closed. Thanks a lot for your time and help!

Glad to hear it, and thanks for the points. One other thing I should have mentioned is that to make sure you only run this code when it might make sense, use the plugin activation hook. You can use that to wrap all your upgrade code, including this table creation script, in a function that only gets called if your user activates your plugin (which always happens at the end of upgrading). This is obviously much better than having it check every single time the plugin runs.

Check out this article explaining more about it: http://codex.wordpress.org/Function_Reference/register_activation_hook

For instance, if your plugin is wrapped in one class file, and your upgrade code resides inside a method named 'activation', then at some point in your constructor you'd want to have this bit of code:

register_activation_hook( __FILE__, array( $this, 'activation' ) );

MasonWolf,

I finally got some time to concentrate on this and think I got it pretty simplified. Still have to finish some things up.

Going with an upgrade button that only displays if the user has lite version and now installing pro. The the button doesn't show anymore after upgrade.

 

$settings_old = get_option('autoseo_options');
$settings_new = get_option('autoseo_options_pro');

foreach($settings_old['locale'] as $key => $item) {
	if ($key == 'title' || $key == 'description' || $key == 'h1_status' || $key == 'keywords' || $key == 'alt_attributes') { 
		foreach($settings_new['locale'] as $k => $v){
			if ($key == $k) {
				$settings_new['locale'][$k]['enable'] = $settings_old['locale'][$k]['enable'];
				$settings_new['locale'][$k]['tooltip'] = $settings_old['locale'][$k]['tooltip'];
				$settings_new['locale'][$k]['correct'] = $settings_old['locale'][$k]['correct'];
				$settings_new['locale'][$k]['problem'] = $settings_old['locale'][$k]['problem'];
				$settings_new['locale'][$k]['important'] = $settings_old['locale'][$k]['important'];
			}
		}
	}
}
update_option('autoseo_options_pro', $settings_new);

Select allOpen in new window

This also saved from having to make changes throughout the existing code that runs the tool.

Thanks very much for taking time to help. Wanted to post back the working end result.

I think this is going to make your life so much easier. There are situations in which you need a custom table, but in general, look for ways to avoid it if you can.

Have a great one!