In the interest of decreased overhead or a simpler bit of code it’s often necessary to join multiple arrays together from the mysql query. It saved from making a bunch of mysql calls in a loop and general is easier to work with, except for when the two tables share some common schema names, then it gets kinda messy. A php array can only have 1 item with a particular name, so if both your tables have a “name” field well ones going to write over the other. That is, unless, you do some renaming.
Lets say you have two tables, one for names and one for addresses, both of these have some common items, like an id, and lets sane a name (the name of the person and the name of the address (home, wrok, ect), along with their independent data).
If you did a simple mysql join like this:
SELECT n.id, n.name, a.name FROM names a LEFT JOIN address a ON n.id = a.person_id;
In this case, the output would be something like this:
array(
[0] => 4
[1] => John Doe
[2] => Home Address
[name] => Home Address
)
Now in this example it’s not hard to just use the numbers, But it’s easier to soo what I’m getting at when theres not a ton of fields to wade through. As you can see from the example you can only define name once, so to call the persons names you have to call it via 1. The solution to this is simple, give them all new, proper names like this.
SELECT n.id AS 'id', n.name AS 'person', a.name AS 'address_type' FROM names a LEFT JOIN address a ON n.id = a.person_id;
Those simple little AS statements are all it takes to turn a messy disorganized one into a neat one, using the above example it would now look something like this.
array(
[id] => 4
[person] => John Doe
[address_type] => Home Address
)
Makes for a much simpler fetch array does it not? This can also be used for queries that don’t have a join if you just want to do some renaming, the only reason I’ve even though of for that is when I want to use some variable variables (for small calls that I’m not running via a loop for whatever reason, there aren’t many). It turns out something like this.
SELECT c.ttl AS 'county', s.ttl AS 'state' FROM counties c LEFT JOIN states s ON c.id = s.id
After that run it through a little bit of variable variableness like $$result['state'] = $result['country']. And just like that you have an array of a state name with the country of origin in it. It’s pretty pointless but I have found a use for it when filling data to very short tables, anything more than about 4 entries might as well be done in an array (not to mention an array is easier). I guess it could be used as a way to obfuscate code (especially if you use nonsensical single letter variables) but I’ve never seen a good reason to make your work harder to decipher for the next guy.
The last little tip I’ve used to pre-formatting information, just to save yourself a little extra work, it’s somewhat limited what you can do but it can save you a line of PHP, and sometimes, well, hell you might as well. Lets say I’m sorting images in the img/ folder, and the filenames are stores in the database under the logical schema filename. A simple little query like this:
SELECT name, concat('img/', filename) FROM images
The output will now look like img/filename.jpg. I have actually found a good use for this. Pulling image names from two separate locations. One a serialize table sotred in a single tabel entry, another in a completely different table stored as just a name. Both scripts, of course, written by completely different people. One set had the image path already attached to it, the other didn’t, so all I had to do it concat the path to the ones stored in the database and then I didn’t have to worry about checking in PHP if it had the path or not, saved another if statement, although honestly I havent checked the speed of processing, so I don’t know if it’s actually faster (the code is shorter, and I like short code, look neat and tidy).
Have any tips, want some clarification, leave a comment and I’ll get back to you, clarify anything that I made extra confusing. LOL, like anybody reads this blog.