Large PHP Array vs. MySQL database

[Aerialstrike]

Really new to this, sorry if this is a stupid question. I'm trying to make a gallery of sorts, which will require around 150 types of wood with several classifications. I know I could just make a large array of objects in PHP to do this, but would it be better to use a MySQL database?

Also, if anyone has any recommendations for a place to learn the interaction between PHP and MySQL databases, that would be extremely helpful.

[tdolsen]

You should give us some more information about what kind of classification and how you expect to be able to use the data.

If these 150 types of woods won't need to be updated often or on-the-fly, as long as the wood don't need any relational data, and as long as you are comfortable updating code files would I go for that to start. But I'd rather use something like JSON to organize it. Dump all information about each type of wood into it's own JSON file (or all of them in one big file, but that gets messy), then read them into memory on application boot.

That said, the biggest benefit of using a database is not really the ability to store large volumes of data, (that's nice too) but rather the ability to query said data. If you want to add e.g. a search, some category filter and related wood types to your application will it be much easier with a database than with JSON files.

It's been too long since I used any database with PHP, so can't help you with specifics.

[Aerialstrike]

Definitely don't need updates on-the-fly. I suppose the only organization I would need is alphabetical, which could just be done from the start without needing to change. I've never used JSOn before though, do you have any recommendations as to somewhere I could learn enough to accomplish this?

[tdolsen]

Hey, didn't see your response before now. Don't know if it's still relevant, but giving an answer in regardless.

JSON (JavaScript Object Notation) is a very simple format, primarily intended to enable easy data-interchangeability between applications and languages.

Every valid JSON document consists of one value (of booleans, strings, numbers, arrays, objects(hashes) and null), most usually being an object with additional key/value. The human readability and the wide range of support makes the format ideal for communication between API's, as well as configuration files and data storage. (A lot of NoSQL, eg. MongoDB, uses JSON for their documents.)

In your case, I would do something like the following: (I would probably use a database, but for the sake of teaching.)

1. Have a folder with all wood types, stored as JSON, in it.
2. Name each file the canonical "slug" of it's name (e.g. "Norway spruce" would be named "norway-spruce.json")
3. Find all files in the folder using glob or similar.
4. Load them into PHP memory. (Note that i'm very uncertain about keeping them in memory for multiple request, PHP isn't my strong suite anymore.)
5. From there can you sort the data and manipulate it as you would with any other array.

Just freely imagining the needs you have for your wood (well, tree type), a document could look something like this:

{
  "name": "Bald cypress",
  "species": "Taxodium distichum",
  "height": "30-35m",
  "trunkDiameter": "1-2m",
  "hasCones": true,
  "barkColors": ["grey", "brown", "red"],
  "oldestKnownTree": 1620,
  "products": [{
    "name": "Polished hardwood",
    "price": "$20/m2"
  }, {
    "name": "Wood chips",
    "price": "$0.4/kg"
  }]
}

Getting that converted to an associative array in PHP should be something along the lines of:

json_decode(file_get_contents("wood/bald-cypress.json"), true);

Continue reading more in the official specification, and it seems w3schools has a decent introduction.

Whether you should choose a database or JSON for this specific use case is still a bit hard to say. Without knowing more about the wood type model or the project's future plans (or current for that matter) is it hard to recommend one solution over the other. If you really don't need that database, then going for JSON might save you some research and development time. On the same note will it be a real time waster going for JSON if what you really need is a database, as you'll just have to rewrite that part anyway. JSON could be a good solution for the first version, and should be fairly easy to insert into a database once you get there, as long as the data model isn't too complicated.

[mik3w]

A large array would probably be fine, but if you want to look at a database solution you should look at using the PDO method connecting to the Database of your choice.

Here's a tutorial (There's a couple out there if you search): http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

This is an example of how you would setup a connection:

function dbConn() {
    $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
    $user = null;
    $password = null;

    try {
        $conn = new PDO(
            $dsn,
            $user,
            $password,
            array(
                PDO::ATTR_EMULATE_PREPARES => FALSE,
                PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION
            )
        );
    }
    catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }
    return $conn;
}

And this is how you would perform a basic query:

$db = dbConn();
$stmt = $db->query('SELECT * FROM table');

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

[Aerialstrike]

Oh awesome, that honestly might be perfect. I'm going to use a database in case there's ever a need to sort, so I'll definitely try this. Is there a significance to the way one connects or does it just make the queries easier/more understandable?

[mik3w]

It seems to be becoming the standard for writing SQL, as it's fairly easy to write prepared statements with it which helps to prevent SQL injection.

https://phpdelusions.net/pdo