itemmarket.php error

[wykydz]

i get this error when you go to the item market

QUERY ERROR: Unknown column 'imQTY' in 'field list'

Query was SELECT im.`imPRICE`,`imQTY`,`imCURRENCY`,`imADDER`,`imID`, i.`itmid`,`itmname`, u.`userid`,`username`, it.`itmtypename` FROM `itemmarket` im LEFT JOIN `items` i ON im.`imITEM` = i.`itmid` LEFT JOIN `users` u ON u.`userid` = im.`imADDER` LEFT JOIN `itemtypes` it ON i.`itmtype` = it.`itmtypeid` ORDER BY i.`itmtype`, i.`itmname`, u.`username` ASC

[Damagedcity.com]

The ` imQTY` field is missing in this case

add this sql in database

ALTER TABLE `itemmarket` ADD `imQTY` INT(11) NOT NULL default '0';[php]

[rulerofzu]

No dont do that why would you use int(11)???

Are you really EVER going to have that many? Should a player have that many??

mediumint would suffice

[wykydz]

Thank you alot

[rulerofzu]

No huge scary risk well apart from if your game is successful then the difference is quite large.

Standard fix? how so since when was int(11) the default standard?

[rulerofzu]

Well thanks SRB didnt know the default was 10 I just use whats appropriate!

[KyleMassacre]

Hmm interesting, I have always used int (11) just cause thats how everything else is unless I just need something with a small reall small value so I just use like tinyint (1) does that work is there something better

[Arson]

Ah yea meant 10 lol I knew it was one of them 10 or 11, but yea doesnt really matter if you can secure you code theres no problems either way. Being accurate does leave less room for error and bugs but being smart about the script can be just as good but either way he has multiple answers to try so he should be ok.

My phpMyAdmin defaults INT to 11.

When does it start to matter what field type you use? (besides for the length of the values)

I've been under the impression that its more the value being stored than the field type.

Would INT(11) really be any slower than mediumint when the values aren't astronomical numbers? Furtnermore, when the values aren't going to be that long, is there really even a difference when it comes to INT(10) and INT(11)?

[sniko]

I think they are referring to the amount of memory it will take up from using INT(11) and MEDIUMINT.

[Arson]

I think they are referring to the amount of memory it will take up from using INT(11) and MEDIUMINT.

I'm asking what the real difference is though. If a number stored in a field is 1000, is it going to take up more space as INT(11) or MEDIUMINT, wouldn't the amount of memory depend on the size of the number?

For example 10 should take less memory than 1000 even if they are both INT(11) or both MEDIUMINT. Catch my drift?

[Seker]

I'm asking what the real difference is though. If a number stored in a field is 1000, is it going to take up more space as INT(11) or MEDIUMINT, wouldn't the amount of memory depend on the size of the number?

For example 10 should take less memory than 1000 even if they are both INT(11) or both MEDIUMINT. Catch my drift?

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

TINYINT 1 Byte

SMALLINT 2 Bytes

MEDIUMINT 3 Bytes

INT 4 Bytes

BIGINT 8 Bytes

Negligible difference, but it's there.

Edited May 20, 2012 by Seker

[sniko]

I catch your drift, now, and please don't shoot me dead If my memory serves me incorrectly, but...

Memory will be used, even if, to the human eye - through abstraction - it looks like 4 bits are being used, ie. 1000.

So, INT(11) will have 11 visible bits allocated to that section in memory for integers, and placing 1000 in it, it will be in memory as;

00000001000

MediumINT

The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

INT

The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

That's quite a difference, in number.

Now, to your question.

You place "1000" in unsigned INT, 0000001000

You place "1000" in unsigned MediumINT, 00001000

That is 2 extra bits being used for, well, nothing.

You have 1,000 users, and they have, typically, the value "1000" in this specific column, that is;

1000 * 2 = 2000 extra bits being used.

Sure, you could argue that 2000 bits is not even 2KB(?), but that is extra 'strain', even if minimal, on a server, but consider it on a bigger scale, Facebook for example.

I think it's the 'picky' developers that usually take this into account, and therefore try and force their ways onto others, saying it's good practice.

My personal view is that it is good practice, trying to conserve memory.

I don't know why, but this certain reply seemed to have a 'fetish' towards the number "1000" :P

I hope that helped, moreover, correct.

[Seker]

I catch your drift, now, and please don't shoot me dead If my memory serves me incorrectly, but...

Memory will be used, even if, to the human eye - through abstraction - it looks like 4 bits are being used, ie. 1000.

So, INT(11) will have 11 visible bits allocated to that section in memory for integers, and placing 1000 in it, it will be in memory as;

00000001000

MediumINT

 

INT

 

That's quite a difference, in number.

