A simple MySQL class for use in PHP.
Feel free to suggest improvements.

This little tool is pretty useful for administrators who wear the hat of both system admin and DBA on Linux/MySQL boxes. I wrote it as kind of a joke for our Perl developers, but now I use it just about every day for everything from at prompt hackups to full on table space utilization monitoring scripts.

The snippet has the library script that you can source into scripts that actually do work, as well as a script that uses the functions in the libraries. Anyone who has used the C API should be able to relate to using this script.

It's pretty fast and lightweight. It makes only one external call to mysql and sed per query. The rest is all bash builtins. Requires bash > 3.0

Return a Portion of a Character String

Cuando concatenamos varios campos con CONCAT( Campo1, Campo2,... ) si algún valor es NULL no se realiza la concatenación, para concater campos (aunque algunos tengan NULL) usaremos COALESCE que devuelve el primer valor que no sea null de la lista.
Asi le pasaremos una listca con el nombre del campo seguido de "", si el campo estava lleno, devuelve el campo, si es null devuelve el segundo, es decir ""

I use this method for keeping my sql templates away from my code.

You can extend upon the idea, as I have done in the past, by placing SQL handing classes between your scripts and the template library.

Things to note here:

The lesser userd heredoc string method. The reason this is used is to keep the SQL clear and well laid out, and not as messy as using quotes.

vsprintf() is a very handy function if you don't want to hard code the number of parameters to interpolate your string with.

The use of sprintf templates offers you additional security. For example, only allowing numbers to be placed where a %d falls. This, of course, shouldn't be the only security on user supplied variables, but comes in extra handy for debugging purposes.

Regarding the TODO in there, it would take a check of the number of % placeholders there are in the template. One caveat is remembering to remove the count of %%'s that appear (the literal percentage).

This is a quick and dirty function for preventing SQL Injection, the function is designed to clean any variable that will be concatenated into an SQL query. Apostrophes and Double-Quotes are changed to entities in order to ensure that encoding does not become an issue when the content is pulled back into a page. I'm looking for criticism here, I want to know if this is secure or not.

How to connect to your mysql serve

When ByteMyCode was crashing because of the traffic load, we started optimizing MySQL. The server had a load of >50 but the processor was all tied up in IOWAIT. We realized that the database was the source of the bottleneck so we increased all of the cache sizes. Now, it is really fast.

This class is a simple authentication scheme which makes it easy to add authentication to any page by including one class and adding one table to your MySQL database.

The following functions are employed by this authentication class:

auth()

this is the default constructor; it automatically checks for the POST vars "username" and "password", it also checks to see if the user passed the GET variable "logout", which would prompt it to set the authentication status to un-authenticated.

is_authorized()

Checks the SESSION variable "authorized" and returns true or false depending on that variable.

mysql_bind()

This is automatically called by the constructor each time the class is instantiated and $_POST['username'] and $_POST['password'] are present. It queries the db for a valid username and MD5 encoded password.

user_create($username,$email,$password)

Creates a user, if the username is available, and creates an MD5 hash based on username, password and date, to be used in the "activation" of the account.

user_activation_message($username)

Sends the custom activation message to the email address for the username specified

user_activation($activation_hash)

Checks to see if the activation hash is valid, if it is, the activation_hash variable is set to NULL, thus signifying that the account is active.

user_password_change($username,$password_old,$password_new)

Quick and easy way to change the user's password with one function call.

user_logout()

Sets the authorization status in $_SESSION['authorized'] to FALSE

is_username_available( $username )

Returns TRUE or FALSE depending on whether or not the username is free.

I actually like SQL. So, things like Hibernate with it's own query language don't quite fit my style. But, I don't want to code the same catch SQLException conn.close over and over either. So, I came up with this.

The idea is you extend SQLCommand (usually anonymously) override getSql(), and call execute() to get a list of whatever objects you are selecting - all the cleanup stuff is taken care of. It also handles nullable attributes more intuitively using ResultSetWrapper and PreparedStatementWrapper so that in your overridden getRow(ResultSetWrapper rs) method, you can call getInt on a nullable column and have it return null - what a concept! Also I like java.util.Date for my dates, so the wrappers convert to/from java.sql.Timestamp.

Right now I've only bothered with some basic types - it should be pretty clear how to add more if you need em.

See what ya'll think.

Oh, it uses JDK 1.5 Generics, but stripping that away would be pretty easy if you wanted 1.2 compliance.