New sql table create question

[AdrianD]

I created a new table and inserted the information I wanted to put there. I'm having a hard time giving each item inserted a unique table.id number. How do I do this? I refuse to waste any more time searching for how on a question that can be answered in a couple minutes. You don't want to know how much time I wasted...

[moofta]

with what did you create the new table- by hand or using Navicat/HeidiSQL? (I would suggest doing so if you're not very familiar with SQL in general)

I use HeidiSQL and it's pretty simple to use, surprisingly fast,.. and free!


When you create your table, create the first column in the "Basic" tab and call it "id".
Give it a data type of "INT".
Under the "Default" column, select "AUTO_INCREMENT".
Then click on the "Indexes" tab, click on your id field, then click on "Add". An index called "Index1" will appear.
Click on the "type/length" entry for Index1 (which probably says "PRIMARY"), select "PRIMARY".
Then click "save" at the bottom


Manually you can do this when creating (just copy/pasting a table I created):-

CREATE TABLE IF NOT EXISTS `zone_drops` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zone_id` int(11) NOT NULL,
`zone_name` varchar(50) NOT NULL,
`item_id` int(11) NOT NULL,
`item_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you use Navicat or other, there will be a way to set the default value to AUTO_INCREMENT, and also to make it the primary key. That way your ID will always be unique and auto-generated.

[AdrianD]

Thanks for the quick response! That's exactly what I was looking for, I think...

I'll be back in a couple minutes to verify.

Appreciated.

EDIT:

That did the trick, thank you very much.

I'm two weeks into all this stuff and much of it is new and over my head. It's relieving to get fast resolution to a simple roadblock like this.

[Uleat]

I know it's hard to peruse the actual peq dump sql...

If you can find an existing table similar to what you're looking for in the db, try going back to the SVN portion of the eqemu repository and see if you can find
an update sql that modifies it in the smaller file.

Grepwin is a great tool for finding all occurrences of the search criteria inside of a folder.

[moofta]

I'd also highly recommend installing the peq database editor using xampp (it's way easier than it sounds, but you might need to refer to the thread on the PEQ forum- peqtgc.com).

Other than the fact that it is a useful tool in it's own right, you can set sql logging=1 in the config. This means that it will log every query made against the peq database as you use the editor.

You can then look back through the logs to work out what queries were made to show the info you want. Some of the relationships like spawns, spawn groups, loot tables etc are relatively difficult to get your head round otherwise.

[AdrianD]

Thanks a bunch guys.

I have attempted to search for some things I want to change in the db such as skill_caps and spells_new but, my search was fruitless and I moved on to something I could actually accomplish. The main benefit to doing this stuff, even if there is a finished product out there that suits my needs, is that I'm very high on the learning curve and I imagine this will remain for some time. I could save a bunch of time by looking harder, although.

I have the db editor but I haven't installed it yet (see above). I'm sortof dividing my time on this project into making progress on my eqemu goal and learning about everything I have the patience for at that particular moment. They work hand-in-hand, yeah.

It's very duanting at times. I'm just a guy who does not work in the industry but has had a fleeting memory ergo, a vision for what is now possible.

Coming up with queries to change 50k lines in a tiny fraction of the time it would take to manually enter is gratifying, considering I thought a query was like a question two weeks ago.

Thank you for your help.

[moofta]

It might be worth grabbing a copy of something like "SQL for dummies" or "SQL in 24 hours" because your first hurdle is learning how to use SQL in the first place( http://it-ebooks.info/book/1498/ is free and I think, legit).

I basically learnt from a book like that before I went to college, 20 years down the line I'm STILL having to google this stuff from time to time :)