EQEmulator Forums - MySQL database query help

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Development::Database/World Building (https://www.eqemulator.org/forums/forumdisplay.php?f=596)

- - MySQL database query help (https://www.eqemulator.org/forums/showthread.php?t=35609)

MySQL database query help

First off, I should say, I don't ever mess with the DB at all, so I am really unfamiliar with it. And I have absolutely no MySQL experience. Google just returned a lot of confusing results, no matter what I searched for, and I know a lot of people around here are familiar with this stuff, and can maybe give me an answer.

I need a way to get the DB to read a list from either a text file or a spreadsheet or whatever, I can format that easily enough. It will be in the format
1 Bill
1 Sarah
4 John
etcetcetc

I need to read the name of the person, find it in the characters table, get the account ID from that table, and then put the number (the first column) into the status field for that account ID. Could anyone help me out?

There's two things that it appears you want to do here.

1 - Perform an update query
2 - Read values from a 'CSV' file (import from an external source)

I don't know if MySQL is capable of performing a translation in a command-line script. If you are using Excel or some other spreadsheet
program, MySQL MAY be able to read from that table with no issues, but I can't say for sure.

Your best bet is to probably just create a custom table in your PEQ database and use it as your spreadsheet. This will make a query
much easier to code and run. Either Navicat or HeidiSQL are both excellent GUI database management tools and I know that
HeidiSQL will let you import 'CSV' files (comma seperated value.)

(You shouldn't lose this table on an update unless they sneak in a new table into the base with the same name.)

As far as coding it, that shouldn't be too hard, but I won't be much help to you there...

Also, remember to keep track of your characters vs. user account names. If you happen to include two (or more) character names from
the same user account, the last status to be updated will be the current value.

Uleat is correct. You would need to put your information into a table in the DB first so you could use it in the query.

If you had a table called cttemp:

Code:



CREATE TABLE IF NOT EXISTS `cttemp` (

  `t_id` int(11) NOT NULL AUTO_INCREMENT,

  `t_charname` varchar(100) NOT NULL,

  `t_status` int(11) NOT NULL,

  PRIMARY KEY (`t_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Once populated you could run your update like this:

Code:

UPDATE `character_` RIGHT JOIN account ON (account_id = account.id) RIGHT JOIN cttemp ON (character_.name = t_charname) SET account.status = t_status

Uleat is also correct about multiple characters on the same account. If one character has status 1 and another has status 2, whichever one is processed last will set the status.

Thank you guys very much. The situation was remedied by hand already, but I could see what you've told me being very helpful in other circumstances.