[SOLVED] row size in kb

[shadiadiph]

how do i find out the row size in kb? is it possible to automatically insert the size I have a mail table and have the field is there a way of automatically creating the value on insert by resetting the values of the field??

 

`messagesize` int(255) NOT NULL default '0',

 

but am unsure how to do this any ideas??

[gizmola]

why are you declaring the column to be an int(255)?  If it's an int, the largest variable that can be stored is an integer.  The 255 is for display purposes. I know this has nothing to do with your question, but it bothers me when people do those types of declarations.  It's not a varchar().

 

To your question -- I have to make some assumptions because your question is really unclear.  What I assumed is that you have another column in this message table that is of type TEXT.

 

If that is the case, then theoretically you could write an insert trigger that uses the CHAR_LENGTH() function on the TEXT column to get the size in characters of the message.  This is character set aware, so it will truly be characters and not Bytes!

 

If I read you correctly, your solution would be to write the insert trigger. 

 

Yes, I'm sure the question is -- how do I write a trigger?

 

 

Check this out --- should help you.  One thing about mysql triggers -- the mysql root user needs to add them typically.

 

mysql> describe mes;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| message     | text    | YES  |     | NULL    |       | 
| messagesize | int(11) | YES  |     | NULL    |       | 
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> delete from mes;
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER |
mysql> 
mysql> CREATE TRIGGER message_Insert_Trg BEFORE INSERT ON mes
    ->   FOR EACH ROW BEGIN
    ->     SET NEW.messagesize = CHAR_LENGTH(NEW.message);    
    ->   END;
    -> |

DELIMITER ;Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> DELIMITER ;
mysql> insert into mes (message) values ('This is a string of a particular size');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mes (message) values ('Another string, but this one is a different size for sure');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mes;
+-----------------------------------------------------------+-------------+
| message                                                   | messagesize |
+-----------------------------------------------------------+-------------+
| This is a string of a particular size                     |          37 | 
| Another string, but this one is a different size for sure |          57 | 
+-----------------------------------------------------------+-------------+
2 rows in set (0.00 sec)

[shadiadiph]

that was just something i saw somewhere when i first tried making a table and have used it ever since i will stop that now.

 

In answer to your question yes there are other fields in the row subject meassage file attachments if applicable this is why i need to find out the value of the row in kb so i can add them up as the mailbox has to have a limit in size in kb or bytes is there no size attribute that can be used?

[gizmola]

Yes, LENGTH() will give you the size in bytes().  The rest of what I wrote you still holds true.  If you want the message size to include the multiple columns, simply get LENGTH() for all of them and add em up in the trigger.

[shadiadiph]

mm couldn't i just do it using something like this?

 

but its not working.

 

$welcomesql = " insert into mailtbl (fromuserID, touserID, fromusername, tousername, mailfolder, seen, subject, message, timereceived, datereceived) values ('0', '$userid', 'webmaster', '$username', 'inbox', 'No', '$welcomesubject', '$welcomemessage $welcomefooter', now(), now())";
$result = mysql_query($welcomesql);
$messageid = mysql_insert_id();


$messsize = "SELECT * FROM mailtbl WHERE mailID='$messageid';
$messresult = mysql_query($messsize);
while ($row = mysql_fetch_array($messresult)) {  
$newsize += $row['Data_length']; 
} 

$addmessagesize = "update mailtbl set messagesize='$newsize' where mailID='$messageid'";
$resultsize = mysql_query($addmessagesize);

[shadiadiph]

ok i get what you mean by strlen is the same as the amount of bytes.

 

I just tried this but it doesn't work either what am i doing wrong or maybe this is turning into more of a php question?

 

$msize = "SELECT * FROM mailtbl WHERE mailID='$messageid';
$mresult = mysql_query($msize);
$row = mysql_fetch_array($mresult); 
$length = strlen($row);
$kilobites = $length/1024;

$addmessagesize = "update mailtbl set messagesize='$kilobites' WHERE mailID='$messageid'";
$resultsize = mysql_query($addmessagesize);

[gizmola]

Ok, so first off, in regards to doing it procedurally using 2 queries and some PHP code --- yes absolutely.  There is nothing automatic about that, of course, but it's certainly possible. 

 

If you do go that way, then as an aside, make sure you initialize your $newsize variable to zero before you use it.

 

Currently your thinking isn't quite right.  You should not get more than one row back, so there's no reason to fetch in a loop when there should only be one row that matches a mailID. 

 

Regardless of that, you need an inner foreach loop on the $row variable.

Also you should specify MYSQL_NUM or you'll get double the columns in the array, because you get both a numeric index'd array and an associative array with the key being the column name.  You only need one or the other here, so I suggest to just specify the numeric one.

 

At minimum your code should be something like this:

 

$newsize = 0;
while ($row = mysql_fetch_array($messresult, MYSQL_NUM)) { 
  foreach ($row as $val) {
     $newsize += strlen($val);     
  }
} 

 

 

 

[shadiadiph]

thanks alot for your help that seems to work fine