Elizabeth Elis Elizabeth Elis - 5 months ago 15
MySQL Question

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

This is my first time working with either PHP or SQL.

I'm making a simple website which hosts apps alongside their names, authors, version, descriptions etc. The PHP script fetches this information from a MySQL database I've made. Each app has a unique identifier assigned to it. Multiple versions would re-use the same identifier.

I've got it to display the entries with only one version fine.


When an updated version of an app is released, I want it to list the old version(s) (just the version number linked to the old version's link) in a drop down next to the newest version. I made the database assuming that I'd figure out a way to display it fine, thus when I added an updated version of an app to the database, I left the things that didn't change blank (like name, author, description), gave it the same identifier as the older version and only added the newer version number and the filename of the newer version. See here for an example.

However, I assumed wrong.

I have no clue how to proceed. I have a query:

SELECT * FROM apps WHERE identifier IN ( SELECT identifier FROM apps GROUP BY identifier HAVING count(*) > 1)

However, this only selects all of the entries which have duplicate "identifier"s. I don't know how to run this through a loop, to echo the older entry's description/name but the newer entry's version number/link (while ALSO being able to echo the old version number/link for my dropdown). Oh, and also so it wouldn't assign names/descriptions/etc to apps which DO have a duplicate identifier, but which isn't identical to other apps' identifiers. Sorry, this is hard to explain.

My current loop, which is:

$i=0;while ($i < $num) {while ($row = mysqli_fetch_array($result)) {
// do stuff (echo)
} ;$i++;};

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

How should I proceed?


How about something like this:

You select all the data from your table, ordered by identifier and then by version. The query being something like:

--                              ↓ orders data by identifier, thereby 'grouping' together records with the same identifier
SELECT * FROM apps ORDER BY identifier, version DESC
--                                           ↑ orders each 'group' by version number descending - highest / 'newest' first

Then, from this data, you could create an array structure in PHP which would enable you to easily loop through the data. Something like the following:

$result = mysqli_query($connection,$query);
$apps = array(); // Create apps array

while ($row = mysqli_fetch_array($result))  {
    // Assign the Name, Description, Author of the row
    // These will be blank until the last (oldest version) which has these values
    // $app[$row[1]] will create a key for each identifier or use the existing one
    $apps[$row[1]]["name"] = $row[2];
    $apps[$row[1]]["description"] = $row[3];
    $apps[$row[1]]["author"] = $row[4];

    // If there is no current version stored
        $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, this means this row is an older version 
        $v = array(
            "version" => $row[5],
            "source" => $row[6],
            "filename" => $row[7],
        $apps[$row[1]]["older_versions"][] = $v; // Add the array to the older_versions array

This then produces the following array structure:

    [vitashell] => Array
            [name] => VitaShell
            [description] => VitaShell is an 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 could then use a couple of foreach loops to go through the $apps array printing out the data as necessary.

I made a simple example of printing out the data. For display purposes, I added some dummy rows to the dataset:

<?php foreach($apps as $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"] as $version){ ?>
            <option value="<?php echo $version["source"] ?>"><?php echo $version["version"]; ?></option>
            <?php } ?>
        <?php } ?>
<?php } ?>

Which creates:

enter image description here

Note: "TIMSTUFF" only has 1 version, hence no drop-down for older versions