Entries tagged "database"

Formatting Dates within the MySQL Query

Before displaying dates from a MySQL database, do you change the format? If so, how are you formatting those dates? For me, I typically went straight to PHP for the answer. That is until it was brought to my attention that MySQL has a built in function for formatting dates. Let's talk about the date_format() function. [Continue reading]

Sorting Two MySQL Table Columns as One

How do you sort database entries chronologically when there are two different date fields? One shows when the entry was updated. The other indicates when it was created. If the developer didn't have the hindsight to set both fields to the same date when an entry is created, how do you work with the fields for sorting? [Continue reading]

Naming Your HTML Form Fields with an Associative Array

When using database entries to dynamically build HTML forms, how do you go about naming the form fields? Do you name them "Field1", "Field2′, etc.? Or do you have a more efficient way to access the fields when processing the form submissions? If you haven't tried using an array as the name, you may be missing out. [Continue reading]

Going Beyond the Typical Sort: Sorting by Specific Values with MySQL’s Order By Clause

When performing MySQL database queries, have there been times where the "Order By" clause doesn't seem to cut it? Well it turns out that we can do more than sort by one or more columns in ascending and descending order. MySQL's FIELD() function provides a way to target a specific value from within a column. Let's take a closer look at the function. [Continue reading]

Make Sure Those Passed IDs Contain Numbers

When passing row IDs between pages, it's a good idea to check the value is what you expect. Values which could be tampered with by the user need to validated and sanitized. So, if an ID is supposed to be a number, we should make sure it is before running the database query. Let's discuss some options for checking for numbers. [Continue reading]

Sorting HTML Data Tables Part 2: Dynamically Sort in Ascending and Descending Order

In last week's post we looked at dynamically sorting HTML data tables. But we only talked about sorting the columns in either ascending or descending. If the user is looking for a last name that appears near the end of the alphabet and the column is sorted from A to Z, they may have a lot of names to go through before finding the one they want. Instead we could provide an option for sorting in both descending and ascending order. [Continue reading]

Sorting HTML Data Tables Using the Header Row with PHP

When displaying data tables, how do you usually sort the information? Do you sort by the field that you think is the most important to the visitor? For example, it's perfectly acceptable to list entries chronologically; where the newest is shown first. But what if the user wants the data organized differently? We could let them choose. [Continue reading]

Changing Database Entries on the Fly by Embedding PHP Variables

When pulling information from a database, have you ever needed to change aspects of the text on the fly? For example, I have a form that contains several questions like "Did you utilize any of the website resources in 2011?" These questions are stored in a database and need to change annually. So next year the 2011 should be changed to 2012. The year could be removed altogether in some cases, but others require the year to remain. In a perfect world, a PHP variable could be stored in the database along with the question, but that can't be done…can it? [Continue reading]