Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:
log
area (char) lognotice (char or text) logtime (timestamp when the event was logged). Fetching Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated - the Query to fetch the data could typically look like this:
When playing the role of the DBA, it’s often useful to get a quick listing of how many rows each table in a database contains. The syntax for this is pretty simple in Mysql:
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '***database name***'; Replace database name with the actual database name in the SQL above.
Notice that when using innodb tables, it’s only a rough estimate.
Just a word of warning when using PHP and Mysql - if you’re trying to make efficient code and not utilizing all sort of frameworks and abstractions, you might be in for a small surprise in a default setting.
Usually is slightly lazy and often use the mysql_fetch_assoc function. It provides each row as an associative array, and is quite convenient to work with. Recently however while optimizing some code, I figured I’d switch to using mysql_fetch_array assuming it should be more efficient.
Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it.
Let imagine we have a mailinglist in a table named “mailinglist” and it has a (char) column with the email addresses subscribed to the list.
If you’re a developer and use mysql, I’m sure you’re aware that it’s a database and it quite good at storing data, but one of the neat things about Mysql (and most other databases) is also their ability to provide meta-data on the contents of the database.
Most people know how to use the meta-data queries in the commandline, but if you want you can also use them in your (php/perl/some-other- ) language.