Some test data for today, HTML colors and the ZIP code database.

Earlier today I was looking for some test data, some specific test data, and though I’d share it, along with another set of data I’ve used a few times (and not only for testing). First some color test data, this PHP array contains ever html color with a name, along with it’s corresponding hex color. This was actually pulled from tinycolor.js, which I was looking through to add something to.

Next I’ve attached (since it’s far to large to paste) a mysql file containing every ZIP code, although with corresponding city, state, lat, long, population, and a bunch of other data. Just upload the attached file and have a ball.

zip_code_db.sql.zip

I did some work on something else that might get posted this weekend, but will probably not make an appearance till next week.

Simple MySQL to CSV

I needed to make a quick export earlier this week so that we could get records into another program. At first I though I’d need to find a php library to make a csv for me (to save me the time of making the script myself) but it terns out that the process couldn’t be easier. The csv format is very easy to create and really seems to follow several key rules. First, you put things in quotation marks. If it’s a number you don’t have to, but just to be safe I put everything in quotation mark (it was also easier coding wise). Second, if there is a quotation in a cell you must put another quotation before it. Finally, you must create a new line at the end of every new like (obviously), With That in mind I made this:

Snipplr: http://snipplr.com/view/58408/simple-mysql-to-csv/

The code is pretty self explanatory, just doest what you need to as mentioned in the first paragraph. I tested it (admittedly in a limited fashion) and it works great. I don’t think there could be an potential problems but as this was created in less than 5 minutes as really just a quick test I’m not sure. If anybody find anything that might cause issues let me know and I’ll expand upon it but as of now it does exactly what I created it for.

MySQL joins made easy with naming! (and other mysql tricks)

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.