G4ZFE MySQL Log Search - Creating Bar Charts

Creating Bar Charts

In addition to be able to search the MySQL database it is also possible to display bar charts using PHP and the GD Library. The GD Library is able to create PNG graphics and dynamically create graphics objects. To check whether GD has been installed on your web server check the output of phpinfo() and you should see a --with--gd at the top under "Configure Command". If GD has not been installed then please refer to your ISP.

The following PHP script uses GD to create a Bar Chart displaying the percentage of QSOs that 9M2/G4ZFE has made per mode (CW, SSB and Digital).

An example bar chart is displayed here in PNG format. The 9m2qsochart.php script can be found on the Log Search PHP scripts download page.

The following variables need to be changed in the include file dbinc.php used by the below script:

<?php
	include 'dbinc.php';	// Variables for DB connection
	include 'error.inc';	// Error handler
  
	// Function to display a bar chart from O'Reilly 'PHP Cookbook'
	function pc_bar_chart ($question, $answers)
	{
	
		// define colours to draw the bars
		$colours = array (array(255,102,0), array(0,153,0),
				array(51,51,204), array(255,0,51),
				array(255,255,0), array(102,255,255),
				array(153,0,204));

		$total = array_sum($answers['qsos']);

		// define some spacing
		$padding = 15;
		$line_width = 80;
		$scale = $line_width * 7.5;
		$bar_height = 10;

		$x = $y = $padding;

		// Allocate a large palette for drawing
		$image = ImageCreate (550, 800);
		$bg_colour = ImageColorAllocate($image, 224, 224, 224);
		$black = ImageColorAllocate($image, 0, 0, 0);

		// print the query
		$wrapped = explode ("\n", wordwrap($question, $line_width));
		foreach ($wrapped as $line)
		{
			ImageString($image,3,$x,$y,$line,$black);
			$y += 10;
		}

		$y += $padding;

		// print the results
		for ($i = 0; $i < count ($answers['query']); $i++)
		{

			// format percentages
			$percent = sprintf ('%1.1f', 100 * $answers ['qsos'][$i]/$total);
			$bar = sprintf ('%d', $scale * $answers ['qsos'][$i]/$total);

			// grab colour
			$c = $i % count($colours);
			$text_colour = ImageColorAllocate($image, $colours[$c][0],
							$colours[$c][1], $colours[$c][2]);

			// draw bar and percentage numbers
			ImageFilledrectangle ($image, $x, $y, $x + $bar,
						$y + $bar_height, $text_colour);

			ImageString ($image, 3, $x + $bar + $padding, $y, "$percent%", $black);

			$y += 10;

			// print query
			$wrapped = explode ("\n", wordwrap ($answers['query'][$i], $line_width));
			foreach ($wrapped as $line)
			{
				ImageString ($image, 2, $x, $y, $line, $black);
				$y += 12;
			}

			$y += 25;
		}

		// crop image by copying it
		$chart = ImageCreate (550, $y);
		ImageCopy ($chart, $image, 0, 0, 0, 0, 550, $y);

		//deliver image
		header ('Content-type: image/png');
		ImagePng($chart);

		//clean up
		ImageDestroy ($image);
		ImageDestroy ($chart);
	}

	// Start of main program
	$qso_count = 0;
	$cw_count = 0;
	$dig_count = 0;
	$ssb_count = 0;

	// Connect to MySQL
	if (! ($connection = @ mysql_connect($hostName,$username,$password)))
		die ("Could not connect to the database");

	// Connect to the log database.
	if (!mysql_select_db ($databaseName, $connection))
		showerror();

	// Query the total number of QSOs made by 9M2/G4ZFE (logbooks 2,3,4,5 and 10)
	if (! ($result = mysql_query (" SELECT count(*) 
					FROM qsos
					WHERE fk_dxstn IN (2,3,4,5,10)",
			$connection)))
		showerror();

	$row = @ mysql_fetch_array ($result);

        $qso_count = $row['count(*)'];
	
	// Extract the number of QSOs made per mode
	if (! ($result = mysql_query (" SELECT count(*), op_mode 
					FROM qsos 
					WHERE fk_dxstn IN (2,3,4,5,10) 
					GROUP BY op_mode",
			$connection)))
		showerror();

	// Fetch each row. It is returned in the following order - CW, DIG, SSB
	while ($row = @ mysql_fetch_array ($result))
	{
		$count = $row['count(*)'];
		$mode = $row['op_mode'];

		switch ($mode)
		{
			case "CW":
			$cw_count = $count;
			break;

			case "DIG":
			$dig_count = $count;
			break;

			case "SSB":
			$ssb_count = $count;
			break;
		}			
	}

	// Display text
	$question = 'QSOs per mode for 9M2/G4ZFE. Total number of QSOs = ' . $qso_count;

	// Bar Chart for CW QSOs
	$answers['query'][] = $cw_count . ' CW QSOs';
	$answers['qsos'][] = $cw_count;

	// Bar Chart for Digital QSOs
	$answers['query'][] = $dig_count . ' Digital QSOs';
	$answers['qsos'][] = $dig_count;

	// Bar Chart for SSB QSOs
	$answers['query'][] = $ssb_count . ' SSB QSOs';
	$answers['qsos'][] = $ssb_count;

	// Display the Bar Chart
	pc_bar_chart ($question, $answers);

	// Close the database connection
	if (!mysql_close ($connection))
		showerror();  
?>
Creating Bar Charts are of course optional but show the flexibility of MySQL and PHP. You will also need to change the SQL statements in the above example PHP script as needed for your own requirements.

> Next: Download scripts