G4ZFE MySQL Log Search - Searching

PHP Search script

Once the MySQL database has been created and populated the search program needs to be created. The search script is shown below and is called search.php. The script should be copied from the Log Search PHP scripts download page and transferred to your server.

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

Additionally I have created images to display the modes that each station has been worked on:

The above images should be saved (as cw.gif, ssb.gif, ssbcw.gif, cwdig.gif, cwssbdig.gif, dig.gif and ssbdig.gif respectively) and copied to your web server in the same directory as the search.php file. On web browsers with images disabled the text is displayed e.g. "CW" instead of displaying the cw.gif image. 

<html>
<head>
<title>G4ZFE Log Search Results</title>
</head>

<body>
<?php
  include 'dbinc.php';	// Variables for DB connection
  include 'error.inc';	// Error handler
	
  // This function displays the modes contacted for each band (cell) as an image
  function print_cell ($value)
  {
    switch ($value)
    {
			case '0':
			echo "\n\t<td width=\"5%\"> </td>";
			break;

			case '1':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/ssb.gif\" ALT=\"SSB\"></CENTER></td>";
			break;

			case '2':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/cw.gif\" ALT=\"CW\"^gt;</CENTER></td>";
			break;

			case '3':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/ssbcw.gif\" ALT=\"SSB CW\"></CENTER></td>";
			break;

			case '4':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/dig.gif\" ALT=\"DIG\"></CENTER></td>";
			break;

			case '5':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/ssbdig.gif\" ALT=\"SSB DIG\"></CENTER></td>";
			break;

			case '6':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/cwdig.gif\" ALT=\"CW DIG\"></CENTER></td>";
			break;

			case '7':
			echo "\n\t<td width=\"5%\"><CENTER><img src=\"images/cwssbdig.gif\" ALT=\"CW SSB DIG\"></CENTER></td>";
			break;
    }  
  }

  // This function is used to add up a whole array. It is used to check if any 
  // QSOs have been made with a particular DX station
  function add_up ($running_total, $current_value)
  {
    $running_total += $current_value;
    return $running_total;
  }

  // Start of program

  // Read the callsign to search. Check to see if we need to strip any slashes
  // from the callsign (magic_quotes_gpc option in php.ini)
  $callsign = ini_get ('magic_quotes_gpc')
        ? stripslashes ($_POST['callsign'])
        : $_POST['callsign'];
  
  // Make the callsign upper case
  $callsign = strtoupper ($callsign);

  echo "<CENTER><H1>Log Search result for $callsign</H1></CENTER>";
  echo "<P>";
  
  // Connect to the database. The variables are stored in the include file db.inc
  if (! ($connection = @ mysql_connect($hostName,$username,$password)))
    die ("Could not connect to the database");

  // Connect to the log database. The error handler is defined in the error.inc include file
  if (!mysql_select_db ($databaseName, $connection))
    showerror();

  // Query the database for all the DX callsigns available. Select in alphabetical 
  // order of the callsign for display purposes
  if (! ($result = mysql_query ("SELECT dxcallsign 
                                 FROM dxstation
                                 ORDER by dxcallsign",
                                 $connection)))
    showerror();

  // Read each row from the database and store the callsign in the dxcalls array
  while ($row = @ mysql_fetch_row($result))
    for ($i=0; $i<mysql_num_fields($result); $i++)
      $dxcalls [] = $row[$i];

  // Create an array of the bands to be displayed. This is hard coded to make 
  // my life easier. It should really be read from the database and the HTML 
  // table dynamically created. Let as a later exercise....
  $bands = array (160,80,40,30,20,17,15,12,10);

  // Initialise the 2-d array i.e. set number of QSOs on each band for each DX 
  // station to zero. This make populating the HTML table a little easier.
  for ($i=0; $i < count($dxcalls); $i++)
    for ($j=0; $j < count($bands); $j++)
      $table[$dxcalls[$i]][$bands[$j]] = 0;

  // Query the database for all the QSOs for the requested callsign
  if (! ($result = mysql_query ("SELECT DISTINCT dx.dxcallsign, 
                                 q.op_mode, 
                                 q.band
                                 FROM dxstation dx, qsos q
                                 WHERE q.callsign = '$callsign'
                                 AND q.fk_dxstn = dx.id
                                 ORDER by dx.dxcallsign,q.band DESC",
                                 $connection)))
    showerror();

  // Check the number of QSOs
  $count = mysql_num_rows ($result);

  if ($count == 0)
    echo "<P>Sorry no QSOs found for $callsign! <P>";
  else
  {

  // Table Headings - bands
  echo "\n<center><table BORDER=\"1\" CELLSPACING=\"0\" CELLPADDING=\"5\" width=\"70%\">\n<tr>\n" .
    "\n\t<th>Callsign</th>" .
    "\n\t<th>160</th>" .
    "\n\t<th>80</th>" .
    "\n\t<th>40</th>" .
    "\n\t<th>30</th>" .
    "\n\t<th>20</th>" .
    "\n\t<th>17</th>" .
    "\n\t<th>15</th>" .
    "\n\t<th>12</th>" .
    "\n\t<th>10</th>" .
    "\n</tr>" .
    "\n<p>";

  // Read each row from the database
  while ($row = @ mysql_fetch_array($result))
  {
    // Add up the number of QSOs on each band
    switch ($row["op_mode"])
    {
      case 'SSB':
        $table [$row["dxcallsign"]] [$row["band"]] += 1;
        break;

      case 'CW':
        $table [$row["dxcallsign"]] [$row["band"]] += 2;
        break;
        
      case 'DIG':
	$table [$row["dxcallsign"]] [$row["band"]] += 4;
	break;
    }
  }

  // We have now read all the QSOs made for all the DX stations into a 2D matrix ($table)
  // Now we go through each row (DX station) and column (band)

  foreach ($table as $k => $v)
  {
    // Count the number of QSOs made with this DX station
    $total = array_reduce ($v, 'add_up');

    // None? Then don't bother displaying the row
    if ($total == 0)
      continue;

    echo "\n<tr>";
    echo "\n\t<td width=\"10%\"><center>$k</center></td>";

    foreach ($v as $k2 => $v2)
    {
      switch ($k2)
      {
        // Display QSOs made on each band
        case '160':
          print_cell($v2);
          break;
        case '80':
          print_cell($v2);
          break;
        case '40':
          print_cell($v2);
          break;
        case '30':
          print_cell($v2);
          break;
        case '20':
          print_cell($v2);
          break;
        case '17':
          print_cell($v2);
          break;
        case '15':
          print_cell($v2);
          break;
        case '12':
          print_cell($v2);
          break;
        case '10':
          print_cell($v2);
          break;
      }
    }
    echo "\n</tr>";
  }

  echo "\n</table></center>";
  echo "\n\n";
  
  } // End else no QSOs found

  // Display summary info
  
  // Count the number of QSOs in the database
  if (! ($result = mysql_query ("SELECT count(*) 
                                 FROM qsos",
                                 $connection)))
    showerror();

  // Read each row from the database and store the callsign in the dxcalls array
  while ($row = @ mysql_fetch_row($result))
    for ($i=0; $i<mysql_num_fields($result); $i++)
      $total = $row[$i];
      
  switch ($count)
  {
    case 1:
      echo "\n<CENTER><P><P>Total of $count QSO with $callsign<P>";
      break;
      
    case 0:
      echo "\n<CENTER><P><P>";
      break;
      
    default:
      echo "\n<CENTER><P><P>Total of $count QSOs with $callsign<P>";
      break;
  }
  
  echo "\n<P>There are $total QSOs in the Database<P></CENTER>";
  
  // Close the database connection
  if (!mysql_close ($connection))
    showerror();
?>

<BR>
<P>
<A href="search.html">< Return to Log Search Page</A>
</P>

</body>
</html>

Calling the PHP Search script

The below HTML page is an example of how to call the above PHP search script. The example HTML search page - search.html - should be copied from the Log Search PHP scripts download page and transferred to your server. 

<HTML>
<HEAD>
<title>G4ZFE Log Search Page</title>
</HEAD>

<BODY>
<H1>G4ZFE On-line Log Search</H1>

<H2>Full Log Search</H2>
Search the logs of M2A, M6A, M7A, SV9/G4ZFE, 9M2/G4ZFE, 9M2/G4ZFE/P<BR>
and 3W2ER
<form action="search.php" method="POST">
Enter a callsign: <input type="text" name="callsign" 
<input type="submit" value="Search Log!">
</form>
</BODY>
</HTML>

Copyright

The above PHP program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

The program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

If you use the program I would appreciate a link on your page to my download page (http://www.g4zfe.com/downloads.html) so that the latest version and the source code are freely available.

> Next: Creating Bar Charts