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 ""

this snippet show how to set up passwords in a mysql console.

My previous entry on an easy to use SQLite interface for XUL/JS got me thinking: how many times have I had to write functions that were just wrappers for a little SQL?

Below is the fruits of that little query (excuse the pun). Forgive the dbXXX functions; my implementation depended on a small portion of a larger mysql library that I've grown accustomed to coding with.

Well after searching the internet for something similar to this, I didn't come up with anything...so I made my own! This simple script will track your members' time spent on your website. Please feel free to give comments/suggestions/feedback.

This is a cool function that lets you save MySQL query data to an Excel spreadsheet. This is good for taking a backup or if you want to change a lot of information fast, then re-upload it.

I will use the config.php and class.mysql.php files from MySQL DB Class with Extras, so please look at those also.

This is a little function that you can use to print out results of a query. It will dynamicly generate the table for you (like phpMyAdmin). This is a nice little checker that is fast. It beats logging into phpMyAdmin and typing in your query there.

I will use the config.php and class.mysql.php files from MySQL DB Class with Extras, so please look at those also.

This is my MySQL database class that I use for all of my sites. Some included features are:
- Get execution time
- Error reporting (screen and email)
- SQL stats

I made this script to delete any unneeded files from the server that weren't being used by the database. Of course you can expand this a lot more, but I cut it down a little to put it up here.

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

When you need to perform an action on a single row of data, many people turn to cursors. Cursors come with a lot of overhead in the database engine and can be confusing. Here's a way to get the same results without the overhead in a much simplier way.

How to connect to your mysql serve

MySQL 5.x Stored Routines, PHP Session Values, a little CSS oh my! Complex to set up but easy to impliment. Geared toward security, simplicity (of use) and convenience.

This routine will get an image stored in a SQL database (IBM Informix shown here) as a BLOB and return it to the screen.

To place this on an HTML page simply add

I have an MD5 hashed version as well, but can't share that...(So you can have encrypted id's passed)

Have fun,
Jeremy

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.

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.

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

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.

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.

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

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.

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

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

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

If you've ever wanted to build an application that can both find the distance between two zip codes, and find zip codes within a specific radius of another zip code, then this is the snippet for you.

I've tried to build this before using PHP4 and MySQL 4 and have found it to be painfully slow for a one-user connection over a Local Area Network (LAN). So, before you continue reading, I must make it abundantly clear that this method requires PHP 5 and MySQL 5.

This is because in PHP5, we will need the MySQL-Improved interfaces, and in MySQL 5, we will need functions and procedures.

Using functions and procedures in MySQL puts the weight of the calculations on MySQL, where they will run hundreds of times faster than if PHP were to attempt to do the work. In addition, using functions and procedures is still hundreds of times faster than carefully crafted SQL statements (one benchmark took over 10 seconds using a WHERE clause, while the function + procedure method took around .12 seconds--note the dot).

Since I am not a mathematician by trade, I found the necessary calculations using Google.

For the "Great Circle Distance" formula, I used Meridian Word Data's website. For the radius finder, I used another that unfortunately I do not have off the top of my head right now. I will look for it again and repost it for proper accreditation.

For the zip code database, I am using the free zip code database from CFDynamics.

Below, you will find the database and table schema, the functions and procedures, and also a demo PHP script to see all of this put into action. As a courtesy, you may download a dump of this database from my website. It's uncompressed size is 3.4M, and it's compressed size is 537K. The checksum is available here.

Please leave me comments, suggestions, questions, and/or rants about this snippet. I feel this is a very useful bit of code, and hope that others out there may find as much use from it as I will be.

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.

Pagination Class , I think everyone knew now what's it does and means

NT: This class written by my friend have phun!

This is a mysql php pager class that I found online somewhere and modified.

Anti-QSL Injection. I'm sure it could be better, so any improvements are welcome.

This is a bare-bones DB connectivity class. It's really just meant to abstract a few basic mysql_ functions so that if you ever have to change DBs you don't have to change every database function call.

I really just threw this together on a whim with no testing what-so-ever. Also, it only abstracts the most basic MySQL functions--this is intentional; please expand it as you see fit.

Database Class with quick connectivity and useful database functio

"Today in the news, MySQL previews..."

As a "preview" of text, this MySQL select will return the first few words out of a text field in a database. It returns entire words (doesn't cut them off in the middle) and follows the last word with an elipse "..."

In the PHP, assign $CharactersToDisplay to a number - the approximate number of characters desired (or you can hard-code it if you want).

For the example, I also use $TheID as the row identity for the record to display.

Here is a pair of function to use in combination. The first one will change newlines into tags to store into a database. The second one does the reverse so you can edit the content of the database without having the tags show when you edit the content. If you can find improvements or have comments please let me know

A database API written in Perl for a MySQL.

You will need to edit the settings for your server.

Enojy

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

This is a little script I wrote that copies your database through a cfquery statement

Enjoy,
Ernie
ernest.breau@gmail.com

This is an example of how to shrink a database with simple query.

Some usefull snippets for SQL Server.

List the databases on a MySQL serve

Creates a new user with all privileges when logging in from localhost.

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.

Connect to MySQL db & display result from Table query

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).

Return a Portion of a Character String