Sql Tag | Jacob Emerick's Blog

Connecting to a MySQL Database with PHP

Working directly with the database is something I rarely do any more with the applications I work with. Instead of creating a raw connection and pulling data directly, I usually utilize some sort of customized data layer. (If you don't know what a data layer is, imagine creating a PHP application with no queries. You still have data being pulled, usually from MySQL table, but the data storage and structure is kept independent of your application. When done correctly, this can help immensely with code cleanliness and development.) However, setting up a connection and pulling information is still an important skill set, so here is a post about the basics. If you're looking for information that is more basic than a PHP-MySQL connection, you can read my series on the basics of the SQL language.

There are several ways to connect to a table, but I'd highly recommend using the MySQL Improved Extension, or mysqli, as a simple but powerful way to connect to your MySQL ...

• Tags:
• introduction,
• php,
• sql

Cliffs of Mulligan Plains

The -15ºF air hit me like a brick wall as I stepped outside of my car at Mulligan Plains. Unaccustomed to the cold Upper Peninsula mornings I quickly covered my nose and mouth with a warm scarf before heading down the plowed two-track. The sun's early rays had only just begun showing over the eastern cliffs and I didn't have time to wait for the valley to warm up; there was a long, hard hike before me.

Last summer marked the first time I visited Mulligan Plains without stopping by Mulligan Falls. I had always known that there were other attractions in the narrow valley but had never made a direct effort to see them until then. The main goal of today's trip was to revisit one of these sights: Rocking Chair Lakes. My route wouldn't be an easy one but did a promise some awesome views of the plains. I planned to start near the bridge over lower Mulligan Creek, follow the top of eastern cliffs to Rocking Chair Lakes before looping back to the car, resulting in a 6 mile hike with plenty of vertical distance change.

The first climb of ...

• Tags:
• abstraction,
• oop,
• sql

What's With the Big Pause?

When I first decided to create an online blog I wanted to avoid a common pitfall of many other blogs: lack updates. While there were a few empty months in 2009, it feels like a did a good job with regular posts every week or so... Until the New Year rolled around. This is the first blog post in over six months.

Starting in mid-November a few things changed that really impacted my regular blogging. The first, and most obvious one, was winter. While I do enjoy winter adventuring, driving to the Upper Peninsula through the snow to come up with fresh content for the hiking side of my blog was something I decided to avoid. That, coupled with decrease daylight and other seasonal blues, dampened my motivation to write new hiking blogs.

Another problem that I ran into was a decrease in time. Not only did I start working later hours in November at my full-time job, but I started a large contract project that ate up any free time I normally reserved for my personal site development. My site was half rewritten at this time - I was starting to ...

• Tags:
• oop,
• php,
• sql

Intro to SQL Part D

For the last post in this short series on SQL I wanted to explain some more in-depth usage. A technique that I wish I would have started using in my early websites is a centralized script to handle all of my database calls. PHP does have some great functions built in to connect, read, and write using SQL statements (check out mysqli), but it's easy to start copying the same eight to ten lines of code every time you want a piece of data from your tables. A better approach is to handle the queries with a centralized class.

Before anything can be read or written to a table with a SQL statement, you need to create a connection. Your website will be signing as a user with specific privileges. A good technique is to make two users, one that can read and one that can read and write. By using the former user as much as possible and taking extra care with the latter one, you will minimize the possibility of hackers uploading malicious information or dropping whole tables.

Once a connection is made you can start having fun with the database ...

• Tags:
• oop,
• php,
• sql

Intro to SQL Part C

While the last two posts focused heavily on theory and fundamentals of SQL it's time to start on the actual usage. There's a ton of good tutorials out there that skip right to the code, but as a young developer, I wasn't convinced that SQL was a worthwhile tool until I was forced to use it. I wanted to spend a good amount of time discussing the basics before jumping into some examples. If you're looking for more advanced and specific uses, I'd highly recommend checking out w3schools SQL tutorials.

As I introduced in my last post, an SQL table is a collection of unique rows. There are two primary things that you do with the data in a table - read and write. With the proper syntax, a hacker can write to your database, injecting into or deleting your data, so it's important to restrict the front end privileges and validate. That's a subject for a later post, though. Let's look at how you can read a table.

Reading from a table is also known as a 'select' statement, ...

• Tags:
• introduction,
• sql

Intro to SQL Part B

In my last post, I introduced some of the basic ideas of data storage and how I came to realize that SQL would be a great solution to my content handling problems. Now I'd like to walk through some of the more advanced concepts and start going through some basic uses. There's plenty of great tutorials out there if you'd like to take SQL to the next level (I'd recommend checking out w3schools), but I hope these posts make a great foundation for a starting developer.

An Excel workbook is a good way to picture a SQL database. A database is a collection of tables (one table per tab), with each table showing a different portion of the total content. Each table should be unique in most cases to avoid duplicating your data. The individual tables in the database have some constraints. Rows must have the same number of columns and identical column types. It's also a good idea to keep the columns simple. If you're saving numbers, then the column should ...

• Tags:
• introduction,
• sql

Intro to SQL Part A

I like to think of myself as a practical web developer. With no true formal training, most of what I use to make my web sites is self-taught. Instead of trying to apply what I learned in a classroom into an online project, I went out and learned what I needed to get a project done. This allowed me to enter the world of web programming slowly, gradually picking up additional techniques and languages as my web sites became more advanced. There has been more than once that this approach has made my life difficult, though, and SQL is one of the more blatant examples.

After I learned xHTML and CSS, I thought that I was set with web development. I could design and launch fully compliant sites, updating them as necessary with new content. Once a few months passed, though, I started to realize that there was a big difference between markup and content, and that the content (data) of a web site should be handled separately from the markup (html tags). By keeping my changing content hard-coded on the page, I was losing my old data and funneling all of ...

• Tags:
• introduction,
• sql