database-oriented web pages

I would like to be able to retrieve content of my web pages from data stored in a MySQL database(in either a unix or windows environment). I have got access to IIS, Apache, PHP, Perl, and Javascript.

First of all I would like to know how I set up the environment on either a unix or windows platform.

How I connect, retrieve data from database and format data for browsers(how I build HTML pages at runtime) to understand using php.

Ideas on other scripting languages which can be useful.
bravismoreAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
All for 50 points! WOW! There are entire books and websites on this.

But I suppose the easiest way to get this all set up on a windows pc is to have a read of ...

http://www.wowwebdesigns.com/power_guides/killer_trio_intro.php

This should get you up and running with the apps.

As for the actual page creation, I've seen to ways.

1 - embedded php/html
2 - templates

I think the templates method is a little trickier but I think is a far better solution as this (in theory) seperates your PHP code from the specifics of the layout. You can manipulate the templates without having to alter your code. phpBB is a good example of the use of templates.

If you want to keep things a little simpler, then embedded php/html is OK.


Below is a part of one of my earlier PHP programs. Its only real function is to parse a firewall log file and put the results into an Access DB via ODBC. Not the greatest of the fastest but it works.

<?php
// Allow this script to run forever.
set_time_limit(0);
error_reporting(E_ALL);
$PHP_SELF = $_SERVER['PHP_SELF'];

function getmicrotime()
{
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}

function Main_Import($Log_Filename)
{
global $db,$Last_Line_Imported;
$mStartTime = 0;
$mStopTime = 0;
$iCounter = 0;
$sTestLine = "";

$strippedLog_Filename = stripslashes($Log_Filename);
echo<<<END
      <tr><td class="inner">About to import '$strippedLog_Filename'.</td></tr>

END;
if (($fp = fopen($Log_Filename,"r")) === false)
      {
      die("<tr><td class=\"inner\">Cannot open the log file '$strippedLog_Filename'.</td></tr>");
      }
$mStartTime = getmicrotime();
if (strlen($Last_Line_Imported) > 0)
      {
      while($sTestLine != $Last_Line_Imported)
            {
            $sTestLine = str_replace("]",")",str_replace("[","(",trim(fgets($fp,2048))));
            }
      }
while(!feof($fp))
      {
      $sLine = trim(fgets($fp,2048));
      if (strlen($sLine) > 5)
            {
            $iCounter++;
//      0 = Entire Line
//      1 = Bitmap
//      2 = Date
//            0 = Date
//            1 = Day
//            2 = Month
//            3 = Year
//      3 = Time
//      4 = Rule
//      5 = State
//      6 = Direction
//      7 = Protocol
//      8 = Start Point
//            0 = Start Point
//            1 = Name
//            2 = Address
//            3 = Port
//      9 = End Point
//            0 = End Point
//            1 = Name
//            2 = Address
//            3 = Port
//      10 = Owner
            preg_match("_(\d{1}),\[(\d{2}/[A-Z][a-z]{2}/\d{4}) (\d{2}:\d{2}:\d{2})\] Rule '(.*?)': (Permitted|Blocked): (In|Out) (TCP|UDP|ICMP \[\d{1,5}\] .*?|protocol \[\d{1,5}\]), (.*)->(.*), Owner: (.*)_",$sLine,$aLine);
            preg_match("_(\d{2})/([A-Z][a-z]{2})/(\d{4})_",$aLine[2],$aDate);
            $aLine[2] = "$aDate[1] $aDate[2] $aDate[3]";
            if (!preg_match("_(.* )\[(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}):(\d{1,5})\]_",$aLine[8],$aStartPoint)) //Name,address,port
            if (!preg_match("_(.* )\[(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\]()_",$aLine[8],$aStartPoint)) //Name,address
            if (!preg_match("_()(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}):(\d{1,5})_",$aLine[8],$aStartPoint)) //Address,port
            if (!preg_match("_(.*)():(\d{1,5})_",$aLine[8],$aStartPoint)) //Name,port
            if (!preg_match("_()(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})()_",$aLine[8],$aStartPoint)) //Address
            if (!preg_match("_(.*)()()_",$aLine[8],$aStartPoint)) //Name
                  {
                  $aStartPoint[0] = $aLine[8];
                  $aStartPoint[1] = "";
                  $aStartPoint[2] = "";
                  $aStartPoint[3] = 0;
                  }
            if (!preg_match("_(.* )\[(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}):(\d{1,5})\]_",$aLine[9],$aEndPoint)) //Name,address,port
            if (!preg_match("_(.* )\[(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\]()_",$aLine[9],$aEndPoint)) //Name,address
            if (!preg_match("_()(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}):(\d{1,5})_",$aLine[9],$aEndPoint)) //Address,port
            if (!preg_match("_(.*)():(\d{1,5})_",$aLine[9],$aEndPoint)) //Name,port
            if (!preg_match("_()(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})()_",$aLine[9],$aEndPoint)) //Address
            if (!preg_match("_(.*)()()_",$aLine[9],$aEndPoint)) //Name
                  {
                  $aEndPoint[0] = $aLine[9];
                  $aEndPoint[1] = "";
                  $aEndPoint[2] = "";
                  $aEndPoint[3] = 0;
                  }
// Insert into the table if required
            $sInsert = "INSERT INTO Log_Entries(Blocked,DateTimeStamp,Rule,Direction,Protocol,Start_Point_Name,Start_Point_Address,Start_Point_Port,End_Point_Name,End_Point_Address,End_Point_Port,Owner) VALUES(";
            $sInsert .= (($aLine[5]=="Permitted")?"0":"1"); // Blocked
            $sInsert .= ",#" . date("m/d/Y H:i:s",strtotime($aLine[2] . " " . $aLine[3])) . "#"; // DateTime
            $sInsert .= ",'$aLine[4]'"; // Rule,
            $sInsert .= "," . (($aLine[6]=="Out")?"0":"1"); // Directorn
            $sInsert .= ",'" . str_replace("]",")",str_replace("[","(",$aLine[7])) . "'"; // Protocol
            $sInsert .= ",'$aStartPoint[1]','$aStartPoint[2]'," . (($aStartPoint[3] == 0)?"0":$aStartPoint[3]); // Start Point
            $sInsert .= ",'$aEndPoint[1]','$aEndPoint[2]'," . (($aEndPoint[3] == 0)?"0":$aEndPoint[3]); // End Point
            $sInsert .= ",'$aLine[10]'"; // Owner
            $sInsert .= ")";
            if (($result = odbc_exec($db,$sInsert)) == false)
                  {
                  echo<<<END
                  <tr><td class="inner">Error : $sInsert</td></tr>
      
END;
                  }
            $sSavedLine = str_replace("'","''",(str_replace("]",")",str_replace("[","(",$sLine))));
            }
      }
fclose($fp);
if ($iCounter > 0)
      {
      $sUpdate = "UPDATE Static_Data SET Last_Import=#" . date("m/d/Y H:i:s") . "#, Last_Line_Imported='$sSavedLine'";
      if (($result = odbc_exec($db,$sUpdate)) == false)
            {
            echo<<<END
            <tr><td class="inner">Error : $sUpdate</td></tr>
      
END;
            }
      }
$mStopTime = getmicrotime();
$mDuration = round($mStopTime - $mStartTime,2);
$sPer = round($iCounter / $mDuration);
echo<<<END
      <tr><td class="inner">$iCounter records read in $mDuration seconds at $sPer records per second.</td></tr>
      
END;
return (true);
}

