What's a better method, using Large Array vs SQL Joins?

Question

I can't decide on w/c technique to go w/... although I am tempted to go the Array way as it is much more straight forward and easier/cleaner to code...

lemme explain in a simple way..

For my messaging app, I have 2 tables

1. Contacts - contains name, and phone number (currently 5,000+ records)
2. Messages - messages and the phone number, but not the name of the member

I need to list 1000 Messages, w/ the corresponding phone number and name if possible...

eg.

1. "Hi dude" - from 565-1111 (John)
2. "Bring Cheese" - from 565-2222 (Bieber)
3. "Hehehe " - from 565-2332
4. "iron man is cool!" - from 565-7748 (Arnie)

etc...

I would normally join contacts to messages via the phone number... pretty standard stuff... (but in reality my queries are a bit more complex than a simple left join)

Now, i've been playing around w/ the idea of fetching all contacts and putting it into an array where my app/system could use it in a much easier , more straight forward and cleaner code... where:

1. query SELECT * contacts

and store them into an array:

contacts['555-7748'] = "John";
contacts['555-1111'] = "Joe";
contacts['555-2233'] = "Borat";
contacts['555-4234'] = "Arnie";
etc

this way i can easily lookup the NAME of the Contact via accessing the array, many times in my page/script, just by simply doing:

$name = contacts[$phoneNumber];

I have actually implemented this and it's really cool .. no more join headaches and re-querying contacts table etc..

now the question is.. is this OK? Practical? or could i be hitting some memory cap or something here?

it's basically running JOIN query on EACH message my script outputs or store ALL THE CONTACTS/RECORDS into array (memory) and refer to that instead, making queries are more simple.. my only worry is, querying thousands of records into an array, may be a bit too much? or is this really OK/practical? specially since there are times when my script needs to fetch specific contacts more than once...

To add, I was also thinking of storing that array into a Session variable! lolz.. so my scripts can simply share this data.. lol

looking forward to some good inputs Thanks guys.

---

---- UPDATE as of 03-31-2013 (Sunday) ----

---

Ok... I'm thinking about a totally different approach now..

1) SINCE all this is really me needing to show the "CONTACT NAME" for each message for neat/presentation purposes (better to see the name of the contact on a message rather than phone number yeh?) ....

2) and since each message will need to show 2 contact names (receiver and sender) based on their phone numbers on the MESSAGES table.....

3) I now am thinking of ADDING 2 fields into the MESSAGES table instead... "sender_name" and "receiver_name" .. I have seen Bulletin board / Forum software use this technique a lot ..

4) when system receives a message , it checks for existing Contacts entry of the numbers associated w/ the message...and if it exists, get the respective names and include it into storage/insert

5) ..problem is if during the message was received, contact info isnt available, so no NAMES are saved.. and later CONTACT info is then created... the OLD messages wont show the contact names as intended...

6) 3 way solution... 1st, evertime a new contact is added/changed my php will make updates to all messages w/ the matching numbers... 2nd, from time to time perhaps once / month, run a "synchronize routine" to make sure things are in sync.. or, when a user Actually OPENS a message, mandatory routine will run to check if contact name of that particular message is still in sync w/ the contacts table information... and make updates as necessary..

hmmm... better approach you thing? this way, i just need to issue straight forward SELECT queries , simple, fast.. but am able to display the names..

then again, i still kinda find that QUICK array reference thing so cool though.. lolz

• indecisive

Answer 1

It sounds as if you've decided how to proceed, in which case I'd suggest looking into the most efficient method to manage your list. "Sharing" it across sessions I've not encountered (who do not share sessions for security reasons, or shouldn't). You've nixed redis.io and Memcached as too complicated.

One way you could do this, and minimize it's impact in efficiency, would be to setup a CRON or configure some workers to periodically run a script that outputs the contacts table to a PHP-parseable file.

For instance:

<?php

// Let's not run this if the app isn't accessing it.
if (!defined('APP_RUNNING_CONST')) {
    header('HTTP/1.1 401');
    exit;
}

$contacts = $db->query()... // Get the contacts list.
$lines = array();
$num_contacts = $contacts->getRows();

while ($contact = $contacts->nextRow()) {
    $contact->field->contactname = addslashes($contact->field->contactname);

    array_push($lines, "\$contacts['$contact->field->phone'] => '$contact->field->name';");
}

if (!empty($lines)) {
    $o_array = implode(PHP_EOL, $lines);
    $c_date = date('r');

    $o_array = "<?php

// CRON contact table cache to array
// Saved $c_date by __file__
// Number rows: $num_contacts

$contacts = array();
$o_array

";
}

// Write the PHP file over the last:
$contactsfilename = 'contacts.php' ...

Then all you need to do is

include '/secure/lists/contacts.php';

in the pages you need it. You should be careful how you replace the file (copy, save and delete when finished) as well.

Some other improvements you could make might be to output files for each area code/prefix....

contacts.403.555.inc.php
contacts.403.585.inc.php
contacts.461.550.inc.php

And then create a function to autoload the appropriate file group only as you need them ("lazy loading") and use static variables to keep the references in the function. This also overcomes the global variable problem, as well as helps keep the functional code accessing the files in a central place. Of course you could use a class, maybe with an array interface implementation.

Anyhow, yet another option could be to use a ini file format.

Answer 2

Do you have access to the DB? If so, I'd suggest creating a view so that your queries from PHP are more simple. Maybe that will simplify things for you. I'd still suggest using queries instead of simply large arrays. That being said, if you know the load you are going to place on these and the array works fine there shouldn't be any reason to avoid it. Best practices are good, but there is something to be said for ease of coding which breeds better maintenance.