SQL Query Help please

[dew1960]

Hello again!

I am beginning to learn to write my own querys for MySQL and I tried this to select all items from the database that have the word 'defiant' in the name.


Here is what I tried:

 

SELECT "*" FROM items WHERE name LIKE 'crude defiant *';

It isn't working so I am asking how to do this properly.

Next I want to set sellrate to a number like '3'

(I have some custom vendors set up to sell Defiant armors but the sell rate is like 60 or higher and my players can not afford to buy it at lower lvls.
If I lower the sell rate, it will be easier for them.

I don't want to set it to the same number for all items, just the defiant stuff and then a different rate for each type of defiant gear.

Example: sellrate =1 for 'Crude Defiant *', sellrate = 2 for 'Simple Defiant *', etc. I will have to do like trial and error to see how it works out in game. I can figure all that out and edit the # to input in the sell rate field, if I can just get the basic query above in the correct format, LOL.

Thank you so much for helping me with this if you will be so kind.

dew1960
(SQL Dummy)

P.S. - Is there some way to edit a group of items like that in an editor like GeorgeS Item Editor or some other editor?

[Mariomario]

If you are wanting to find the entire length of a string contained anywhere within the words you would need to use a % wildcard surrounding the name you want to find. Use this query instead:

SELECT * FROM items where Name like '%Crude Defiant%';

This will return all items containing the words Crude Defiant. After that you can add in whatever extra you want, just make sure your surrounding the string with the % wildcard.

Edit::

I forgot to add in for setting the sell rate as well. You would just adjust your parameters as well for whatever you want your sell rate to be for the gear. Your generic statement would be:

Update items set sellrate = 1 where Name like '%Crude Defiant%';

Update items set sellrate = 2 where Name like '%Simple Defiant%';

and so forth.

I would honestly suggest just adjusting your price for the items though. The price is expressed in copper pieces and just know that 1000 CP = 1 PP. So using this method say I wanted all Crude Defiant gear to cost someone 50 platinum each piece. 50 x 1000 = 50,000 so you would input into the query:

Update items set price = 50000 where Name like '%Crude Defiant%';

If you wanted to set the weapons to a different price than the armor you could alter your statement to something like:

Update items set price = 60000 where Name like '%Crude Defiant%' and damage > 0;

This would target only the Crude Defiant weapons as they contain a damage amount. There are many ways to alter the statement to target only the particular group you want effected.

You can tweak it from here.

[dew1960]

Thanks, I was trying to use '*' as wildcard, but that was improper. I appreciate the support.

I like how you did that in one line. I was thinking I had to select first and then update, doh!

I am learning from good examples

[Mariomario]

No problem, we were all new at one point, just glad i could help.

Select from is just a great tool to use to ensure your parameters choose the correct categort of items or spells or whatever you want to edit because just remember anytime you run a query the change is permanent unless you make a backup of that particular table.

[c0ncrete]

it's not necessary to surround the entire string with the % symbol in a wildcard search. you just need it in the position(s) that you are not specifying the characters to match. if you only want item names that begin with "Simple Defiant", you would only need to match for "Simple Defiant %". if you were trying to find all types of defiant gear, then you would use "% Defiant %".

more info on the subject (and sql queries in general) can be found here:

http://www.w3schools.com/sql/sql_wildcards.asp