PHP better coding?

[Oroton]

This is a code i have to get data from the database and turn it into javascript basically..

what it does is it gets a field from the database. relating to columns. It creates an array to get all the column data for that map.

Then for each column, it does another query to get all the info for each row in that column. Some row's have particular functions or multiple functions so it also queries whether that particular one has one or not.

Now this code works and produces the desired effect. but is there a better way to do it?
I feel doing one query first then doing a while loop and then a for loop inside might be better than doing a query then query the same table again. (i also know that SELECT * is slower but i'll optimise that later.)
Anyway here is the code

	$_chip = chippy;                                                                                  //db unique modifyer for table

	                                                                                                  //End Global Variables

                                                                                                  //Begin Table Names
define('SCENEDATA', $_chip."_scenedata");                                                         //Begin Table Names
define('SCENEFUNCTION', $_chip."_scenefunction");                                                 //scenedata table name
                                                                                                  //End Table Name

$sceneid = '1';

   $sceneinfo="SELECT *
                 FROM " . SCENEDATA . "
                 WHERE scene_value ='$sceneid'
                 GROUP BY scene_column
                 ORDER BY scene_column ASC ";
                 $sceneinfo2=mysql_query($sceneinfo) or die("could not get map data!");   //could you get data?
                 $sceneNumCols = mysql_num_rows($sceneinfo2);
                 $scenecol_list = "";
                 $colcount = 1;
                 $colcount_num = 0;
                 while ($sceneinfo3=mysql_fetch_array($sceneinfo2))
                       {

                       $scenecol_num = $sceneinfo3['scene_column'];
                       if ($colcount_num == $scenecol_num)
                       {
                              if ($colcount == $sceneNumCols)
                              {
                              $colcomma = "<br>";                 // last row
                              } else
                              {
                              $colcount++;
                              $colcomma = ",<br>";                // not last row
                              }

 

                       $scenedatainfo="SELECT d.*, f.*
                       FROM " . SCENEDATA . " d
                       LEFT JOIN " . SCENEFUNCTION . " f ON (d.scene_id = f.scenedata_id)
                       WHERE d.scene_value ='$sceneid'
                       AND   d.scene_column = '$scenecol_num'
                       ORDER BY d.scene_column ASC, d.scene_row ASC";
                       $scenedatainfo2=mysql_query($scenedatainfo) or die(mysql_error());   //could you get data?
                       $sceneNumRows = mysql_num_rows($scenedatainfo2);                                 //scene_value    The Scene that it is
                       $scenerow_list = "";
                       $rowcount = 1;
                       $rowcount_num = 0;
                       $scene_function = "";

                       //Begining of the Row lists.
                       while ($scenedatainfo3=mysql_fetch_array($scenedatainfo2))
                             {//1
                             $scene_idv = $scenedatainfo3['scene_id'];
                             $scene_row = $scenedatainfo3['scene_row'];
                             $scene_name = $scenedatainfo3['scene_name'];
                             $scene_fid = $scenedatainfo3['scene_function'];
                             $sceneunique_id = $scenedatainfo3['scene_id'];
                             $scenedatafunction_id = $scenedatainfo3['scenedata_id'];
                                     if ($scenedatafunction_id == $sceneunique_id)
                                     {
                                      $scene_function = ",<br>". $scene_fid;
                                     }
                                     elseif ($scenedatafunction_id != $sceneunique_id)
                                     {
                                        $scene_function = "";
                                     }
                                      
                                      if ($rowcount == $sceneNumRows)
                                      {
                                      $rowcomma = "<br>";                 // last row
                                      }
                                      else
                                      {
                                      $rowcomma = ",";                // not last row
                                      $rowcount++;
                                      }

                                 if (empty($scene_name))
                                 {
                                   $scenemap_name = "' ' ";
                                   $scene_new_map = "";
                                   $scene_end_map = "";
                                 }
                                 else
                                 {//2
                                  
                                 $scenemap_name = $scene_name;
                                      
                                     $scene_new_map =  " <b>new map</b> (";
                                     $scene_end_map = "<br>)";
                                }//2 //else scene name

                             $scenerow_list.= $scene_new_map . $scenemap_name.$scene_function .$scene_end_map. $rowcomma ."<br>";
                            }//end while row

 

                       // This is the return of the each Column.

                       $scenecol_list.= " [<i>//column " . $scenecol_num. "</i>  <BR>" .$scenerow_list. " ]". $colcomma;
                       $colcount_num++;
                       }//end if first col
                       else
                       {
                       }

                  } //end colum WHILE

  print   "<html><br><b> var maps</b> = [<br>" . $scenecol_list. "<br>];<br><br>".$sceneNumCols ."/" .$colcount."</html>";

This is the result.

var maps = [
[//column 0
new map (map0x0,
function() { if (game.plotSequence == 0) { eng.setMessage('Arrow keys to move, Spacebar to interact and close messages.'); game.plotSequence ++; } this.precip('rain', 35); }
),
new map (map0x1,
function() { this.precip('rain', 35); }
),
new map (map0x2,
function() { if (game.plotSequence == 1) { eng.setMessage('AHHHHH!!! What is that thing!? Help me!!!'); game.plotSequence ++; } this.precip('rain', 35); }
),
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
new map (map0x12
)

],
[//column 1
' ' ,
new map (map1x1
),
new map (map1x2
),
new map (map1x3
)

]

];

2/2

[saricden]

If you're going for better in terms of readability + scalability I think it'd really be worth using MVC structure (though in this case the V would kinda just be JSON output), and creating an API that utilizes a framework to help keep that structure together.

A PHP framework I would suggest is Laravel. I find version 4 is a lot easier to pick up than version 5, but version 5 is probably enforcing some new even better advancements in software architecture / design that I totally don't understand yet.

If you choose to go this route you might also consider using Lumen first which is a micro-framework by the people who made Laravel. The big advantage here is it has a lot of solid functionality you can use for your back-end, however if you find yourself wishing you'd gone with a full-fledged MVC framework you can move your Lumen code into a new Laravel project and it's supposed to just work.

If you do use any of this stuff post about how it worked out, I really hope Lumen is as awesome as it sounds.

Sometimes "it just works" isn't as true as we all hope...

 

[saricden]

Also depending on what you're doing on the front-end it might be worth looking at Firebase. It's mindblowingly simple to setup and has the advantage that it's completely modular from your front-end. Plus you don't have to host any serverside code, and just need to find static hosting for your JS (which you can do for free with GitHub and I THINK Bitbucket as well).