[Resolved] size of MySQL row in KB

ucbones

Hey,

I've written a simple PM system for my site, but I want to set a max inbox size for each user. I can get the total size of a table, but not the size of a given individual row. Is this possible?

Many thanks,

Dominic

sid

here is the size in kb for the row with id = 1:

$id = 1;

$query = "SELECT FORMAT(((LENGTH(field1) + LENGTH(field2) + LENGTH(field3) + LENGTH(field4) + LENGTH(field5)) / 1024), 2) AS row_size_in_kb
FROM dbtable
WHERE id = " . $id . ";";

i didnt test it but suppose it is close to a working solution - field1... youll have to replace with your fields obviously

sid

i tried it in one of my tables and it works a-ok!

Shrike

Seems a little strange that you need to do that at all, if each entry occupies one row why not just limit how many rows a user can have?

ucbones

Thanks sid, that looks perfect.

Shrike, I need to do that to stop people PM'ing huge amounts of text around, and because it look cool and requires little effort

Shrike

Yes but it seems that you are putting all PM's for one user in one table row, and hence need to limit the size of the row. I would take a different approach to the DB design 🙂

ucbones

No way! Haven't done that, just wanted a place to start from for calculating row size. Think I need to rethink my design anyway, to allow PM'ing multiple people...

cheers for the advice though!!

Dominic

Shrike

Ok 🙂

Why not just limit the number of chars allowed in a field, rather than calculate the actual disk size? 🙂

ucbones

That would be something I'll do as well, but I'd quite like to allow lots of little messages to be sent as well, rather than only allowing a few messages. If that makes sense, not quite sure how to write that... :rolleyes:

Cheers,

Dominic

Weedpacket

Can you SUM(string length of a field)? And give each PM a separate row, with the user as another field?

ucbones

Hey,

Can you get me a few more details on how to do that?

The structure of my table is roughly:

ID (INT, auto_increment)
userto (INT)
userfrom (INT)
subject (varchar 25)
body (TEXT)
is_read (TINYINT)
is_moderated (TINYINT)

Is that going to be ok?

Cheers,

Dominic

Weedpacket

Originally posted by ucbones
Is that going to be ok?

Yeah, that looks good...

SELECT SUM(I haven't looked up the function for this bit) FROM the_table WHERE userfrom=$whatever