Topic: My approach to this problem is probably completely wrong. (PHP, MySQL) (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=30942" title="Pages that link to Topic: My approach to this problem is probably completely wrong. (PHP, MySQL) (Page 1 of 1)" rel="nofollow" >Topic: My approach to this problem is probably completely wrong. (PHP, MySQL) <span class="small">(Page 1 of 1)</span>\

 
DavidJCobb
Nervous Wreck (II) Inmate

From:
Insane since: Mar 2009

IP logged posted posted 04-04-2009 02:26 Edit Quote

I'm working on revamping the table-based, barely-PHP-using, database-less monstrosity that is my site1. The new layout (DIV-based, CSS sprites, rounded corners, degradable JavaScript) is finished, but I need to work on a database.

I already have a database set up, but I'm not sure if it's set up the best way.

'K, here's what I'm trying to do. This is a video game site (primarily devoted to video game glitches). People are supposed to be able to look up a game (by name or console or both), and then they can view the glitches, gameplay videos, guides, etc., for that game (with each category of information getting its own page).

Now, glitches can have videos associated with them (if you're on a game's Glitches page, and there's a video of a glitch, that video is linked to (and some information about it is displayed) with the glitch's entry on the Glitches page).

So here's what I've set up. Considering I have limited knowledge of PHP and MySQL, and barely any knowledge of how to make the two work together, the following setup (and PHP code) will probably make you cringe.

I have an InnoDB table games to hold each game's primary key, name, and console.
I have an InnoDB table glitches to hold each glitch's data.
I have an InnoDB table videos to hold each video's data.
I have a (relation?) table links--games-glitches that associates games and glitches by their primary keys.
I have a (relation?) table links--glitches-videos that associates glitches and videos by their primary keys.

On the PHP end, the code to render the glitches page for a given game is as follows (some comments added):

code:
<?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.

?>



So, assuming that no errors or oddities occur, this code would have to loop through five different tables to display all of one game's glitches. This includes several loops to collect the data, and if this code was finished it would include more (and nested) loops to display it.

I seriously think there's a better way to do this.

Help plz?

----------------------

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

IP logged posted posted 04-04-2009 19:46 Edit Quote

your table setup is correct.
read up on sql joins, see if that helps you, if not, come in again and I'll read your php
in more detail.

DavidJCobb
Nervous Wreck (II) Inmate

From:
Insane since: Mar 2009

IP logged posted posted 04-05-2009 03:39 Edit Quote

Thanks, I found that really helpful!

----------------------



Post Reply
 
Your User Name:
Your Password:
Login Options: Remember Me On This Computer
 
Your Text:
Loading...
Options: Show Signature
Enable Slimies
Enable Linkwords

« BackwardsOnwards »

Show Forum Drop Down Menu