Showing joined mysql data in a structured way without performance loss

Question

In the db, there are projects. Each projects has files. Each file has multiple versions.

So theoretically, I could do this (pseudo);

$res = mysql_query("SELECT * FROM projects");
while($row=mysql_fetch_assoc($res))
{
    echo "Project ".$row["id"]."<br/>";
    $res2 = mysql_query("SELECT * FROM projectfiles");
    while($row2=mysql_fetch_assoc($res2))
    {
        echo "FileID ".$row2["id"]."<br/>";
        $res3 = mysql_query("SELECT * FROM fileversions");
        while($row3=mysql_fetch_assoc($res3))
        {
            echo $row3["name"]."<br/>";
        }
        echo "<br/>";
    }
}

Sample output:

Project 1
    FileID 1
        test_file_1.txt.1

    FileID 2
        test_file_2.txt.1

    FileID 3
        test_file_3.txt.1
        test_file_3.txt.2

But this would mean load and loads of mysql queries where only one would be needed.

So I join the queries:

$sql = "SELECT projectfiles.*, fileversions.*, 
    projects.id as projects_id
   FROM projects";

$sql .= " LEFT JOIN".
    " (SELECT 
        projectfiles.id as projectfiles_id,
        projectfiles.fileID as projectfiles_fileID,
        projectfiles.projectID as projectfiles_projectID
       FROM projectfiles
       ) AS projectfiles".
    " ON projects.id = projectfiles_projectID";


$sql .= " LEFT JOIN".
    " (SELECT 
        fileversions.id as fileversions_id,
        fileversions.name as fileversions_name,
        fileversions.location as fileversions_location,
        fileversions.fileID as fileversions_fileID
       FROM fileversions
       ) AS fileversions".
    " ON projectfiles.projectfiles_fileID = fileversions_fileID";

But this now, of course, leaves me with unstructured data:

So what I did was:

while($row = mysql_fetch_assoc($res))
{
    $projectID = $row["projects_id"];
    $fileID = $row["projectfiles_fileID"];
    $fileversionID = $row["fileversions_id"];

    $fileversionsArray[$fileversionID] = array($row["fileversions_name"],$row["fileversions_location"]);
    $fileArray[$fileID][$fileversionID] = $fileversionID;
    $projectArray[$projectID][$fileID] = $fileID;
}

So I can show it like:

foreach($projectArray as $projectID => $projectDatas)
{
    echo "Project ID: ".$projectID."\n";
    foreach($projectDatas as $fileID)
    {
        echo "\tFile ID: ".$fileID."\n";
        foreach($fileArray[$fileID] as $fileversionID)
        {
            echo "\t\tFile version name: ";
            echo $fileversionsArray[$fileversionID][0];
            echo "\n";
            echo "\t\tFile location: ";
            echo $fileversionsArray[$fileversionID][2];
            echo "\n";
        }
    }
}

Which gives the output:

But I'm not so sure if I'm even gaining any performance doing this, because there's alot of duplicate data in the joined rows, and it sure is alot of work to update the code once/if stuff in the db changes.

I guess in short; this just feels like a dirty workaround to a proper solution which I believe is out there.

Is there a better solution to this?

Answer 1

There is no way to return structured data from MySQL database directly. Your effort to convert these table oriented data to arrays is correct.

Take a look at PHP dibi library, the ->fetchAssoc() method (doc) which does everything you need in nice and short syntax.