Create Images based on SQL data


#1

Hi all, I hope somebody can help me with this.

I have a table which contains a list of start times and finish times.

I also have a php/html page which has a table with the headings in separate columns as 00, 01, 02, 03 up to 23. ( hours in the day). for the purpose of displaying the contents of the said table.

I want my php script to create an image which looks like the one in the url below and reflects the data for each row.

The image is not to scale but just an example of what i’m looking for.

I have had a browse through google and found things like bar charts which could be adapted however i don’t understand the code around drawing images enough.

Perhaps somebody could give me somewhere to start. any help is greatly appreciated.

Kind Regards

Adam


#2

Please disregard the above post. I have managed to get php to draw the image I want however I have run into another problem.

When I subtract the finishtime from the starttime, it will only return a whole number and never a decimal. Is there anything I can do about this? example values would be 08:00:00 and 16:30:00. For which the result should be 8.5 and not 8 which my script is returning.

Here is my code so far:

[php]<?php
############################# draw grid
header(“Content-type: image/jpeg”);

    $height = 21;
    $width = 497;
    
    $im = imagecreate($width,$height);
    $white = imagecolorallocate($im,255,255,255); 
    $black = imagecolorallocate($im,0,0,0);   
    $red = imagecolorallocate($im,255,0,0);   

//draw grid
imagerectangle($im, 8, 1, 28, 20, $black);
imagerectangle($im, 28, 1, 48, 20, $black);
imagerectangle($im, 48, 1, 68, 20, $black);
imagerectangle($im, 68, 1, 88, 20, $black);
imagerectangle($im, 88, 1, 108, 20, $black);
imagerectangle($im, 108, 1, 128, 20, $black);
imagerectangle($im, 128, 1, 148, 20, $black);
imagerectangle($im, 148, 1, 168, 20, $black);
imagerectangle($im, 168, 1, 188, 20, $black);
imagerectangle($im, 188, 1, 208, 20, $black);
imagerectangle($im, 208, 1, 228, 20, $black);
imagerectangle($im, 228, 1, 248, 20, $black);
imagerectangle($im, 248, 1, 268, 20, $black);
imagerectangle($im, 268, 1, 288, 20, $black);
imagerectangle($im, 288, 1, 308, 20, $black);
imagerectangle($im, 308, 1, 328, 20, $black);
imagerectangle($im, 328, 1, 348, 20, $black);
imagerectangle($im, 348, 1, 368, 20, $black);
imagerectangle($im, 368, 1, 388, 20, $black);
imagerectangle($im, 388, 1, 408, 20, $black);
imagerectangle($im, 408, 1, 428, 20, $black);
imagerectangle($im, 428, 1, 448, 20, $black);
imagerectangle($im, 448, 1, 468, 20, $black);
imagerectangle($im, 468, 1, 488, 20, $black);

	//draw shift line
	$starttime = $_GET['starttime'];

$finishtime = $_GET[‘finishtime’];
$stime = substr($starttime, 0, 5); // ignore these for now as they are the time
$etime = substr($finishtime, 0, 5); // strings i will put at the start and end of each line
//start and end points for lines based on times.
$shiftlength = $finishtime - $starttime; // this is the formula subtracting the start and finishtimes
$startpoint = $starttime * 20 + 8;
$endpoint = $startpoint +($shiftlength * 20);

imagestring($im, 2, 350, 5, $shiftlength, $black); // echos 8 not 8.5
imageline($im, $startpoint, 10, $endpoint, 10, $red);

    imagejpeg($im);

#####################
?>[/php]

For those wanting to use this script call it via the following:

[php][/php]

Any help with my arithmetic issue would be greatly appreciated.

Kind Regards

Adam


#3

If you are using a time set up as a string such as “09:00:00”, you would have to use substr and pull out each part. Hour, minutes and seconds and add them into a variables and do math such as… $time=$hour3600+$minutes60+seconds, or whatever… DO the same for the other time and then subtract. This would give you the difference in seconds and you would have to divide by 3600 or 60 depending on the amount to get the actual answer. Lots of calculating, but, quite straight forward.

If you get into trouble figuring it out post some code of this section and someone can help you…


#4

Hi Ernie, thanks for your reply. I understand this and have managed to get it to work however for some reason it produces the correct result minus 1 hour and the following issues.

The string to echo the result returns -7.5
Obviously I swapped around the start and finishtimes to reflect this and got 7.5 righly so.
However I am wondering why this hour has gone missing. I understand that a quick fix would be to add a + 1 to the end however this isn’t doesn’t identify and correct the root cause of this.

[php]
$starthours = substr($starttime, 0, 2) * 3600;
$startmins = substr($starttime, 3, 2) * 60;
$starttime1 = $starthours + $startmins;

$finishours = substr($finishtime, 0, 2) * 3600;
$finishmins = substr($finishtime, 3, 2) * 60;
$finishtime1 = $finishhours + $finishmins;

$shiftlength = ($starttime1 / 3600) - ($finishtime1 / 3600);
$linelength = $shiftlength * 20;
$startpoint = $starttime * 20 + 8;
$endpoint = $startpoint +($shiftlength * 20);

imagestring($im, 2, 350, 5, $shiftlength, $black);
imageline($im, $startpoint, 10, $endpoint, 10, $red);[/php]


#5

Sorry to double post guys but there is no edit function. I also believe that I should have had to swap around the start and finish times to get a non-negative result.

Surely in order to find out the amount of hours between 08:00:00 and 16:30:00 you would subtract 08:00:00 from 16:30:00.

Just doesn’t make sense.


#6

Again my apologies. All has been sorted as I uncovered a spelling mistake in one of my variables.

Thanks for your help!

Adam


#7

Glad you solved it… Usually the missing hour is due to the fact that we use a 12 hour clock.
So, that 13th hour sometimes causes issues if the software is not thought out correctly.
(Try starting at 11 AM and ending the the at 10 AM the next morning… LOL)

Glad you got it figured out!

PS: New members and guest’s can not edit posts, keeps some spammers out of our hair!