Get MySQL columns width ?

Question

Hello. I was wondering if I can somehow convert a column header text form MySQL into actial width size pixels. I am trying to generate a PDF from the database and I want it to automatically adjust column widths. As I will use it for many tables, the width must differ so I should be able to see it like: "The header for this column is called CompanyID so it should have the width of 40". Can I achieve something like this ? Can I convert text characters length to actual width ?

Thanks.

Answer 1

I'm not sure that's possible. I would imagine the best compromise would be to select an arbitrary width for a character then multiply it by the string length of the mysql field name.

Answer 2

You need to have some way of calculating the font metrics which will be used in the PDF - which PDF generation library are you using?

Answer 3

If I'm not misunderstanding, this is entirely dependent on the font that you're using, and that doesn't have anything to do with the data stored in MySQL. You could certainly come up with a way to approximate it, but it heavily depends on features of the font that you're using. For example, Is the font monospaced? If not, a column named "llllll" is going to be much thinner than one named "MMMMMM", even though they're the same number of characters.

Answer 4

Though I'd strongly recommend using a font whose characters are all of equal width (Courier New, perhaps) and then just multiplying the string length by the width you determine one character to have, you could use a bit of server-side code to do the trick. I'll be speaking in PHP from here on. : )

Assming you have access to the GD library, you can use the imagettfbbox() function to return the coordinates of the bounding box of the string; following is an example:

<?php
$size = 15;
$angle = 0;
$path = 'name_of_font.ttf';
$bounding = imagettfbbox($size, $angle, $path, "This is some text.");

// $bounding is now an array of values that contain
// information regarding the invisible "box" around
// the text; the only element you're really interested
// in, though, the one that will tell you the width of
// the text, is $bounding[2]
?>

So, whenever you need to deduce the width of a string, simply call this function and use the element at index 2 of the resultant array to get the information you need. Hope I've helped.

Answer 5

You need a function that will calculate the width of a string, given (as Mike Houston says) the font (face, size, etc) that it will be rendered in. It's nothing to do with MySQL, and everything to do with printing/rendering text, so look to your PDF library. Or, possibly, your operating system's printing/graphics library.

Answer 6

Thank you for your replies. I am using FPDF library. The font setup goes like this:

$this->SetFont('Arial','',12);

I need to come up with a light way to do this. I would generate PDF's based on large datasets. So I don't want to spend much resources for this conversion...

Answer 7

FPDF happens to have a GetStringWidth() method that predicts the width of a string.