mysql - How to handle multiple versions of files on a PHP/SQL powered file mirror? -

this first time working either php or sql.

i'm making simple website hosts apps alongside names, authors, version, descriptions etc. php script fetches information mysql database i've made. each app has unique identifier assigned it. multiple versions re-use same identifier.

i've got display entries 1 version fine.

however...

when updated version of app released, want list old version(s) (just version number linked old version's link) in drop down next newest version. made database assuming i'd figure out way display fine, when added updated version of app database, left things didn't change blank (like name, author, description), gave same identifier older version , added newer version number , filename of newer version. see here example.

however, assumed wrong.

i have no clue how proceed. have query:

select * apps identifier in ( select identifier apps group identifier having count(*) > 1) 

however, selects of entries have duplicate "identifier"s. don't know how run through loop, echo older entry's description/name newer entry's version number/link (while being able echo old version number/link dropdown). oh, , wouldn't assign names/descriptions/etc apps have duplicate identifier, isn't identical other apps' identifiers. sorry, hard explain.

my current loop, is:

$result=mysqli_query($connection,$query); $num=mysqli_num_rows($result); $i=0;while ($i < $num) {while ($row = mysqli_fetch_array($result))  {             // stuff (echo)                     } ;$i++;}; 

just echoes entries seperately, 1 being old version , 1 being new version (with no description, name or author displayed).

how should proceed?

how this:

you select data table, ordered identifier , version. query being like:

--                              ↓ orders data identifier, thereby 'grouping' records same identifier select * apps order identifier, version desc --                                           ↑ orders each 'group' version number descending - highest / 'newest' first 

then, data, create array structure in php enable loop through data. following:

$result = mysqli_query($connection,$query); $apps = array(); // create apps array  while ($row = mysqli_fetch_array($result))  {     // assign name, description, author of row     // these blank until last (oldest version) has these values     // $app[$row[1]] create key each identifier or use existing 1     $apps[$row[1]]["name"] = $row[2];     $apps[$row[1]]["description"] = $row[3];     $apps[$row[1]]["author"] = $row[4];      // if there no current version stored     if(empty($apps[$row[1]]["current_version"])){         $apps[$row[1]]["current_version"]["version"] = $row[5];         $apps[$row[1]]["current_version"]["source"] = $row[6];         $apps[$row[1]]["current_version"]["filename"] = $row[7];     } else { // if not, means row older version          $v = array(             "version" => $row[5],             "source" => $row[6],             "filename" => $row[7],         );         $apps[$row[1]]["older_versions"][] = $v; // add array older_versions array     } }     

this produces following array structure:

array (     [vitashell] => array         (             [name] => vitashell             [description] => vitashell alternative replacement...             [author] => theflow             [current_version] => array                 (                     [version] => 0.86                     [source] => http://www.example.com/link/version/0_86                     [filename] => vitashell_0_86.vpk                 )              [older_versions] => array                 (                     [0] => array                     (                         [version] => 0.8                         [source] => http://www.example.com/link/version/0_8                         [filename] => vitashell_0_8.vpk                     )             )     )      [identifier] => array (...) ) 

you use couple of foreach loops go through $apps array printing out data necessary.

---

i made simple example of printing out data. display purposes, added dummy rows dataset:

<?php foreach($apps $app){ ?>     <div class="app">         name: <?php echo $app["name"]; ?><br />         description: <?php echo $app["description"]; ?> <br />         author: <?php echo $app["author"]; ?> <br />         version: <a href="<?php echo $app["current_version"]["source"]; ?>"><?php echo $app["current_version"]["version"]; ?></a> <br />          <?php if(!empty($app["older_versions"])){ ?>         older versions:         <select name="older_versions">             <?php foreach($app["older_versions"] $version){ ?>             <option value="<?php echo $version["source"] ?>"><?php echo $version["version"]; ?></option>             <?php } ?>         </select>         <?php } ?>     </div>     <hr> <?php } ?> 

which creates:

_{note: "timstuff" has 1 version, hence no drop-down older versions}