DB Query, moving data from table to table.

[Figback65]

Hey all,

I am tryen to collect data into a custom table via script(which I can do) but then restore it into another table based off the character ID#(I can't figure out)

The background is this. I have instances on my server and when a player zones in I am storing information like their exp, level, name, skill values, memmed spells ect. After they complete or leave the instance I am trying to restore the information back into the correct tables so everything is automated but I cannot figure out how to make the query verify the characterID then replace the information back into the table. I will post examples below. I think my main problem for example is in character_skills and character_memmedspells the information is stored repetitively down in rows instead of 1 row per ID#, meaning, each character ID will have 8 rows for memmed spells and 76 rows for skills. In my table I have everything done in 1 row. I have been reading the leaderboards plugin and anything else I can get my hands on but I cannot figure it out.

I need to get this saved information setup
instanced_saved_info table
659777 Reboot 50 164708608 200 200 200 200 200 200 200


to this setup to restore saved info back
character_skills
659777 0 60
659777 1 65
659777 2 60
659777 3 60
659777 4 60
659777 5 60
659777 6 60
659777 7 60
659777 8 60
659777 9 60
659777 10 60
659777 11 60
659777 12 60
659777 13 60
659777 14 60
659777 15 66


This is an example of what needs to be done.
Is this even possible? Or will I have to store individual rows per ID#?

EDIT: I started with globals but then realized how much information was being stored and it was just a huge mess. Using a table is much cleaner and easier to read. I also research sites like stackoverflow but I do not exactly know the proper terminology to help find my answers.

[Kingly_Krab]

If you're using a script to store the information you could just as easily write a script that would set an array to the row information of your custom table and then create table rows automatically based on the information provided. May I ask why you need to do this? I'm pretty sure our character saves are working just fine.

[Figback65]

I explained why in the post, I am doing instances. For example my unrest instance is max level of 30. If the player wants to do the instance and lets say is level 50. Then the query will store their level 50 information, their name, level, experience, skill values, memmed spells and the spell ID in the spell slot. As they zone in they are deleveled to 30 and skills are set to the max value for a level 30, and all spells memmed are unmemmed so they don't freeze up. With that information stored, as they leave the instance then the stored information is pulled from my instance info table and restored into the characters proper tables. Thus they zone out and are restored to proper level, skills are set back to their proper saved values and spells are rememmed back into their saved spots. This way it is all automated. Saving hassle from forcing players to always having to remem spells and skill values being off.

I can store the information with a scripted query, I have that all setup. My problem is pulling the information out of my table and restoring it back into the character_skills, character_memmedspells, etc etc. I tried doing an array but I cannot get it setup properly.

EDIT : I will post how far am, trying a few more times.

[Zaela_S]

Your table sounds like it's straight out of a "how not to design a database 101" textbook. Having everything in one loooooooong row is tedious (for exactly the sort of trouble you're having -- interfacing with sensible, multi-row tables is a pain, not to mention having to write super-long queries to get anything done) and hard to maintain (although I guess futureproofing isn't much of an issue here...).

If you had multiple tables that mirrored the originals (with perhaps some extra columns) you could use the "INSERT INTO SELECT (http://www.w3schools.com/sql/sql_insert_into_select.asp)" syntax to copy back and forth directly.

[Figback65]

So, what your suggesting I should do is make multiple tables instead of 1?
Like, Have an
instance_saved_skills
instance_saved_spells
instance_saved_level

and just mirror it from the originals?
I would then be running more queries, one per table, that is better than 1 long query?

[Shin Noir]

I'm half glancing at this, but:

I need to get this saved information setup
instanced_saved_info table
to this setup to restore saved info back
character_skills

the above needs at least fields names to make any remote sense..

Perhaps clarify what you're after, and the use case. for example:

When a player enters a zone, info is taken from X and stored into instanced_saved_info.
When a player exits, info is taken from instanced_saved_info and placed back into X.

A part of me thinks you want the above, another part thinks you're doing some sort of temporary snapshot of a player and emulating a shroud system, making them level 30 and giving them rigged spells while in an instance.

Depending on your use case, you may be going down a much darker rabbit hole than you need to.

the tl;dr answer is going to be, a lot of messy scripts iterating pivots to restore data back.

[Zaela_S]

So, what your suggesting I should do is make multiple tables instead of 1?
Like, Have an
instance_saved_skills
instance_saved_spells
instance_saved_level

and just mirror it from the originals?
I would then be running more queries, one per table, that is better than 1 long query?

Like that, yeah. It may actually be more efficient than having one big table with 100+ columns -- you're going to be running multiple queries anyway, since you are restoring to multiple source tables.

Having short rows may make better use of cache, and if you do use INSERT INTO SELECT and avoid reading data from mysql into your script for manual manipulation, you can save some bandwidth and time costs there, since INSERT INTO SELECT can be done entirely internally by the mysql server process (rather than ferrying row data back and forth from your script's process).

[Figback65]

Thanks Zaela, that's what ill do. Ill just make mirror copies and move information over. That should be a lot easier than reorganizing the information being moved. Then last thing ill have to figure out is how and what to move based on the characterID so it only moves the people who zone in and need info backed up.