Fetching player data

Question

I would like to select some information I have stored in my MySQL DB.

I have 2 tables named gameTable and playerTable. Now I want to get the gameTable and get the names and levels of these players.

This is what I got so far, it works but I'm not sure this is the most efficient way. Do I really need to create a join for every referred row?

<?php

include ('connect.php');


 $result = mysqli_query($connect,  
        "SELECT gameTable.id, 
            playerTable1.name AS name1, playerTable1.level AS level1, 
            playerTable2.name AS name2, playerTable2.level AS level2, 
            playerTable3.name AS name3, playerTable3.level AS level3, 
            playerTable4.name AS name4, playerTable4.level AS level4
         FROM gameTable
            JOIN playerTable AS playerTable1 ON gameTable.P1_id = playerTable1.id
            JOIN playerTable AS playerTable2 ON gameTable.P2_id = playerTable2.id
            JOIN playerTable AS playerTable3 ON gameTable.P3_id = playerTable3.id
            JOIN playerTable AS playerTable4 ON gameTable.P4_id = playerTable4.id
        ");

    while ($record = mysqli_fetch_array($result))
    { 
        echo $record['id']  . " - " 
            . $record['name1'] . " - " . $record['level1'] . " - " 
            . $record['name2'] . " - " . $record['level2'] . " - " 
            . $record['name3'] . " - " . $record['level3'] . " - " 
            . $record['name4'] . " - " . $record['level4'] . "<br>";
    }

?>

As a secondary question, my database is set to MyISAM. Would it be helpful to change it to InnoDB? Or is this only useful for update delete etc. In your database and not for fetching data?

Answer 1

Well, as far as I see it, I can't see another way of listing what you're using. But as you're using JOIN, I would make sure that your columns have the right indexes there, or it'll be very slow when your database grow up.

About INNODB: It depends on your database, and what you're doing with the data, but the good thing about INNODB is that if you have that table engine you can make use of Transactions, and will be easier to make a backup of your live databases if that is the engine selected. And also, if your engine is INNODB, you can use foreign keys to keep your data synchronized (for example, you can restrict the delete of a user if the id is in another table, or not allowing insert a player id if that id doesn't exist).

If you're not planning using transactions neither foreign keys, and your tables are going to perform way more read operation than modify operations (create/update) usually MyISAM is faster, but if you have to modify the rows and read at the same time, is going to be better INNODB because when reading/modifying it only locks the row that is using, while MyISAM locks the whole table. When you'll have heavy load and read/write at the same time, MyISAM will be slower. Anyway, don't trust me: Test it by yourself performing load testing on your application, that's the way to go.

And lately, about your code: Don't mix connection, SQL and show the presentation on the same file, or it'll be hard to you later make the code maintenance. I would suggest you divide that code into classes, so you can retrieve the data from a model class, and show them in a template. Read From flat PHP to Symfony 2, which is quite good and may help you a lot with the concept. Although is Symfony2, the main point is grab the idea and try to separate the code concepts as much as possible, trying to reuse it.