Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Tools

Development::Tools 3rd Party Tools for EQEMu (DB management tools, front ends, etc...)

View Poll Results: Is this a good idea?
Yes 11 91.67%
No 0 0%
You are completely insane, why ould anyone want that. 1 8.33%
Voters: 12. You may not vote on this poll

Reply
 
Thread Tools Display Modes
  #1  
Old 01-29-2008, 07:00 AM
Dispair2
Fire Beetle
 
Join Date: May 2007
Posts: 28
Exclamation Sql

Yes my SQL statement is a little funny because i have also noticed sme non functional parts of it however i was able to see that the warrior part does work.

This is how the SQL statement looks inside the application:

SQL = "Select id, name, ac, haste, hp, reqlevel from items where reqlevel >= 1 and id = 25462 and classes like %5% and price >= 1 and name = 'itemname' and itemtype = 'something'"

of course i edited it to make it easier to read and post but you can see how it works.

This is alot buggy and will be fixed in the future but it is as functional as i need it to be for testing purposes rigt now.

What happens on the back end is that when you click search it goes through and if a value is present then it adds it to the SQL statement. So above is not always how it looks as it is dynamic and when you type in the id field i believe it disables the rest of the box's. I am not at the actual dev machine now so i can not say definatively.
Reply With Quote
  #2  
Old 02-01-2008, 10:37 AM
AndMetal
Developer
 
Join Date: Mar 2007
Location: Ohio
Posts: 648
Default

Quote:
Originally Posted by Dispair2 View Post
What happens on the back end is that when you click search it goes through and if a value is present then it adds it to the SQL statement. So above is not always how it looks as it is dynamic and when you type in the id field i believe it disables the rest of the box's. I am not at the actual dev machine now so i can not say definatively.
That's how I ended up doing it on mine, otherwise you run into problems.

On a side note, if you already know the item ID (it is a unique value, no 2 items have the same item id), the other fields don't matter. MySQL ignores the rest because they are redundant. However, you don't usually want to make exceptions in your code if at all possible (HUGE PITA to change later down the line), and since it doesn't usually cause problems to leave it in, you should be fine.

I am a little curious about the Select statement:
Code:
Select id, name, ac, haste, hp, reqlevel from items where reqlevel >= 1 and id = 25462 and classes like %5% and price >= 1 and name = 'itemname' and itemtype = 'something'
You mentioned that it's buggy but functional. There are a few things to be aware of (unless you already are) about some of the data types:

First of all, classes requires a bitwise operation because of how it is stored in the items table, because multiple classes can use it, and adding a column for each class can be very hard to read. When I went through discovering the information initially, I put my findings in the Wiki:
Quote:
classes

* The class or classes can use the item. Remember to add up the results.
o 0 = None
o 1 = Warrior
o 2 = Cleric
o 4 = Paladin
o 8 = Ranger
o 16 = Shadow Knight
o 32 = Druid
o 64 = Monk
o 128 = Bard
o 256 = Rogue
o 512 = Shaman
o 1024 = Necromancer
o 2048 = Wizard
o 4096 = Magician
o 8192 = Enchanter
o 16384 = Beastlord
o 32768 = Berserker
o 65535 = Any/All
* IE, Monk (64) + Shaman (512) = 576
So, instead of using LIKE, you should use & (bitwise AND).

Secondly, name should only be = if you know exactly what the name is, and if you're searching for an item, you typically don't. That is why you normally want to use LIKE. I usually put a little spin on it, because running name LIKE '%Cloth%' can be pretty resource intensive (it searches all of the item names in the database to see if they contain the string "Cloth"). However, if you include an option to search using it as either the beginning or end of the name, it speeds up the results substantially (so name LIKE 'Cloth%', name LIKE '%Cap', or name LIKE 'Cloth Cap'). I usually do the same for the ID field, in case I don't know the exact item #.

In conclusion, this is how I would recommend to change the initial query:
Code:
SELECT id, name, ac, haste, hp, reqlevel FROM items WHERE reqlevel >= '1' AND id = '25462' AND classes & '5' AND price >= '1' AND name LIKE '%itemname%' AND itemtype = '0'
__________________
GM-Impossible of 'A work in progress'
A non-legit PEQ DB server
How to create your own non-legit server

My Contributions to the Wiki
Reply With Quote
  #3  
Old 02-16-2008, 05:24 AM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default

Not sure if anyone uses bitwise AND SQL, but I find this info extremely useful. It makes what would take lots of code oftherwise into 1 line.

Say you want to find all items for a mage , you would enter
SELECT classes,id, name FROM items WHERE classes & '4096'

In my job, things like this makes life easier.

GeorgeS


__________________
Your source for EQ database tools
Toolshop is open for business


http://www.georgestools.chrsschb.com//
Reply With Quote
  #4  
Old 02-16-2008, 09:12 PM
AndMetal
Developer
 
Join Date: Mar 2007
Location: Ohio
Posts: 648
Default

Me too, George. I didn't even know about that little jerk until I started messing around with EQBrowser (see page 4 for a working download) and checked out some of the queries. In reality though, I'm not sure how much info is really stored as a bitmask anymore It's just not scalable enough...
__________________
GM-Impossible of 'A work in progress'
A non-legit PEQ DB server
How to create your own non-legit server

My Contributions to the Wiki
Reply With Quote
  #5  
Old 03-25-2008, 11:13 AM
Dispair2
Fire Beetle
 
Join Date: May 2007
Posts: 28
Default Sorry for the delay

I have been very busy with projects at work and sleep i have not done anything at all on this project but wanted to let everyone know it is not DEAD.
Reply With Quote
  #6  