?>
<html>
<head>
<title>Personal Firewall Logfile Analyzer</title>
<link rel="stylesheet" type="text/css" href="./styles.css" title="styles">
</head>
<body>
<table class="outer" align="center"><tr><td class="outer">
      <table class="center"><tr><td class="center">
            <table class="inner"><tr><td class="inner" colspan="2"><a href="<?php echo $PHP_SELF; ?>"><h2>Personal Firewall Logfile Analyzer</h2></a></td></tr>
<?php
// Connect to the database via ODBC.
$db = @odbc_connect("PFL","","");
if ($db === false)
      {
      die("<tr><td class=\"inner\">Cannot connect to the database.</td></tr>");
      }

// Get the static data.
$sStaticSQL = "SELECT * FROM Static_Data WHERE Unique_ID=1";
$static = @odbc_exec($db,$sStaticSQL);
if ($static === false)
      {
      die("<tr><td class=\"inner\">Cannot retrieve standing data.</td></tr>");
      }

$bHasStatic = (strlen(odbc_result($static,2)) > 0);

if ($bHasStatic == true)
      {
      $Log_Filename = odbc_result($static,2);
      $strippedLog_Filename = stripslashes($Log_Filename);
      $Last_Import = odbc_result($static,3);
      $Last_Line_Imported = odbc_result($static,4);
      
// Has an action been requested?      
      if (isset($_GET["Action"]))
            {
            switch($_GET["Action"])
                  {
                  case 1: // Import
                        Main_Import($Log_Filename);
                        break;
                  case 2: // Delete
                        break;
                  case 3: // Reset
                        break;
                  case 4: // Tail
                        break;
                  case 5: // 1 minute
                        break;
                  case 6: // 1 hour
                        break;
                  case 7: // 1 day
                        break;
                  case 8: // 1 week
                        break;
                  case 9: // 1 month
                        break;
                  case 10: // 1 year
                        break;
                  case 11: // custom
                        break;
                  }
            }
      else
            {
            echo<<<END
                  <tr><td class="inner">1</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=1">Import firewall logfile</a></td></tr>
                  <tr><td class="inner">2</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=2">Delete entries from database</a></td></tr>
                  <tr><td class="inner">3</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=3">Reset database</a></td></tr>
                  <tr><td class="inner">4</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=4">View tail of firewall logfile</a></td></tr>
                  <tr><td class="inner">5</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=5">Analyse the entries from the last minute</a></td></tr>
                  <tr><td class="inner">6</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=6">Analyse the entries from the last hour</a></td></tr>
                  <tr><td class="inner">7</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=7">Analyse the entries from the last day</a></td></tr>
                  <tr><td class="inner">8</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=8">Analyse the entries from the last week</a></td></tr>
                  <tr><td class="inner">9</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=9">Analyse the entries from the last month</a></td></tr>
                  <tr><td class="inner">10</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=10">Analyse the entries from the last year</a></td></tr>
                  <tr><td class="inner">11</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=11">Analyse the entries over any time period</a></td></tr>

END;
            }
      $sDBEntryCount = "SELECT Count(*) FROM Log_Entries";
      $EntryCount = @odbc_exec($db,$sDBEntryCount);
      if ($EntryCount === false)
            {
            die("<tr><td class=\"inner\">Cannot retrieve number of log entries.</td></tr>");
            }
      else
            {
            $iEntryCount = odbc_result($EntryCount,1);
            if ($Last_Import != NULL)
                  {
                  $sLastImported = date('l, jS F Y g:i A',strtotime($Last_Import));
                  }
            else
                  {
                  $sLastImported = "Never";
                  }
            echo<<<END
                  <tr><td class="inner" colspan="2">Number of log entries : $iEntryCount</td></tr>
                  <tr><td class="inner" colspan="2">Log filename : $strippedLog_Filename</td></tr>
                  <tr><td class="inner" colspan="2">Last imported : $sLastImported</td></tr>

END;
            }

$bHasStatic = (($aStaticRow = odbc_fetch_row($static)) === true);
      }
