OZONE Asylum
Forums
Server-Side Scripting - Oh my!
My approach to this problem is probably completely wrong. (PHP, MySQL)
This page's ID:
30942
Search
QuickChanges
Forums
FAQ
Archives
Register
Edit Post
Who can edit a post?
The poster and administrators may edit a post. The poster can only edit it for a short while after the initial post.
Your User Name:
Your Password:
Login Options:
Remember Me On This Computer
Your Text:
Insert Slimies »
Insert UBB Code »
Close
Last Tag
|
All Tags
UBB Help
I'm working on revamping the table-based, barely-PHP-using, database-less monstrosity that is [url=http://david.pyroboyproductions.com/]my site[/url][sup]1[/sup]. 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 [b]games[/b] to hold each game's primary key, name, and console. I have an InnoDB table [b]glitches[/b] to hold each glitch's data. I have an InnoDB table [b]videos[/b] to hold each video's data. I have a (relation?) table [b]links--games-glitches[/b] that associates games and glitches by their primary keys. I have a (relation?) table [b]links--glitches-videos[/b] 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. ?>[/code] 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? ---------------------- [url=http://www.ozoneasylum.com/30926][img]http://img19.imageshack.us/img19/268/cellu.gif[/img][/url][url=http://davidjcobb.deviantart.com/][img]http://img262.imageshack.us/img262/428/24464470.gif[/img][/url][url=http://getfirefox.com/][img]http://img515.imageshack.us/img515/6336/25338231.gif[/img][/url][img]http://img4.imageshack.us/img4/2163/end.gif[/img]
Loading...
Options:
Enable Slimies
Enable Linkwords
« Backwards
—
Onwards »
Maximum Security
OZONE
DHTML/Javascript
Server-Side Scripting - Oh my!
CSS - DOM - XHTML - XML - XSL - XSLT
Stupid Basic HTML
Visual Therapy
Photoshop
Photoshop Pong, Anyone?
***WARNING*** BIG SIG APPROACHING
Photography
3D Modelling & Rendering
Multimedia/Animation
Print Graphics
Holding Pens
Philosophy and other Silliness
Outpatient Counseling
Site reviews!
Mad Scientists' Laboratory
Getting to know the Grail