Now, to your question.

You place "1000" in unsigned INT, 0000001000

You place "1000" in unsigned MediumINT, 00001000

That is 2 extra bits being used for, well, nothing.

You have 1,000 users, and they have, typically, the value "1000" in this specific column, that is;

1000 * 2 = 2000 extra bits being used.

Sure, you could argue that 2000 bits is not even 2KB(?), but that is extra 'strain', even if minimal, on a server, but consider it on a bigger scale, Facebook for example.

I think it's the 'picky' developers that usually take this into account, and therefore try and force their ways onto others, saying it's good practice.

My personal view is that it is good practice, trying to conserve memory.

I don't know why, but this certain reply seemed to have a 'fetish' towards the number "1000" :P

I hope that helped, moreover, correct.

This seems more in the range of general good practice than it does just personal preference. Code optimization should always be done, not in general terms, but on very specific levels.

On top of how you laid it out, sniko.

Each time a query accesses that number, it's also accessing all 11 of those lines. As far as memory usage, I don't know if this is separate, but it seems the queries would be faster if they were limited only to what's needed.

Example:

INT(11) 1000

Your query would be looking for 00000000000 and changing it to 00000001000

Whereas with SMALLINT

Your query would only be looking for 00000 and changing it to 01000

This make sense?

[sniko]

Isn't that what I've said, but simpler? :p

But, my answer to your question, yes, it makes sense.

[Seker]

Isn't that what I've said, but simpler? :p

But, my answer to your question, yes, it makes sense.

After re-reading both posts again, twice, I realized that is exactly what you were saying. :P

In any case, faster queries = faster page loads and faster page loads = happier users. So, I think the original suggestion from RoZ would be the better way to go.

[Arson]

Thanks guys you have really shed some light on the subject for me.

However, how about when using something like INT(4) for 1000? Assuming I am correct in thinking that with INT(4) you can have from -9999 to 9999?

What would be the difference then in using INT(4) or MEDIUMINT(4)?

[Seker]

Thanks guys you have really shed some light on the subject for me.

However, how about when using something like INT(4) for 1000? Assuming I am correct in thinking that with INT(4) you can have from -9999 to 9999?

What would be the difference then in using INT(4) or MEDIUMINT(4)?

I would assume it all comes back to this again:

SMALLINT 2 Bytes

MEDIUMINT 3 Bytes

INT 4 Bytes

By default, INT, regardless of the character limit, will take up more space. Again, while not noticeable in small databases, can definitely clear out the empty corners as the user base grows.

[sniko]

I believe so.

MediumINT

The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

INT

The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

 

INT(11) will have 11 visible bits

Therefore,

INT(4) will have 4 visible bits

And through the process of abstraction, it will only show, to you, the user, "1000" and not "0000001000".

This theory may be incorrect, I'd suggest approaching one of the more 'experienced' developers

[Arson]

So basically, you can set them whatever number (within the limits of the field type), but by default each field type is going to reserve(?) it's own predefined amount of memory (INT 4bytes, MEDIUMINT 3bytes etc) (which is why they have the limits on how many numbers can be used).

From what I'm gathering one could compare a table row to a sort of partition, each row upon insertion in the database will automatically reserve the amount of memory equal to the memory set by it's respective field types.

So rows in a table with say, `id` INT(11), `level` TINYINT(2), `money` INT(11), as the fields, will automatically take up 10 bytes each; However the data in these fields will determine how much of those 10bytes is actually used.

[Arson]

Another thing I have gathered from this is that one could go through the default MCCodes database and eliminate a significant amount of overhead simply by changing the field types to properly suit the data they hold. ie. All the INT(11)'s that are holding numbers that could be held by TINYINT and MEDIUMINT.

[Seker]

Yes, I believe it's all allocation. It basically sets up a safe-zone so you won't run out of memory.

You could definitely clear up some unused space by changing all the field types, I think. I suppose it was Dabs' own form of a safety net. Was the most common choice, so used it for everything.

[Arson]

I guess so, but after learning what I have from this thread, using INT(11) for something like item quantity seems silly, let alone using it for things like unread mail/events/announcements.

You probably wouldn't open up more than a couple hundred bytes, but in the long run that could turn into MB's if you have enough rows in the table...ie..if you ever plan on your game becoming successful. Personally, I'll plan for success.

[Seker]

I guess so, but after learning what I have from this thread, using INT(11) for something like item quantity seems silly, let alone using it for things like unread mail/events/announcements.

You probably wouldn't open up more than a couple hundred bytes, but in the long run that could turn into MB's if you have enough rows in the table...ie..if you ever plan on your game becoming successful. Personally, I'll plan for success.

I completely agree. Already altered the SQL's for a few of the custom mods I'm working on.