else
      {
// Has an action been requested?      
      if (isset($_GET['Action']))
            {
            switch($_GET['Action'])
                  {
                  case 1: // Ask for filename
                        echo<<<END
                              <tr><td class="inner">Set Personal Firewall Log Filename<br><form name="SetLog" method="get" action="$PHP_SELF"><input type="hidden" name="Action" value="2"><input type="text" name="Log_Filename" maxlength="200" size="40">&nbsp;<input type="submit" name="Submit" value="Submit"></form></td></tr>

END;
                        break;
                  case 2: // Check the filename.
                        $strippedLog_Filename = stripslashes($Log_Filename);
                        if (file_exists($Log_Filename) === true)
                              {
                              echo<<<END
                                    <tr><td class="inner">'$strippedLog_Filename' located.</td></tr>

END;
                              $sUpdateDB = "INSERT INTO Static_Data (Log_Filename) VALUES ('$Log_Filename')";
                              $sUpdatedDB = odbc_exec($db,$sUpdateDB);
                              }
                        else
                              {
                              echo<<<END
                                    <tr><td class="inner">Cannot locate '$strippedLog_Filename'.</td></tr>

END;
                              }
                        break;
                  }
            }
      else
            {
            echo<<<END
                  <tr><td class="inner">1</td><td class="inner"><a class="innerlink" href="$PHP_SELF?Action=1">Set Personal Firewall Log Filename</a></td></tr>

END;
            }
      }
echo<<<END
                  <tr><td class="inner" colspan="2"><a class="innerlink" href="$PHP_SELF">Menu</a></td></tr>

END;
?>                  
</table>
</td></tr></table>
</td></tr></table>
</body></html>

This shows the use of <<< too.

Regards,

Richard Quadling.
0
 
bravismoreAuthor Commented:
Thank you

Will try it out in a moment.
0
 
BatalfCommented:
Here's a tutorial which includes how to set up php, mysql and a tutorial how to program in php and mysql:

http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html
0
 
Richard QuadlingSenior Software DeveloperCommented:
How are you doing? Anything more to say?

0
 
shmertCommented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept: rquadling
Please leave any comments here within the next seven days.
               
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
               
Sam Barnum
EE Cleanup Volunteer              
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.