How to handle multiple versions of files in a file mirror with PHP / SQL?

Question

This is my first experience with PHP or SQL.

I make a simple website that hosts applications along with their names, authors, versions, descriptions, etc. The PHP script fetches this information from the MySQL database that I created. Each application has a unique identifier assigned to it. Multiple versions will use the same identifier.

I need to display entries with only one version.

However...

When the updated version of the application is released, I want it to list the old version (s) associated with the link to the old version in the drop-down list next to the newest version. I made a database, assuming that I figure out a way to display it, so when I added the updated version of the application to the database, I left things that did not change empty (for example, name, author, description), gave it the same identifier, as the older version, and added only the new version number and the name of the new version. See here for an example.

However, I was wrong.

I have no idea how to proceed. I have a request:

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

, , "". , , / , / ( / ). , //etc , , . , .

, :

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

, , - ( , ).

?

Answer 1

- :

, , . :

--                              ↓ 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

PHP, . - :

$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
    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, 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
    }
}    

:

Array
(
    [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 (...)
)

foreach $apps, .

---

. :

<?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 } ?>
        </select>
        <?php } ?>
    </div>
    <hr>
<?php } ?>

:

_{: "TIMSTUFF" ,}