I have always been curious about the handling and usability of data between a MySQL database and JSON files in PHP. I decided to do some reasonable case examples using both format to see which was easier, quicker, more efficient and a conclusion on which format should be used in what case (if at all).

Before running tests I am assuming that the JSON method will take a little longer, lets see:

Storing / Inserting data

Placing data to be read at a later time, the placed data obviously needs to be organized and identifiable against other data. For this test I will insert and store data around mock user registration information from a form, with the following POST results.

submit.php:

session_start();
$first_name = $_POST["fname"];
$last_name = $_POST["lname"];
$age = $_POST["name"];
$email = $_POST["email"];
$gender = $_POST["gender"];
$id = substr( "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" ,mt_rand( 0 ,51 ) ,1 ) .substr( md5( time() ), 1);//random id

Now lets handle and store the data with the two methods:

JSON

$our_array = array('id' => $id, 'fname' => $first_name, 'lname' => $last_name, 'age' => $age, 'gender' => $gender, 'email' => $email);
$our_array = json_encode($array);
$fp = fopen("".$id.".json", "w");
fwrite($fp, $our_array);
fclose($fp);

Time: 0.0046 Seconds

MySQL

$connect = mysqli_connect("127.0.0.1", "user", "password", "database");
$sql = "INSERT INTO `users` (`id`, `fname`, `lname`, `gender`, `age`, `email`) VALUES ('$id', '$first_name', '$last_name', '$gender', '$age', '$email')";
$result = mysqli_query($connect,$sql);

Time: 0.0032 Seconds

MySQL can be done in fewer lines but do note that this is not using prepared statements and can be prone to injections. Also I need to make a database + table + user before using the MySQL method, which is not needed with the JSON method as the file gets created automatically.

Reading / Retrieving data

In this situation we are going to be getting the data for where the users id = p6e3b86e1bf978f10ffda1eff1a140ac

https://ourwebapp.com/user/fetch.php?id=p6e3b86e1bf978f10ffda1eff1a140ac

JSON

$id = $_GET['id'];
$data = json_decode(file_get_contents("".$id.".json"));
$first_name = $data->fname;
$last_name = $data->lname;
$email = $data->email;
$gender = $data->gender;
$age = $data->age;

Time: 0.0002 Seconds

MySQL

$id = $_GET['id'];
$connect = mysqli_connect("127.0.0.1", "user", "password", "database");
$sql = "SELECT `id`, `fname`, `lname`, `age`, `gender`, `email` FROM users WHERE `id` = ".$id."";
$result = mysqli_query($connect,$sql);
$first_name = $result["fname"];
$last_name = $result["lname"];
$age = $result["age"];
$gender = $result["gender"];
$email = $result["email"];

Time: 0.0014 Seconds

This time its JSON that has fewer lines, but if we needed to see all the email address’ where age is under 30:

JSON

$files = glob("*.json");
foreach ($files as $file) {
    $thisData = file_get_contents($file);
    $data = json_decode($thisData);
    $age = $data->age;
    $email = $data->email;
    if ($age < 30) {
        echo "$email : $age<br>";
    } else {
        //do nothing
    }
}

Time: 0.0010 Seconds

MySQL

$connect = mysqli_connect("127.0.0.1", "user", "password", "database");
$sql = "SELECT `id`, `fname`, `lname`, `age`, `gender`, `email` FROM users WHERE `age` < 30";
$result = mysqli_query($connect,$sql);
while($row = $result->fetch_assoc()) {
        $id = $row["id"];
        $first_name = $row["fname"];
        $last_name = $row["lname"]; 
        $gender = $row["gender"];
        $email = $row["email"];
        echo "$email : $age<br>";
};

Time: 0.0012 Seconds

Conclusion

Firstly I want to get it out-of-the-way that storing large amounts of data using MySQL wins everyday and it’s just a smart idea to use it, as it is meant for handling data. From the tests I did Inserting or storing data MySQL was quicker and the code is cleaner even though you still have to design the database layout and schema it’s still worth it to use MySQL.

Reading and fetching data JSON was quicker, i was surprised! I assume reading from disk and reading raw files is quicker than doing a MySQL database connection and query. The difference wasnt massive and MySQL may beat JSON out once you get hundreds of entries which i only had 6.

Finally there is no problem whatsoever in storing small amounts of data in a JSON file, just keep it named to something uncommon to prevent outside access. Stuff like online status, YouTube views, Avatar links etc are fine to store in a JSON file for quick setup (avoid MySQL setup). But remember for a large database + future proof please use MySQL!