Old 04-14-2008, 12:44 AM
Kayot
Discordant
 
Join Date: Sep 2006
Location: Subsection 185.D354 C.12
Posts: 346
Default

A ton of data is stored as bit masks in the peq database. Every class/race combo field. I use the bit mask to save me a ton of time and code. What used to be several thousand lines of code turned into JUST the sql statment and a few box fills.

Trust me, learn the bitmask. You'll be glad you did.

Also, Arrays are your friends. Don't forget that they count them selfs when doing math, store strings with those numbers and CAN be two dimensional (Even more if you need it, two is almost always enough though.)
__________________
If at first you don't succeed destroy all evidence that you ever tried.

God doesn't give second chances... Hell, he sets you up the first time.
Reply With Quote
  #7  
Old 04-14-2008, 01:31 PM
John Adams
Demi-God
 
Join Date: Jul 2006
Posts: 1,552
Default

Not sure this will help any, but I built some arrays in php for these bitwise comparisons. It was so long ago, I am not really sure why I did it this way, but here it is nonetheless.

Code:
// Gear slots
$a = 1;
$gear_slots=array(
	"Charm" 				=> $a << 0,
	"Left Ear" 			=> $a << 1,
	"Head" 					=> $a << 2,
	"Face" 					=> $a << 3,
	"Right Ear" 		=> $a << 4,
	"Neck" 					=> $a << 5,
	"Shoulder" 			=> $a << 6,
	"Arms" 					=> $a << 7,
	"Back" 					=> $a << 8,
	"Left Bracer" 	=> $a << 9,
	"Right Bracer" 	=> $a << 10,
	"Range" 				=> $a << 11,
	"Hands" 				=> $a << 12,
	"Primary" 			=> $a << 13,
	"Secondary" 		=> $a << 14,
	"Left Ring" 		=> $a << 15,
	"Right Ring" 		=> $a << 16,
	"Chest" 				=> $a << 17,
	"Legs" 					=> $a << 18,
	"Feet" 					=> $a << 19,
	"Waist" 				=> $a << 20,
	"Ammo" 					=> $a << 21
);

//foreach($gear_slots as $row=>$val) {
//	echo $row.$val."<BR>";
//}

// Char Inventory slots (8)
$s=1;
for($i=22;$i<=29;$i++) {
	$inv="Inventory$s";
	$inv_slots[$inv]=$i;
	$s++;
}

// Char Inventory bag slots (8bags * 10slots)
$b=1; // bag 1
$s=1; // slot 1
for($i=251;$i<=330;$i++) {
	if($s>10) {
		$s=1;
		$b++;
	}
	$bag="Bag$b";
	$bagslot="Slot$s";
//	echo "$bag-$bagslot<br>";
	$inv_slots["$bag-$bagslot"]=$i;
	$s++;
}

// Char Bank Slots (16)
$s=1; // slot 1
for($i=2000;$i<=2015;$i++) {
	$inv="BankSlot$s";
	$inv_slots[$inv]=$i;
	$s++;
}

// Char Bank Bag slots (16bags * 10slots)
$b=1; // bag 1
$s=1; // slot 1
for($i=2031;$i<=2190;$i++) {
	if($s>10) {
		$s=1;
		$b++;
	}
	$bag="BankBag$b";
	$bagslot="Slot$s";
//	echo "$bag-$bagslot<br>";
	$inv_slots["$bag-$bagslot"]=$i;
	$s++;
}


// Create Classes Array
$a = 1;
$classes=array(
	"Unknown" 			=> 0,
	"Warrior" 			=> $a << 0,
	"Cleric" 				=> $a << 1,
	"Paladin" 			=> $a << 2,
	"Ranger" 				=> $a << 3,
	"Shadowknight"	=> $a << 4,
	"Druid" 				=> $a << 5,
	"Monk" 					=> $a << 6,
	"Bard" 					=> $a << 7,
	"Rogue" 				=> $a << 8,
	"Shaman" 				=> $a << 9,
	"Necromancer" 	=> $a << 10,
	"Wizard" 				=> $a << 11,
	"Magician" 			=> $a << 12,
	"Enchanter" 		=> $a << 13,
	"Beastlord" 		=> $a << 14,
	"Berserker" 		=> $a << 15
);

// Create Races Array
$a = 1;
$races=array(
	"Unknown" 	=> 0,
	"Human" 		=> $a << 0,
	"Barbarian" => $a << 1,
	"Erudite" 	=> $a << 2,
	"Wood Elf" 	=> $a << 3,
	"High Elf"	=> $a << 4,
	"Dark Elf" 	=> $a << 5,
	"Half Elf" 	=> $a << 6,
	"Dwarf" 		=> $a << 7,
	"Troll" 		=> $a << 8,
	"Ogre" 			=> $a << 9,
	"Halfling" 	=> $a << 10,
	"Gnome" 		=> $a << 11,
	"Iksar" 		=> $a << 12,
	"Vah Shir" 	=> $a << 13,
	"NPC" 			=> $a << 14,
	"PET" 			=> $a << 15,
	"Froglok" 	=> $a << 16
);
I used these arrays to build the combo boxes on my lookup page. Something like this:
Code:
<select name="searchClass" class="advSearch">
	<option value="-1">Pick a Class</option>
	<?php
	foreach($classes as $key=>$val) {
		if(isset($_POST['searchClass']) && $val==$_POST['searchClass']) {
			$selected=" selected";
		} else {
			$selected="";
		}
		echo '<option value="'.$val.'"'.$selected.'>'.$key.'</option>';
	}
	?>
</select>
Again, kinda barbaric, but it works. Good luck with the app. I like the idea.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 11:42 AM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3