Topic: My approach to this problem is probably completely wrong. (PHP, MySQL) (Page 1 of 1)

<?php
include("/php_includes/mysql.php") or echo("<!-- Noes! Unable to set up a connection to the database! -->\n");
// generic code to open a connection to the database

// url is of the format "glitches.php?game=The+Game+Name&console=Console+Name&id=primarykey"
// game or id must be included

// grab the vars we need
$gameURLName = mysql_real_escape_string($_GET['game']); // problems if not defined? does " a = VAR || DEFAULTVALUE " work in PHP?
$gameURLConsole = mysql_real_escape_string($_GET['console']);
$gameDisplayName = ''; // name, with proper capitalization, symbols
$gameDisplayConsole = $gameURLConsole;
$gamePrimaryKey = mysql_real_escape_string($_GET['id']);
$gameQueryURL = implode('&',array(($gameURLName?'game='.$gameURLName:''),($gameURLConsole?'console='.$gameURLConsole:''),($gamePrimaryKey?'id='.$gamePrimaryKey:'')));
// $gameQueryURL is used for part of the layout -- not important

$glitches = array();

$Err = 0; // set to 1 if any fatal errors occur (no content could be loaded)
$ErrInline = 0; // set to 1 if any non-fatal errors occur (page'll still load with whatever data we got, but user is notified)
$Err_GlitchRetrievalInterrupted = 0; // so we know what error message to show to the user
$Err_MultipleGamesFound = 0; // why do I refer to myself as "we" in my code?
$Err_MultipleGamesData = 0;
$Err_NoGamesFound = 0;
$Err_NoGameSpecified = 0;
$Err_QueryFailed = 0;

if($gameName||$gameConsole||$gameId) {

   //if(!$gamePrimaryKey) { // we need to grab the name and console anyway, see below
      $query="SELECT * FROM games WHERE ";
      if($gameURLName) { $query.="Name='{$gameURLName}'"; }
      if($gameURLConsole) { $query.="Console='{$gameURLConsole}'"; }
      if(!$DJCSCv3_MySQL) { return false; }
      $result=mysql_query($query) or die('An error has occured.');
      if(mysql_num_rows($result)>1) {
         // multiple games; prepare to ask for clarification.
         $Err = $Err_MultipleGamesFound = 1;
         $Err_MultipleGamesData = array();
         while ($row = mysql_fetch_assoc($result)) {
            $Err_MultipleGamesData[] = array($row['PRIMARY'],$row['Name'],$row['Console']);
         }
      } else if (mysql_num_rows($result)) { // one game found
         $row = mysql_fetch_assoc($result);
         $gamePrimaryKey = $row['PRIMARY'];
         $gameDisplayName = $row['Name']; // capitalization
         $gameQueryURL = implode('&',array(($gameURLName?'game='.$gameURLName:''),($gameURLConsole?'console='.$gameURLConsole:''),($gamePrimaryKey?'id='.$gamePrimaryKey:''))); // used in a layout include -- not important.
      } else {
         $Err = $Err_NoGamesFound = 1;
      }
      mysql_free_result($result);
   //}

   if($gamePrimaryKey) {
      $result=mysql_query("SELECT 'Glitch Primary Key' FROM 'links--game-glitch' WHERE 'Game Primary Key'={$gamePrimaryKey}"); // find associated glitches
      if(!$result) {
         $Err = 1;
         $Err_QueryFailed = mysql_error();
      } else {
         $gameGlitchPrimaryKeys=array();
         while(($gameGlitchPrimaryKeys[] = mysql_fetch_assoc($result)) || array_pop($gameGlitchPrimaryKeys)); // saw this on PHP.net
         foreach($gameGlitchPrimaryKeys as $gPrimary) { // this next part is dubious... loop through glitch primary keys
            $result=mysql_query("SELECT * FROM glitches WHERE PRIMARY=$gPrimary LIMIT 1"); // grab the glitch
            if(!$result) {
               $ErrInline = $Err_GlitchRetrievalInterrupted = 1;
               break; // should probably be continue? in case only this glitch is messed up
            }
            $glitchInProgress = 0;
            if (mysql_num_rows($result)<1) {
               $glitchInProgress=array('err'=>1,'msg'=>'[error: this glitch could not be retrieved from the server]');
            } else {
               $gData=mysql_fetch_assoc($result);
               $glitchInProgress=
                  array( // just random data about the glitch
                     'err'=>0,
                     'name'=>$gData['Glitch Name'],
                     'text'=>$gData['Information'],
                     'screens'=>$gData['Screenshots'],
                     'table'=>$gData['TableData'],
                     'discovered'=>$gData['Discovered By'],
                     'submitted'=>$gData['Submitted By'],
                     'date'=>$gData['Date Submitted'],
                     'fo\' reals?'=>$gData['Reliable']
                  );
            }
            mysql_free_result($result);
            $result=mysql_query("SELECT 'Video Primary Key' FROM 'links--glitch-video' WHERE 'Glitch Primary Key'=$gPrimary"); // here's where things get tricky
            if (!$result) {
               $glitchInProgress['video'] = 0;
               mysql_free_result($result);
               // free $result
            } else {
               // not yet written. what should be here:

               // store all the video primary keys from $result in $videoList.
               // then free $result and loop through $videoList, doing 
               // queries on the video database to get the info we need on the 
               // video.
               // I seriously doubt this is an efficient approach.
               // Once we have all the video data, store it as...
               // $glitchInProgress['video'][$i] = [associative array of video's data]

               $glitchVideoPrimaryKeys=array();
               while(($glitchVideoPrimaryKeys[] = mysql_fetch_assoc($result)) || array_pop($glitchVideoPrimaryKeys));
               mysql_free_result($result);
               foreach($glitchVideoPrimaryKeys as $vPrimary) {
                  // launch a query for each video key, get its info as desc.'d above
               }
            }
            // do this: $glitches[]=$glitchInProgress;
         }
      }
   }

} else {
   $Err = $Err_NoGameSpecified = 1;
}

if ($Err) {
   $pageHeader = 'An error has occured.';
} else {
   if($gameName) {
      $pageHeader = $gameDisplayName.' » Glitches';
   } else {
      $pageHeader = 'SystemCrash';
   }
}

include("/php_includes/top.php") or die("The site layout failed to load! D:"); // layout - top

include("/php_includes/gamemenu.php"); // layout - "Index"/"Glitches"/"Videos"/etc. menu

// begin rendering glitches

// not yet written.
// code should loop through $glitches, take the information and 
// write the mid-page layout code as appropriate, "filling in the 
// blanks" with the information.

// end rendering glitches

require_once("/php_includes/footer.php") or die("The footer failed to load! D:"); // layout - bottom
// I realize that I need to close the MySQL connection, I'm considering having footer.php 
// check if one was ever opened and if so, closing it for me -- it'd save me some typing.

?>